Oracle 12c新特性之如何检测有用的多列统计信息详解
前言
之前和大家分享过Oracle11g下的一个新特性——收集多列统计信息(https://www.nhooo.com/article/109514.htm)，今天和大家分享Oracle12c的一个新特性——自动检测有用列组信息。二者相得益彰，大家可以具体情况酌情使用。
言归正传，我们可以针对一个表，基于特定的工作负荷，通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定我们需要哪些列组。当你不清除需要创建哪个扩展统计信息时，这个技术是非常有用的。需要注意的是，这种技术不适用于包含表达式列的统计工作。
接下来，我们通过例子来学习这个的新特性。
一、环境准备
首先，我们创建测试表customers_test，基于sh示例用户下的customers表。
SQL>selectbannerfromv$version; BANNER -------------------------------------------------------------------------------- OracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProduction PL/SQLRelease12.1.0.2.0-Production CORE12.1.0.2.0Production TNSforLinux:Version12.1.0.2.0-Production NLSRTLVersion12.1.0.2.0-Production SQL> SQL>connsh/sh@HOEGH Connected. SQL> SQL>DROPTABLEcustomers_test; DROPTABLEcustomers_test * ERRORatline1: ORA-00942:tableorviewdoesnotexist SQL>CREATETABLEcustomers_testASSELECT*FROMcustomers; Tablecreated. SQL>selectcount(*)fromcustomers_test; COUNT(*) ---------- 55500 SQL>
二、收集统计信息
SQL> SQL>EXECDBMS_STATS.GATHER_TABLE_STATS(user,'customers_test'); PL/SQLproceduresuccessfullycompleted. SQL>
三、开启负载监控
另外打开一个会话，通过sys用户登录，开启负载监控。其中，SEED_COL_USAGE的第三个参数表示监控的时间，单位是秒，300表示5分钟。
SQL>showuser USERis“SYS” SQL>BEGIN DBMS_STATS.SEED_COL_USAGE(null,null,300); END; /234 PL/SQLproceduresuccessfullycompleted. SQL>
四、使用explainplanfor查询执行计划
SQL>
SQL>EXPLAINPLANFOR
SELECT*
FROMcustomers_test
WHEREcust_city='LosAngeles'
ANDcust_state_province='CA'
ANDcountry_id=52790;23456
Explained.
SQL>
SQL>SELECTPLAN_TABLE_OUTPUT
FROMTABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basicrows'));2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Planhashvalue:2112738156
----------------------------------------------------
|Id|Operation|Name|Rows|
----------------------------------------------------
|0|SELECTSTATEMENT||1|
|1|TABLEACCESSFULL|CUSTOMERS_TEST|1|
----------------------------------------------------
8rowsselected.
SQL>
从执行计划来看，查询结果只有1列。我们暂且记下这个结果。
五、查看列使用信息
此时，我们可以通过REPORT_COL_USAGE来查看列的使用信息。
我们看到，Oracle帮我们检测到了一个有用的列组信息，包括customers_test、cust_city和cust_state_province三列。
SQL> SQL>SETLONG100000 SQL>SETLINES120 SQL>SETPAGES0 SQL>SELECTDBMS_STATS.REPORT_COL_USAGE(user,'customers_test') 2FROMDUAL; LEGEND: ....... EQ:UsedinsingletableEQualitypredicate RANGE:UsedinsingletableRANGEpredicate LIKE:UsedinsingletableLIKEpredicate NULL:Usedinsingletableis(not)NULLpredicate EQ_JOIN:UsedinEQualityJOINpredicate NONEQ_JOIN:UsedinNONEQualityJOINpredicate FILTER:UsedinsingletableFILTERpredicate JOIN:UsedinJOINpredicate GROUP_BY:UsedinGROUPBYexpression ............................................................................... ############################################################################### COLUMNUSAGEREPORTFORSH.CUSTOMERS_TEST ......................................... 1.COUNTRY_ID:EQ 2.CUST_CITY:EQ 3.CUST_STATE_PROVINCE:EQ 4.(CUST_CITY,CUST_STATE_PROVINCE, COUNTRY_ID):FILTER ############################################################################### SQL>
六、创建扩展统计信息
检测工作完成后，我们可以通过CREATE_EXTENDED_STATS方法来创建扩展统计信息。其中，黄色标注部分就是创建对象的名称。
SQL> SQL>SELECTDBMS_STATS.CREATE_EXTENDED_STATS(user,'customers_test')FROMDUAL; ############################################################################### EXTENSIONSFORSH.CUSTOMERS_TEST ................................ 1.(CUST_CITY,CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STUMZ$C3AIHLPBROI#SKA58H_Ncreated ############################################################################### SQL>
七、重新收集统计信息
SQL> SQL>EXECDBMS_STATS.GATHER_TABLE_STATS(user,'customers_test'); PL/SQLproceduresuccessfullycompleted. SQL>
八、查看USER_TAB_COL_STATISTICS，确认列统计信息
通过查询USER_TAB_COL_STATISTICS，我们可以获取到刚刚创建的列组对象，和第6步的输出结果是一致的。
SQL> SQL>COLCOLUMN_NAMEFORA30 SQL>SELECTCOLUMN_NAME,NUM_DISTINCT,HISTOGRAM FROMUSER_TAB_COL_STATISTICS WHERETABLE_NAME='CUSTOMERS_TEST' ORDERBY1;234 COUNTRY_ID19FREQUENCY CUST_CITY620HYBRID CUST_CITY_ID620NONE CUST_CREDIT_LIMIT8NONE CUST_EFF_FROM1NONE CUST_EFF_TO0NONE CUST_EMAIL1699NONE CUST_FIRST_NAME1300NONE CUST_GENDER2NONE CUST_ID55500NONE CUST_INCOME_LEVEL12NONE CUST_LAST_NAME908NONE CUST_MAIN_PHONE_NUMBER51344NONE CUST_MARITAL_STATUS11NONE CUST_POSTAL_CODE623NONE CUST_SRC_ID0NONE CUST_STATE_PROVINCE145FREQUENCY CUST_STATE_PROVINCE_ID145NONE CUST_STREET_ADDRESS49900NONE CUST_TOTAL1NONE CUST_TOTAL_ID1NONE CUST_VALID2NONE CUST_YEAR_OF_BIRTH75NONE SYS_STUMZ$C3AIHLPBROI#SKA58H_N620HYBRID 24rowsselected. SQL>
九、重新查询执行计划
我们看到，在第4步中查询执行计划中，Rows为1；现在呢，是867。这差距也忒大了点儿。
SQL>
SQL>EXPLAINPLANFOR
SELECT*
FROMcustomers_test
WHEREcust_city='LosAngeles'
ANDcust_state_province='CA'
ANDcountry_id=52790;23456
Explained.
SQL>
SQL>SELECTPLAN_TABLE_OUTPUT
FROMTABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basicrows'));2
Planhashvalue:2112738156
----------------------------------------------------
|Id|Operation|Name|Rows|
----------------------------------------------------
|0|SELECTSTATEMENT||867|
|1|TABLEACCESSFULL|CUSTOMERS_TEST|867|
----------------------------------------------------
8rowsselected.
SQL>
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。