Statistics on Column Groups
单个列统计信息对于判断where子句中的单个谓词的选择性是非常有用的。然而，当where子句中包含来自相同表的不同列的多个谓词时，单个列统计信息不能显示列之间的关系。使用列组(column group)就是用来解决这个问题的。优化器单独计算谓词的选择性，然后合并它们。然而，如果在单列之间存在关联，那么优化器当评估基数时不会考虑它，优化器会使用每个表谓词的选择性来乘以行数来评估基数。
下面的语句查询dba_tab_col_statistics表来显示关于sh.customers表中列cust_state_province与country_id列的统计信息。
SQL> COL COLUMN_NAME FORMAT a20
SQL> COL NDV FORMAT 999
SQL> SELECT COLUMN_NAME, NUM_DISTINCT AS "NDV", HISTOGRAM
2 FROM DBA_TAB_COL_STATISTICS
3 WHERE OWNER = 'SH'
4 AND TABLE_NAME = 'CUSTOMERS'
5 AND COLUMN_NAME IN ('CUST_STATE_PROVINCE', 'COUNTRY_ID');
COLUMN_NAME NDV HISTOGRAM
-------------------- ---- ---------------
CUST_STATE_PROVINCE 145 FREQUENCY
COUNTRY_ID 19 FREQUENCY
下面的语句查询住在California的客户人数3341人:
SQL> SELECT COUNT(*)
2 FROM sh.customers
3 WHERE cust_state_province = 'CA';
COUNT(*)
----------
3341
来显示查询state为CA，country_id为52790(USA)的客户人数的查询执行
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM sh.customers
4 WHERE cust_state_province = 'CA'
5 AND country_id=52790;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1115 | 205K| 423 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1115 | 205K| 423 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre
shold
17 rows selected.
基于单列country_id和cust_state_province列的统计信息，优化器评估住在USA的California的客户人数是1115,而实际上有3341人，但优化器不知道，所以通过所有谓词减少了返回的行数因此大大降低了评估基数。可以通过收集列组统计信息来让优化器知晓列country_id与cust_state_province之间的真实关系。
自动与手动列组统计信息
Oracle数据库可以自动或手动创建列组统计信息。优化器可以使用SQL执行计划指令来生成更优的执行计划。如果dbms_stats引用参数auto_stat_extensions被设置为ON(缺省值为OFF)，那么SQL执行计划指令基于工作量中谓词的使用情况可以自动触发来创建列组统计信息。可以通过set_table_prefs,set_global_prefs或set_schema_prefs过程来设置auto_stat_extensions。
当想要手动管理列组统计信息时，可以使用dbms_stats来执行以下操作:
.探测列组
.创建以前探测到的列组
.手动创建列组并收集列组统计信息
列组统计信息用户接口
有几个dbms_stats程序单元有与列组相关的引用参数
seed_col_usage过程，迭代指定工作量中的SQL语句，编译它们，然后查看在这些语句谓词中出现列的使用信息。为了决定合适的列组，数据库必须观察一个有代表性的工作量。在监控期间不需要运行查询本身。可以对在工作量中那些运行时间长的查询执行explain plan来确保数据库记录这些查询所使用的列组信息。
report_col_usage函数，生成一个报告列出在工作量中所看到的过滤谓词，连接谓词与group by子句中的列。可以使用这个函数来检查对于指定表所记录的列使用信息。
create_extended_stats函数，创建扩展，它可以是列组或表达式。当用户手动或自动统计信息收集任务对表收集统计信息时数据库会对扩展收集统计信息。
auto_stat_extensions引用参数，控制自动创建扩展，包括列组，当优化器统计信息被收集时，使用set_table_prefs,set_schema_prefs或set_global_prefs来设置这个引用参数。当auto_stat_extensions被设置为off(缺省值)时，数据库不会自动创建列组统计信息。为了创建扩展，你必须执行create_extended_stats函数或在dbms_stats API中的method_opt参数中显性指定扩展统计信息。当auto_stat_extensions设置为ON时，一个SQL执行计划指令基于工作量中谓词中列的使用信息可以触发自动创建列组统计信息。
为特定的工作量检测有用的列组
可以使用dbms_stats.seed_col_usage与report_col_usage来基于特定工作量来决定那个表需要列组。当你不知道需要创建什么样的扩展统计信息时这种技术很有用。这种技术对于扩展统计信息不会工作。
假设存在以下情况:
.查询sh.customers_test表(用customers表来创建)并在谓词中使用了country_id与cust_state_province列但基数评估不正确。
.想要数据库监控工作量5分钟(300秒)。
.想要数据库自动判断需要那些列组。
为了检测列组需要执行以下操作:
1.启动SQL*Plus或SQL Developer，并以用户sh登录数据库
2.创建表customers_test并收集统计信息:
SQL> DROP TABLE customers_test; Table dropped. SQL> CREATE TABLE customers_test AS SELECT * FROM customers; Table created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test'); PL/SQL procedure successfully completed.
3.启用工作量监控
在不同的SQL*Plus会话中，以sys用户登录并执行以下的PL/SQL程序来启用监控300秒:
SQL> BEGIN 2 DBMS_STATS.SEED_COL_USAGE(null,null,300); 3 END; 4 / PL/SQL procedure successfully completed.
4.以用户sh来在使用工作量的情况下对两个查询解析它们的执行计划。
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM customers_test
4 WHERE cust_city = 'Los Angeles'
5 AND cust_state_province = 'CA'
6 AND country_id = 52790;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 1 |
----------------------------------------------------
8 rows selected.
SQL> EXPLAIN PLAN FOR
2 SELECT country_id, cust_state_province, count(cust_city)
3 FROM customers_test
4 GROUP BY country_id, cust_state_province;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1949 |
| 1 | HASH GROUP BY | | 1949 |
| 2 | TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------
9 rows selected.
第一个执行计划显示基数为1而查询返回932行记录，第二个执行计划显示基数为1949而查询返回145行记录。
5.可选操作，检查对表customers_test所记录的列使用信息
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
5. (CUST_STATE_PROVINCE, COUNTRY_ID) : GROUP_BY
###############################################################################
在上面的报告中，前三个列是第一个监控查询中等值谓词中所使用的三个列：
... WHERE cust_city = 'Los Angeles' AND cust_state_province = 'CA' AND country_id = 52790;
所有三个列出现在相同的where子句中，因此报告显示他们作为一组。在第二个查询中，两个列出现在group by子句中，因此报告标记它们作为group_by。在filter与group_by中的列组就是列组的候选者。
在工作量监控下创建所检测到的列组
可以使用dbms_stats.create_extended_stats函数来为执行dbms_stats.seed_col_usage所检测到的列组来创建列组，具体操作如下:
1.基于在监控窗口期间所捕获到的列使用信息来为customers_test表创建列组，执行下面的查询
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
2. (CUST_STATE_PROVINCE, COUNTRY_ID) : SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###############################################################################
数据库将为customers_test表创建两个列组:一个列组是过滤谓词，一个列组是group by操作。
2.重新收集表统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test'); PL/SQL procedure successfully completed.
3.以用户sh来查询user_tab_col_statistics视图来判断数据库创建了那些额外统计信息:
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM 2 FROM USER_TAB_COL_STATISTICS 3 WHERE TABLE_NAME = 'CUSTOMERS_TEST' 4 ORDER BY 1; 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_STU#S#WF25Z#QAHIHE#MOFFMM_ 145 NONE SYS_STUMZ$C3AIHLPBROI#SKA58H_N 620 HYBRID 25 rows selected.
上面的查询显示了由dbms_stats.create_extended_stats函数所返回的两个列组名。为CUST_CITY, CUST_STATE_PROVINCE和COUNTRY_ID列所创建的列组有一个HYBRID类型的直方图统计信息。
4.再次解析之前的两个查询语句的执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM customers_test
4 WHERE cust_city = 'Los Angeles'
5 AND cust_state_province = 'CA'
6 AND country_id = 52790;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 874 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 874 |
----------------------------------------------------
8 rows selected.
SQL> EXPLAIN PLAN FOR
2 SELECT country_id, cust_state_province, count(cust_city)
3 FROM customers_test
4 GROUP BY country_id, cust_state_province;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 145 |
| 1 | HASH GROUP BY | | 145 |
| 2 | TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------
9 rows selected.
第一个查询评估的基数是874，要返回的记录数是932，第二个查询评估的基数是145，要返回的记录数是145,这样基数评估的记录数与实际返回的记录已经非常接近了，这就是列组统计信息所带来的好处。
手动创建与收集列组统计信息
在有些情况下，可能知道想要创建的列组。dbms_stats.gather_table_stats函数的method_opt参数可以自动创建与收集列组统计信息。可以通过使用for columns来指定列组从而来创建一个新的列组。
假设存在以下情况:
.想要对sh.customers表上的cust_state_province与country_id列创建列组。
.想要对sh.customers表与新的列组收集统计信息。
手动创建与收集列组统计信息执行以下操作:
1.启动SQL*Plus并以sh用户登录数据库。
2.使用以下PL/SQL程序来创建列组并收集统计信息:
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers', 3 METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 4 'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' ); 5 END; 6 / PL/SQL procedure successfully completed.
显示列组信息
为了获得列组名，可以使用dbms_stats.show_extended_stats_name函数或数据库视图。也可以使用视图来获得信息比如，distinct值的数量与列组是否有直方图统计信息。
1.启动SQL*Plus并以sh用户登录数据库。
2.为了获得列组名，执行以下PL/SQL程序
SQL> SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 'sh','customers', 2 '(cust_state_province,country_id)' ) col_group_name 3 FROM DUAL; COL_GROUP_NAME ------------------------------------ SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
查询user_stat_extensions视图
SQL> SELECT EXTENSION_NAME, EXTENSION
2 FROM USER_STAT_EXTENSIONS
3 WHERE TABLE_NAME='CUSTOMERS';
EXTENSION_NAME EXTENSION
----------------------------------------------------------------------- ------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ ("CUST_STATE_PROVINCE","COUNTRY_ID")
3.查询创建的列组的distinct值的数量并查看是否创建了直方图
SQL> SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
2 FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
3 WHERE e.EXTENSION_NAME=t.COLUMN_NAME
4 AND e.TABLE_NAME=t.TABLE_NAME
5 AND t.TABLE_NAME='CUSTOMERS';
COL_GROUP NUM_DISTINCT HISTOGRAM
---------------------------------------------------------------------- ------------ ---------
("CUST_STATE_PROVINCE","COUNTRY_ID") 145 FREQUENCY
删除列组
可以使用dbms_stats.drop_extended_stats函数来从表中删除列组
SQL> BEGIN 2 DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers', 3 '(cust_state_province, country_id)' ); 4 END; 5 / PL/SQL procedure successfully completed.