Oracle 12c新特性之如何检测有用的多列统计信息详解
前言
之前和大家分享过Oracle 11g下的一个新特性——收集多列统计信息(一、环境准备
首先，我们创建测试表customers_test，基于sh示例用户下的customers表。
SQL> select banner from v$version;
BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
PL/SQL Release 12.1.0.2.0 – Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 – Production
NLSRTL Version 12.1.0.2.0 – Production
SQL>
SQL> conn sh/sh@HOEGH
Connected.
SQL>
SQL> DROP TABLE customers_test;
DROP TABLE customers_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE customers_test AS SELECT * FROM customers;
Table created.
SQL> select count(*) from customers_test;
COUNT(*)
———-
55500
SQL>
二、收集统计信息
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 user
USER is “SYS”
SQL> BEGIN
DBMS_STATS.SEED_COL_USAGE(null,null,300);
END;
/ 2 3 4
PL/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 100000
SQL> SET LINES 120
SQL> SET PAGES 0
SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, ‘customers_test’)
2 FROM DUAL;
LEGEND:
…….
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
…………………………………………………………………….
###############################################################################
COLUMN USAGE REPORT FOR SH.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> 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 A30
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = ‘CUSTOMERS_TEST’
ORDER BY 1; 2 3 4
COUNTRY_ID 19 FREQUENCY
CUST_CITY 620 HYBRID
CUST_CITY_ID 620 NONE
CUST_CREDIT_LIMIT 8 NONE
CUST_EFF_FROM 1 NONE
CUST_EFF_TO 0 NONE
CUST_EMAIL 1699 NONE
CUST_FIRST_NAME 1300 NONE
CUST_GENDER 2 NONE
CUST_ID 55500 NONE
CUST_INCOME_LEVEL 12 NONE
CUST_LAST_NAME 908 NONE
CUST_MAIN_PHONE_NUMBER 51344 NONE
CUST_MARITAL_STATUS 11 NONE
CUST_POSTAL_CODE 623 NONE
CUST_SRC_ID 0 NONE
CUST_STATE_PROVINCE 145 FREQUENCY
CUST_STATE_PROVINCE_ID 145 NONE
CUST_STREET_ADDRESS 49900 NONE
CUST_TOTAL 1 NONE
CUST_TOTAL_ID 1 NONE
CUST_VALID 2 NONE
CUST_YEAR_OF_BIRTH 75 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N 620 HYBRID
24 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>
总结
本篇文章到此结束，如果您有相关技术方面疑问可以联系我们技术人员远程解决，感谢大家支持本站！
