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

前言

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

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

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

一、环境准备

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

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> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

PL/sql procedure successfully completed.

sql>

三、开启负载监控

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

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> 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','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> 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
.........................................

COUNTRY_ID : EQ
CUST_CITY : EQ
CUST_STATE_PROVINCE : EQ
(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID) : FILTER
###############################################################################

sql>

六、创建扩展统计信息

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

sql> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user,'customers_test') FROM DUAL; ###############################################################################

EXTENSIONS FOR SH.CUSTOMERS_TEST
................................

(CUST_CITY,COUNTRY_ID) : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
###############################################################################

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> 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> 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','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>

总结

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

原文链接：https://www.f2er.com/oracle/64913.html

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

BANNER

COUNT(*)

PLAN_TABLE_OUTPUT

| Id | Operation | Name | Rows |

| 0 | SELECT STATEMENT | | 1 | | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 1 |

| Id | Operation | Name | Rows |

| 0 | SELECT STATEMENT | | 867 | | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |

猜你在找的Oracle相关文章

| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 1 |

| 0 | SELECT STATEMENT | | 867 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |