这里有新鲜出炉的精品教程,程序狗速度看过来!
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
这篇文章主要给大家介绍了 Oracle 12c 新特性之如何检测有用的多列统计信息的相关资料,文中介绍的非常详细,对大家具有一定的参考价值,需要的朋友们下面来一起看看吧。
前言
之前和大家分享过 Oracle 11g 下的一个新特性——收集多列统计信息 (/article/17/0427/332795.html),今天和大家分享 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 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>
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对 PHPERZ 的支持。
来源: http://www.phperz.com/article/17/0524/332794.html