从11g开始引入了对列收集扩展统计信息，扩展统计信息包含了两种额外类型的统计信息：组合列和表达式统计信息。
组合列 对于真实数据来说，一个表中多个字段直接的数据通常具有关联性。比如在CUSTOMERS表中，CUST_STATE_PROVINCE字段会受到COUNTRY_ID字段值的影响，比如湖北只会出现在中国下面，如果只有基本的统计信息，优化器是没有办法知道这些关系的，如果在一个语句中这几个字段条件同时存在的话，优化器就会估算错误。这时如果有收集扩展统计信息的话，优化器就能知道它们之间的关系。
对组合列收集信息后，优化器能更好的计算出基数。可以通过函数DBMS_STATS.CREATE_EXTENDED_STATS去实现多个字段的组合收集。当组合字段的统计信息被收集以后，当这张表收集统计信息时，oracle会自动维护这个组合字段的统计信息，跟普通字段一样。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 ALTER SYSTEM FLUSH SHARED_POOL; conn xb/xb; drop table t1 purge; create table t1 as select 1 col1,2 col2 from dual connect by level>=5000 union all select 2 col1,1 col2 from dual connect by level>=5000; select dbms_stats.create_extended_stats(user,'t1','(COL1,COL2)') name from dual; NAME -------------------------------------------------------------------------- SYS_STUFLHATC5RBD6JHJZWT$X2AAH exec dbms_stats.gather_table_stats(user,'t1');
建完组合列和重新收集统计信息后，会看到一个额外由系统生成名称的列
1 2 3 4 5 6 7 8 9 10 col column_name for a60 select column_name,num_distinct,num_nulls,histogram from user_tab_col_statistics where table_name='T1'; COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM ------------------------------------------------------------ ------------ ---------- --------------------------------------------- COL1 2 0 NONE COL2 2 0 NONE SYS_STUFLHATC5RBD6JHJZWT$X2AAH 2 0 NONE >>>>====新生成的字段名
如果要查用户有哪些其他的扩展统计信息，可以查询视图USER_STAT_EXTENSIONS
1 2 3 4 5 6 7 8 9 10 col table_name for a30 col extension_name for a60 col extension for a60 select table_name,extension_name,extension from USER_STAT_EXTENSIONS where creator='USER'; TABLE_NAME EXTENSION_NAME EXTENSION ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ T1 SYS_STUFLHATC5RBD6JHJZWT$X2AAH ("COL1","COL2")
自动组合列检测 尽管组合列统计信息非常有用，能很方便的生成好的执行计划，但是一般来说不容易知道在一个特定的负载下该收集哪些组合列的信息。
自动组合列检测决定了在一个特定的负载状态下，表的哪些组合列的统计信息应该被收集。注意此功能不对函数字段生成扩展信息，只对组合列适用。自动组合列检测由以下三个步骤完成：
种子列使用 为了决定适当的组合列，oracle必须了解具有代表性的负载情况。负载情况可以通过SQL Tuning Set查到或者通过负载监控工具。通过过程DBMS_STATS.SEED_COL_USAGE来显示负载，并告诉oracle这个负载要观察多久。下面这个例子表示对当前系统监控5分钟。
1 2 3 4 begin dbms_stats.seed_col_usage(null,null,300); end; /
这个监控过程会收集储存在sys.col_usage$当中的信息并储存到sys.col_group_usage$去。包含在这个监控窗口范围内所有执行或解析的sql，当这个监控窗口结束，可以通过一个新的函数DBMS_STATS.REPORT_COL_USAGE去查看到所有表的特定列的使用信息。这个函数会生成一个报告，列出了表所有谓词条件里过滤、关联、group by等操作涉及到的列。
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 xb@ORA12C> select count(1) from t1 where col1=1 and col2=2; COUNT(1) ---------- 5000 xb@ORA12C> select dbms_stats.report_col_usage(user,'T1') from dual; DBMS_STATS.REPORT_COL_USAGE(USER,'T1') ----------------------------------------------------------------------- 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 XB.T1 ............................. 1. COL1 : EQ >>>>==== 2. COL2 : EQ >>>>==== 3. SYS_STUFLHATC5RBD6JHJZWT$X2AAH : EQ >>>>==== 4. (COL1, COL2) : FILTER >>>>==== ###############################################################################
创建组合列 对每张表调用函数DBMS_STATS.CREATE_EXTENDED_STATS，会自动根据之前监控窗口中得到的有用信息创建必要的组合列。当扩展统计信息创建以后，每当表统计信息被收集时，组合列的统计信息也会自动维护。
1 2 3 4 5 6 7 8 9 10 11 xb@ORA12C> select dbms_stats.create_extended_stats(user,'T1') from dual; DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T1') ------------------------------------------------------------------------------------- ############################################################################### EXTENSIONS FOR XB.T1 .................... 1. (COL1, COL2) : SYS_STUFLHATC5RBD6JHJZWT$X2AAH exists >>>>====我这里提示已存在 ###############################################################################
也可以手动指定要组合的列
1 xb@ORA12C> select dbms_stats.create_extended_stats(user,'T1','(COL1,COL2)') from dual;
重新收集统计信息 最后一步就是重新收集表的统计信息，这样新创建的组合列就会拥有统计信息。
1 exec dbms_stats.gather_table_stats(null,'T1')
SQL计划指示和组合列 sql计划指示不仅仅指示用来优化sql执行计划，可以参考12c Adaptive Statistics ,同样可以用来决定组合列是否可以有效解决基数估算不准的问题。如果一个sql计划指示创建了，同时优化器认为基数估算不准的问题可以被这个组合列解决，12cR1中oracle会在下次表分析的时候自动创建组合列。
但是到了12cR2的时候这个特性有了变化，自动创建组合列的功能默认是被关闭的，可以通过包DBMS_STATS包来开启或关闭。
1 2 3 exec dbms_stats.set_global_prefs ('AUTO_STAT_EXTENSIONS', 'OFF'); >>>>====表示不自动创建 exec dbms_stats.set_global_prefs ('AUTO_STAT_EXTENSIONS', 'ON'); >>>>====表示会根据SQL计划指示自动创建
如果你的版本是12.1的话，想关闭这个特性可以通过打补丁解决21171382
表达式统计信息 可以对表达式或函数收集扩展统计信息，去帮助优化器去估算那些谓词条件中包含表达式或者函数的情况。比如条件中含有upper(name)=,那么这时候收集upper(name)的扩展统计信息就会非常有帮助。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 drop table t1 purge; create table t1 (name varchar2(10)); insert into t1 values('xb'); insert into t1 values('XB'); insert into t1 values('Xb'); commit; select dbms_stats.create_extended_stats(NULL,'T1','(UPPER(NAME))') from dual; DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'T1','(UPPER(NAME))') ------------------------------------------------------------------------ SYS_STUMMY#LUDFVUOI56L724JL4ZO
跟组合列一样，定义表达式统计信息以后需要重新收集统计信息。收集完毕后，可以通过USER_TAB_COL_STATISTICS视图查询，同样详细信息也可以通过USER_STAT_EXTENSIONS视图查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 col column_name for a60 select column_name,num_distinct,num_nulls,histogram from user_tab_col_statistics where table_name='T1'; COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM ------------------------------------------------------------ ------------ ---------- --------------------------------------------- NAME 3 0 NONE SYS_STUMMY#LUDFVUOI56L724JL4ZO 1 0 NONE >>>>====可以看到经过函数以后的估算值是1 col table_name for a30 col extension_name for a60 col extension for a60 select table_name,extension_name,extension from USER_STAT_EXTENSIONS where creator='USER'; TABLE_NAME EXTENSION_NAME EXTENSION ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ T1 SYS_STUMMY#LUDFVUOI56L724JL4ZO (UPPER("NAME"))
扩展统计信息的限制 扩展统计信息只适用于那些谓词条件为等于或者IN-LIST。如果列上有直方图信息但是组合列没有，则扩展统计信息不被使用。