Oracle 12c新特性之如何检测有用的多列统计信息详解

数据库  /  管理员 发布于 6年前   181

前言

之前和大家分享过Oracle 11g下的一个新特性――收集多列统计信息(///article/109514.htm)，今天和大家分享Oracle 12c的一个新特性――自动检测有用列组信息。二者相得益彰，大家可以具体情况酌情使用。

言归正传，我们可以针对一个表，基于特定的工作负荷，通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定我们需要哪些列组。当你不清除需要创建哪个扩展统计信息时，这个技术是非常有用的。需要注意的是，这种技术不适用于包含表达式列的统计工作。

接下来，我们通过例子来学习这个的新特性。

一、环境准备

首先，我们创建测试表customers_test，基于sh示例用户下的customers表。

SQL> select banner from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionPL/SQL Release 12.1.0.2.0 - ProductionCORE 12.1.0.2.0 ProductionTNS for Linux: Version 12.1.0.2.0 - ProductionNLSRTL Version 12.1.0.2.0 - ProductionSQL> SQL> conn sh/[email protected]> SQL> DROP TABLE customers_test;DROP TABLE customers_test  *ERROR at line 1:ORA-00942: table or view does not existSQL> CREATE TABLE customers_test AS SELECT * FROM customers;Table created.SQL> select count(*) from customers_test; COUNT(*)---------- 55500SQL>

二、收集统计信息

SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');PL/SQL procedure successfully completed.SQL>

三、开启负载监控

另外打开一个会话，通过sys用户登录，开启负载监控。其中，SEED_COL_USAGE的第三个参数表示监控的时间，单位是秒，300表示5分钟。

SQL> show userUSER is “SYS”SQL> BEGIN DBMS_STATS.SEED_COL_USAGE(null,null,300);END;/ 2 3 4PL/SQL procedure successfully completed.SQL>

四、使用explain plan for查询执行计划

SQL> SQL> EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city = 'Los Angeles' AND cust_state_province = 'CA' AND country_id = 52790; 2 3 4 5 6 Explained.SQL> SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2112738156----------------------------------------------------| Id | Operation | Name | Rows |----------------------------------------------------| 0 | SELECT STATEMENT |  | 1 || 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 1 |----------------------------------------------------8 rows selected.SQL>

从执行计划来看，查询结果只有1列。我们暂且记下这个结果。

五、查看列使用信息

此时，我们可以通过REPORT_COL_USAGE来查看列的使用信息。

我们看到，Oracle帮我们检测到了一个有用的列组信息，包括customers_test、cust_city和cust_state_province三列。

SQL> SQL> SET LONG 100000SQL> SET LINES 120SQL> SET PAGES 0SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test') 2 FROM DUAL;LEGEND:.......EQ : Used in single table EQuality predicateRANGE : Used in single table RANGE predicateLIKE : Used in single table LIKE predicateNULL : Used in single table is (not) NULL predicateEQ_JOIN : Used in EQuality JOIN predicateNONEQ_JOIN : Used in NON EQuality JOIN predicateFILTER : Used in single table FILTER predicateJOIN : Used in JOIN predicateGROUP_BY : Used in GROUP BY expression...............................................................................###############################################################################COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST.........................................1. COUNTRY_ID  : EQ2. CUST_CITY  : EQ3. CUST_STATE_PROVINCE  : EQ4. (CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID)  : FILTER###############################################################################SQL>

六、创建扩展统计信息

检测工作完成后，我们可以通过CREATE_EXTENDED_STATS方法来创建扩展统计信息。其中，黄色标注部分就是创建对象的名称。

SQL> SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;###############################################################################EXTENSIONS FOR SH.CUSTOMERS_TEST................................1. (CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID)  : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created###############################################################################SQL>

七、重新收集统计信息

SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');PL/SQL procedure successfully completed.SQL>

八、查看USER_TAB_COL_STATISTICS，确认列统计信息

通过查询USER_TAB_COL_STATISTICS，我们可以获取到刚刚创建的列组对象，和第6步的输出结果是一致的。

SQL> SQL> COL COLUMN_NAME FOR A30SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAMFROM USER_TAB_COL_STATISTICSWHERE TABLE_NAME = 'CUSTOMERS_TEST'ORDER BY 1; 2 3 4 COUNTRY_ID   19 FREQUENCYCUST_CITY  620 HYBRIDCUST_CITY_ID  620 NONECUST_CREDIT_LIMIT  8 NONECUST_EFF_FROM   1 NONECUST_EFF_TO   0 NONECUST_EMAIL  1699 NONECUST_FIRST_NAME  1300 NONECUST_GENDER   2 NONECUST_ID  55500 NONECUST_INCOME_LEVEL  12 NONECUST_LAST_NAME  908 NONECUST_MAIN_PHONE_NUMBER  51344 NONECUST_MARITAL_STATUS  11 NONECUST_POSTAL_CODE  623 NONECUST_SRC_ID   0 NONECUST_STATE_PROVINCE  145 FREQUENCYCUST_STATE_PROVINCE_ID  145 NONECUST_STREET_ADDRESS  49900 NONECUST_TOTAL   1 NONECUST_TOTAL_ID   1 NONECUST_VALID   2 NONECUST_YEAR_OF_BIRTH  75 NONESYS_STUMZ$C3AIHLPBROI#SKA58H_N 620 HYBRID24 rows selected.SQL>

九、重新查询执行计划

我们看到，在第4步中查询执行计划中，Rows为1；现在呢，是867。这差距也忒大了点儿。

SQL> SQL> EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city = 'Los Angeles' AND cust_state_province = 'CA' AND country_id = 52790; 2 3 4 5 6 Explained.SQL> SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 Plan hash value: 2112738156----------------------------------------------------| Id | Operation | Name | Rows |----------------------------------------------------| 0 | SELECT STATEMENT |  | 867 || 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |----------------------------------------------------8 rows selected.SQL>

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对AIDI的支持。