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

时间:2021-08-01 05:32:43

前言

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

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

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

一、环境准备

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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>

二、收集统计信息

?
1
2
3
4
5
6
SQL>
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');
 
PL/SQL procedure successfully completed.
 
SQL>

三、开启负载监控

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

?
1
2
3
4
5
6
7
8
9
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查询执行计划

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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三列。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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方法来创建扩展统计信息。其中,黄色标注部分就是创建对象的名称。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>

七、重新收集统计信息

?
1
2
3
4
5
6
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步的输出结果是一致的。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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。这差距也忒大了点儿。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>

总结

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

原文链接:http://blog.itpub.net/30162081/viewspace-2124652/