Oracle多列统计信息

Oracle多列统计信息

   通常，当我们将SQL语句提交给Oracle数据库时，Oracle会选择一种最优方式来执行，这是通过查询优化器Query Optimizer来实现的。CBO（Cost-Based Optimizer）是Oracle默认使用的查询优化器模式。在CBO中，SQL执行计划的生成，是以一种寻找成本（Cost）最优为目标导向的执行计划探索过程。所谓成本（Cost）就是将CPU和IO消耗整合起来的量化指标，每一个执行计划的成本就是经过优化器内部公式估算出的数字值。

        我们在写SQL语句的时候，经常会碰到where子句后面有多个条件的情况，也就是根据多列的条件筛选得到数据。默认情况下，oracle会把多列的选择率（selectivity）相乘从而得到where语句的选择率，这样有可能造成选择率（selectivity）不准确，从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断，从而生成准确的执行计划，oracle在11g数据库中引入了收集多列统计信息。本文通过对测试表的多条件查询，介绍收集多列统计信息的重要性。

---

---

optimizer对于cardinality值的估算是否准确关系到能否生成最优的执行计划，而cardinality值估算的准确性又取决于SQL中各个对象的统计信息是否完整、是否能真实反映出对象的数据分布情况。因此使用何种方法收集统计信息是很有讲究的：对于数据倾斜度较大的表开启histogram，在此基础上如果有多个列存在相关性，那么multicolumns statistics又是一个更好的选择，下面用实验来证明multicolumns statistics的独到之处

###创建测试用表
drop table cgtest1;    
create table cgtest1 (c1 number,c2 varchar2(2),c3 varchar2(20)) tablespace ts_info_dat_01;    


declare    
begin    
for i in 1..5000 loop    
insert into cgtest1 values(1,'AA',dbms_random.string('l',20));    
insert into cgtest1 values(2,'BB',dbms_random.string('l',20));    
insert into cgtest1 values(3,'CC',dbms_random.string('l',20));    
insert into cgtest1 values(4,'DD',dbms_random.string('l',20));    
end loop;    
commit;    
end;    
/    


insert into cgtest1 values(11,'A','AAAAAAA');    
insert into cgtest1 values(22,'B','BBBBBBB');    
insert into cgtest1 values(33,'C','CCCCCCC');    
insert into cgtest1 values(44,'D','DDDDDDD');    
commit;    


SQL> select count(1) from cgtest1;    


  COUNT(1)    
----------    
     20004    


select c1,c2,count(1) from cgtest1 group by c1,c2;    
        C1 C2   COUNT(1)    
---------- -- ----------    
         1 AA       5000    
         2 BB       5000    
         3 CC       5000    
         4 DD       5000    
        11 A           1    
        22 B           1    
        33 C           1    
        44 D           1    


###收集cgtest1表的统计信息(但不收集histogram信息)
---收集前确认默认的estimate_percent为auto_sample_size    
SQL> SELECT dbms_stats.get_prefs('estimate_percent',NULL,NULL) from dual;    


DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT',NULL,NULL)    
------------------------------------------------------------------------------------------------------------------------------------------------------    
DBMS_STATS.AUTO_SAMPLE_SIZE    


exec dbms_stats.gather_table_stats(ownname=>'ad',tabname=>'cgtest1',method_opt=>'FOR ALL COLUMNS SIZE 1');    


set linesize 150    
SQL> select owner,table_name,NUM_DISTINCT,sample_size,column_name,histogram from dba_tab_col_statistics where owner='AD' and table_name='CGTEST1';    


OWNER                          TABLE_NAME                     NUM_DISTINCT SAMPLE_SIZE COLUMN_NAME                    HISTOGRAM    
------------------------------ ------------------------------ ------------ ----------- ------------------------------ ---------------    
AD                             CGTEST1                                   8       20004 C1                             NONE    
AD                             CGTEST1                                   8       20004 C2                             NONE    
AD                             CGTEST1                               19938       20004 C3                             NONE    


---c1=1 and c2='AA'实际返回值5000与optimizer估算值313还是有不少差距
SQL> select count(*) from cgtest1 where c1=1 and c2='AA';    


  COUNT(*)    
----------    
      5000    
          
explain plan for select count(*) from cgtest1 where c1=1 and c2='AA';    


set linesize 150    
SQL> select * from table(dbms_xplan.display());    


PLAN_TABLE_OUTPUT    
------------------------------------------------------------------------------------------------------------------------------------------------------    
Plan hash value: 4200988577    


------------------------------------------------------------------------------    
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |         |     1 |     6 |    21   (0)| 00:00:01 |    
|   1 |  SORT AGGREGATE    |         |     1 |     6 |            |          |    
|*  2 |   TABLE ACCESS FULL| CGTEST1 |   313 |  1878 |    21   (0)| 00:00:01 |    
------------------------------------------------------------------------------    


---c1=11 and c2='A'实际返回值1与optimizer估算值313还是有不少差距
SQL> select count(*) from cgtest1 where c1=11 and c2='A';    


  COUNT(*)    
----------    
         1    
          
explain plan for select count(*) from cgtest1 where c1=11 and c2='A';    
SQL> select * from table(dbms_xplan.display());    


PLAN_TABLE_OUTPUT    
------------------------------------------------------------------------------------------------------------------------------------------------------    
Plan hash value: 4200988577    


------------------------------------------------------------------------------    
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |         |     1 |     6 |    21   (0)| 00:00:01 |    
|   1 |  SORT AGGREGATE    |         |     1 |     6 |            |          |    
|*  2 |   TABLE ACCESS FULL| CGTEST1 |   313 |  1878 |    21   (0)| 00:00:01 |    
------------------------------------------------------------------------------    


上面的两个查询中cardinality计算方法：num_rows*(1/num_distinct_c1)*(1/num_distinct_c2)=312.56，和执行计划里的313吻合，因为没有收集列的histogram信息所以optimizer估算返回行数和实际返回行数还是有不少差距，下面对c1、c2列收集histogram


###收集c1、c2列的直方图后重新执行上面两个查询
exec dbms_stats.gather_table_stats(ownname=>'ad',tabname=>'cgtest1',method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS c1 size skewonly,c2 size skewonly');    


set linesize 170    
SQL> select owner,table_name,NUM_DISTINCT,density,num_buckets,sample_size,column_name,histogram from dba_tab_col_statistics where owner='AD' and table_name='CGTEST1';    


OWNER                          TABLE_NAME                     NUM_DISTINCT    DENSITY NUM_BUCKETS SAMPLE_SIZE COLUMN_NAME                    HISTOGRAM    
------------------------------ ------------------------------ ------------ ---------- ----------- ----------- ------------------------------ ---------------    
AD                             CGTEST1                                   8 .000024995           8       20004 C1                             FREQUENCY    
AD                             CGTEST1                                   8 .000024995           8       20004 C2                             FREQUENCY    
AD                             CGTEST1                               19938 .000050155           1       20004 C3                             NONE    


对于c1、c2列density值的计算：1/(num_rows*2)=1/(20004*2)=0.000024995
对于c2列因为没有直方图，density值是这样计算出来的：1/num_distinct_c3=0.000050155


SQL> col column_name format a30    
SQL> col endpoint_actual_value format a50    
SQL> set linesize 170    
SQL> set pagesize 100    
select owner,table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='CGTEST1';    
OWNER                          TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE    
------------------------------ ------------------------------ ------------------------------ --------------- --------------    
AD                             CGTEST1                        C1                                        5000              1    
AD                             CGTEST1                        C1                                       10000              2    
AD                             CGTEST1                        C1                                       15000              3    
AD                             CGTEST1                        C1                                       20000              4    
AD                             CGTEST1                        C1                                       20001             11    
AD                             CGTEST1                        C1                                       20002             22    
AD                             CGTEST1                        C1                                       20003             33    
AD                             CGTEST1                        C1                                       20004             44    
AD                             CGTEST1                        C2                                           1     3.3750E+35    
AD                             CGTEST1                        C2                                        5001     3.3882E+35    
AD                             CGTEST1                        C2                                        5002     3.4269E+35    
AD                             CGTEST1                        C2                                       10002     3.4403E+35    
AD                             CGTEST1                        C2                                       10003     3.4788E+35    
AD                             CGTEST1                        C2                                       15003     3.4924E+35    
AD                             CGTEST1                        C2                                       15004     3.5308E+35    
AD                             CGTEST1                        C2                                       20004     3.5446E+35    
AD                             CGTEST1                        C3                                           0     3.3882E+35    
AD                             CGTEST1                        C3                                           1     6.3594E+35    


---c1=1 and c2='AA'作为predicate执行查询，看下这次是否cardinality值会更加接近真实返回值
select count(*) from cgtest1 where c1=1 and c2='AA';    
 COUNT(*)    
----------    
      5000    
          
set linesize 150    
SQL> explain plan for select count(*) from cgtest1 where c1=1 and c2='AA';    


Explained.    


SQL> select * from table(dbms_xplan.display());    
PLAN_TABLE_OUTPUT    
------------------------------------------------------------------------------------------------------------------------------------------------------    
Plan hash value: 4200988577    


------------------------------------------------------------------------------    
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |         |     1 |     6 |    21   (0)| 00:00:01 |    
|   1 |  SORT AGGREGATE    |         |     1 |     6 |            |          |    
|*  2 |   TABLE ACCESS FULL| CGTEST1 |  1250 |  7500 |    21   (0)| 00:00:01 |    
------------------------------------------------------------------------------    


optimizer里的rows是这样预估出来的:num_rows*(5000/20004)*(5000/20004)=20004*0.0624=1248.2496，相比313更接近于真实值5000，可见有了histogram之后的估算更加准确了


---c1=11 and c2='A'作为predicate执行查询，看下这次是否cardinality值会更加接近真实返回值
SQL> select count(*) from cgtest1 where c1=11 and c2='A';    


  COUNT(*)    
----------    
         1    
          
explain plan for select count(*) from cgtest1 where c1=11 and c2='A';    
SQL> select * from table(dbms_xplan.display());    
PLAN_TABLE_OUTPUT    
------------------------------------------------------------------------------------------------------------------------------------------------------    
Plan hash value: 4200988577    


------------------------------------------------------------------------------    
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |         |     1 |     6 |    21   (0)| 00:00:01 |    
|   1 |  SORT AGGREGATE    |         |     1 |     6 |            |          |    
|*  2 |   TABLE ACCESS FULL| CGTEST1 |     1 |     6 |    21   (0)| 00:00:01 |    
------------------------------------------------------------------------------    


optimizer里的rows是这样预估出来的:num_rows*(1/20004)*(1/20004)=0.00005，近似取值为1


收集了histogram后的cardinality值比没有histogram的情况虽然更接近真实值，但还是有不少差距，optimizer能否统计出更加精确的cardinality，轮到multicolumns statistics(多列统计)出场了，多列统计(multicolumns statistics)又叫列组统计(column group statistics)，可以根据列与列之间的相关性将相关程度高的几列划入column group，之后的统计信息就是基于这个column group进行收集，本例cgtest1表里的c1、c2两个字段就具有一定的相关性，例如c1=1的字段只和c2='AA'的字段组合成一行，c1=1的字段不会和除了c2='AA'以外的值组合成一行，这就是c1、c2之间存在明显的相关性，所以c1和c2可以构成一个column group来形成更精确的统计信息，对column group收集统计信息的方法有两种：
1、采纳系统检测工作负载后给出的建议值后收集统计，如果DBA对表里数据构成情况及表中哪些列具有相关性事先不知道的情况下可以采用这种方法，oracle会根据当前的负载给出哪些表里的哪几个列之间存在相关性的建议，DBA如果采纳这个建议就可以在这几个列上创建出column group
2、手动创建column group后再收集统计信息，对表中具有相关性的列心知肚明，就可以使用手动创建的方法


下面简要介绍一下这两种方法：
###方法1：采纳系统检测工作负载后给出的建议值来生成column group
这个方法里又有两种选择，既可以让oracle针对特定的SQL语句来评估是否有创建column groups的必要，也可以从sql cursor cache、auto workload repository等已经生成的负载里兜取已经执行过的SQL语句来评估是否可以创建column groups    
---针对select count(*) from cgtest1 where c1=1 and c2='AA'让oracle生成创建column group的建议    
exec dbms_stats.seed_col_usage(NULL,NULL,TIME_limit=>100);    


explain plan for select count(*) from cgtest1 where c1=1 and c2='AA';    


set long 20000    
set pagesize 100    
select dbms_stats.report_col_usage(ownname=>'AD',tabname=>'cgtest1') from dual;    


DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>'AD',TABNAME=>'CGTEST1')    
--------------------------------------------------------------------------------    
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 AD.CGTEST1    
..................................    


1. C1                                  : EQ    
2. C2                                  : EQ    
3. (C1, C2)                            : FILTER    
###############################################################################    


***根据上面(C1, C2):filter的建议，生成column group: SYS_STUF3GLKIOP5F4B0BTTCFTMX0W
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(ownname=>'AD',tabname=>'cgtest1') FROM DUAL;    
DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'AD',TABNAME=>'CGTEST1')    
--------------------------------------------------------------------------------    
###############################################################################    


EXTENSIONS FOR AD.CGTEST1    
.........................    


1. (C1, C2)                            : SYS_STUF3GLKIOP5F4B0BTTCFTMX0W created    
###############################################################################    


***dba_stat_extensions查询column group信息
COL EXtension format a50    
set linesize 170    
SQL> select * from dba_stat_extensions where table_name='CGTEST1';    


OWNER                          TABLE_NAME                     EXTENSION_NAME                 EXTENSION                                          CREATO DRO    
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------- ------ ---    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W ("C1","C2")                                        USER   YES    


***SYS_STUF3GLKIOP5F4B0BTTCFTMX0W是系统为column group自动生成的名称，可以把它看作表中的一个列，针对SYS_STUF3GLKIOP5F4B0BTTCFTMX0W列生成统计信息
set linesize 170    
col extension format a15    
SQL> select t1.owner,t1.table_name,t1.column_name,t2.extension,NUM_DISTINCT,sample_size,histogram from dba_tab_col_statistics t1,dba_stat_extensions t2 where t1.owner='AD' and t1.table_name='CGTEST1' and t1.owner=t2.owner and t1.table_name=t2.table_name and t1.column_name=t2.EXTENSION_NAME;    


no rows selected    


exec dbms_stats.gather_table_stats(ownname=>'ad',tabname=>'cgtest1',method_opt=>'FOR COLUMNS SYS_STUF3GLKIOP5F4B0BTTCFTMX0W SIZE skewonly');    


SQL> select t1.owner,t1.table_name,t1.column_name,t2.extension,NUM_DISTINCT,sample_size,histogram from dba_tab_col_statistics t1,dba_stat_extensions t2 where t1.owner='AD' and t1.table_name='CGTEST1' and t1.owner=t2.owner and t1.table_name=t2.table_name and t1.column_name=t2.EXTENSION_NAME;    


OWNER                          TABLE_NAME                     COLUMN_NAME                    EXTENSION       NUM_DISTINCT SAMPLE_SIZE HISTOGRAM    
------------------------------ ------------------------------ ------------------------------ --------------- ------------ ----------- ---------------    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W ("C1","C2")                8       20004 FREQUENCY    


可以看到已经为SYS_STUF3GLKIOP5F4B0BTTCFTMX0W生成了统计，这个统计就是我们开头提到的多列统计(multicolumns statistics)或者列组统计(column group statistics)


注：dbms_stats.seed_col_usage也可以从sql tuning set里分析出column group的候选对象，用法如下
---从sql cursor cache里兜取出语句部分语句让oracle来评估(需要先建立sql tuning set)    
EXEC DBMS_SQLTUNE.CREATE_SQLSET('cgsts1');    


***按照first_load_time排序后选择最新的20条语句创建出sql tuning sets
DECLARE    
 cur DBMS_SQLTUNE.SQLSET_CURSOR;    
BEGIN    
OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(basic_filter=>'parsing_schema_name <> ''SYS'' AND sql_text like ''select%and%'' AND first_load_time > ''2015-01-01/01:36:34'' and first_load_time < ''2015-01-22/01:36:34''',ranking_measure1=>'first_load_time',result_limit=>20)) P;    
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'cgsts1',populate_cursor => cur);     
END;    
/    


SQL> select count(*) from dba_sqlset_statements where sqlset_name='cgsts1';    


  COUNT(*)    
----------    
        20    


***使用dbms_stats.seed_col_usage对cgsts1里的20条sql给出是否创建column group的建议
赋予执行seed_col_usage所需的权限    
grant analyze any,analyze any dictionary to ad;    
          
exec dbms_stats.seed_col_usage(sqlset_name=>'cgsts1',owner_name=>'AD',time_limit=>300);    


***针对sql tuning set中的某个表生成建议报告，前提是这个表必须要有统计信息
set long 2000000    
set pagesize 500    
select dbms_stats.report_col_usage(ownname=>'AD',tabname=>'CA_B_SNAPSHOT_4_2014') from dual;    


***下面是报告详细内容，最后一行用(ACCT_ID, BILL_MONTH, STS)，表明这三个字段是一起进行查询的，可以建立一个基于此三个字段的column group
DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>'AD',TABNAME=>'CA_B_SNAPSHOT_4_2014')    
--------------------------------------------------------------------------------    
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 AD.CA_B_SNAPSHOT_4_2014    
..................................................    


1. R_ID                             : EQ    
2. BEGIN_DATE                          : EQ RANGE    
3. BILL_MONTH                          : EQ    
4. END_DATE                            : EQ    
5. RATE_ID                             : EQ_JOIN    
6. STS                                 : EQ    
7. (ACCT_ID, BILL_MONTH, STS)          : FILTER    
###############################################################################    


***创建column group
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(ownname=>'AD',tabname=>'CA_B_SNAPSHOT_4_2014',extension=>NULL) FROM DUAL;    


###方法2：手动创建column group
---手动创建column group后再通过dbms_stats.gather_table_stats收集统计
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(ownname=>'AD',tabname=>'cgtest1',extension=>'(c1,c2)') FROM DUAL;    
DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'AD',TABNAME=>'CGTEST2',EXTENSION=>'(C1,C2)')    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
SYS_STU3RTXGYOX7NS$MIUDXQDMQ0C    


exec dbms_stats.gather_table_stats(ownname=>'AD',tabname=>'cgtest1',method_opt=>'FOR COLUMNS SYS_STU3RTXGYOX7NS$MIUDXQDMQ0C SIZE skewonly');)    


---或者一步到位：直接对c1、c2列执行统计信息收集，同时也会生成column group
EXEC DBMS_STATS.gather_table_stats('ad','cgtest2',method_opt=>'for columns (c1,c2) size skewonly');    


###生成了column group statistics之后我们再次执行一开始的那句sql：select count(*) from cgtest1 where c1=1 and c2='AA'，看看是否能帮助optimizer算出更精确的cardinality
---先来看看对于代表(c1,c2)的SYS_STUF3GLKIOP5F4B0BTTCFTMX0W列在dba_tab_histogram里的数据分布情况    
SQL> col column_name format a30    
SQL> col endpoint_actual_value format a50    
SQL> set linesize 170    
SQL> set pagesize 100    
SQL> select owner,table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='CGTEST1' and column_name='SYS_STUF3GLKIOP5F4B0BTTCFTMX0W';    
OWNER                          TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE    
------------------------------ ------------------------------ ------------------------------ --------------- --------------    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W               1      716089956    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W            5001     2693090364    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W            5002     3718690277    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W           10002     3926166024    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W           10003     5232674306    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W           15003     5561960012    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W           20003     5832235708    
AD                             CGTEST1                        SYS_STUF3GLKIOP5F4B0BTTCFTMX0W           20004     6322890850    


---预测一下有了基于(c1、c2)的column groups后，select count(*) from cgtest1 where c1=1 and c2='AA'的cardinality返回值会变成多少
cardinality=num_rows*5000/20004=20004*5000/20004=5000    


---实际执行结果与我们的计算结果一致
SQL> select count(*) from cgtest1 where c1=1 and c2='AA';    


  COUNT(*)    
----------    
      5000    
          
explain plan for select count(*) from cgtest1 where c1=1 and c2='AA';    


set linesize 150    
SQL> select * from table(dbms_xplan.display());    


PLAN_TABLE_OUTPUT    
------------------------------------------------------------------------------------------------------------------------------------------------------    
Plan hash value: 4200988577    


------------------------------------------------------------------------------    
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |         |     1 |     6 |    21   (0)| 00:00:01 |    
|   1 |  SORT AGGREGATE    |         |     1 |     6 |            |          |    
|*  2 |   TABLE ACCESS FULL| CGTEST1 |  5000 | 30000 |    21   (0)| 00:00:01 |    
------------------------------------------------------------------------------    


总结：如果表中的数据倾斜度较大，那么收集histogram能最大程度的帮助optimizer计算出准确的cardinality，从而避免产生次优的执行计划；再进一步，如果存在倾斜的多个列共同构成了predicate里的等值连接且这些列间存在较强的列相关性的话，生成带有直方图的multicolumns statistics是一个上佳的选择，能够最大程度的帮助optimizer准确预测出cardinality。

           

---

---

1.环境准备

我们在Oracle 11g中进行试验。

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> select * from v$version;
   
3. 
   
4. BANNER
   
5. --------------------------------------------------------------------------------
   
6. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
   
7. PL/SQL Release 11.2.0.3.0 - Production
   
8. CORE 11.2.0.3.0 Production
   
9. TNS for Linux: Version 11.2.0.3.0 - Production
   
10. NLSRTL Version 11.2.0.3.0 - Production
    
11. 
    
12. SQL>

在hr用户下创建测试表hoegh，重复插入数据，数据量相当于16个employees表（总行数1712＝107*16）。

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> conn hr/hr
   
3. Connected.
   
4. SQL> 
   
5. SQL> create table hoegh as select * from employees;
   
6. 
   
7. Table created.
   
8. 
   
9. SQL> select count(*) from hoegh;
   
10. 
    
11.   COUNT(*)
    
12. ----------
    
13.        107
    
14. 
    
15. SQL> 
    
16. SQL> insert into hoegh select * from hoegh;
    
17. 
    
18. 107 rows created.
    
19. 
    
20. SQL> /
    
21. 
    
22. 214 rows created.
    
23. 
    
24. SQL> /
    
25. 
    
26. 428 rows created.
    
27. 
    
28. SQL> /
    
29. 
    
30. 856 rows created.
    
31. 
    
32. SQL> commit;
    
33. 
    
34. Commit complete.
    
35. 
    
36. SQL> select count(*) from hoegh;
    
37. 
    
38.   COUNT(*)
    
39. ----------
    
40.       1712
    
41. 
    
42. SQL>

2.按照常规方法收集统计量信息；

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\');
   
3. 
   
4. PL/SQL procedure successfully completed.
   
5. 
   
6. SQL>

3.查看执行单个条件的where语句的执行计划

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> explain plan for select * from hoegh where employee_id=110;
   
3. 
   
4. Explained.
   
5. 
   
6. SQL> select * from table(dbms_xplan.display);
   
7. 
   
8. PLAN_TABLE_OUTPUT
   
9. --------------------------------------------------------------------------------
   
10. Plan hash value: 774871165
    
11. 
    
12. ---------------------------------------------------------------------------
    
13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
14. ---------------------------------------------------------------------------
    
15. | 0 | SELECT STATEMENT | | 16 | 1104 | 8 (0)| 00:00:01 |
    
16. |* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1104 | 8 (0)| 00:00:01 |
    
17. ---------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22. PLAN_TABLE_OUTPUT
    
23. --------------------------------------------------------------------------------
    
24. 
    
25.    1 - filter(\"EMPLOYEE_ID\"=110)
    
26. 
    
27. 13 rows selected.
    
28. 
    
29. SQL>

从执行计划可以看出返回了16行记录，结果没有问题。可是，这个16是哪儿来的呢，我们先要了解选择率（selectivity）和返回行数是如何计算的：
选择率（selectivity）＝在本例中是 1/唯一值
返回行数＝选择率（selectivity）＊表记录总数

也就是说，在这个查询语句中，选择率＝1/107，返回行数＝1/107*1712=16

4.查看执行两个条件的where语句的执行计划
点击(此处)折叠或打开

1. SQL> 
   
2. SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';
   
3. 
   
4. Explained.
   
5. 
   
6. SQL> 
   
7. SQL> select * from table(dbms_xplan.display);
   
8. 
   
9. PLAN_TABLE_OUTPUT
   
10. --------------------------------------------------------------------------------
    
11. Plan hash value: 774871165
    
12. 
    
13. ---------------------------------------------------------------------------
    
14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
15. ---------------------------------------------------------------------------
    
16. | 0 | SELECT STATEMENT | | 1 | 69 | 8 (0)| 00:00:01 |
    
17. |* 1 | TABLE ACCESS FULL| HOEGH | 1 | 69 | 8 (0)| 00:00:01 |
    
18. ---------------------------------------------------------------------------
    
19. 
    
20. Predicate Information (identified by operation id):
    
21. ---------------------------------------------------
    
22. 
    
23. PLAN_TABLE_OUTPUT
    
24. --------------------------------------------------------------------------------
    
25. 
    
26.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')
    
27. 
    
28. 13 rows selected.
    
29. 
    
30. SQL>

从执行计划可以看出返回了1行记录，而事实又是什么样的呢？我们执行一下这条sql语句。

点击(此处)折叠或打开

1. SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN\';
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5.         16
   
6. 
   
7. SQL>

由此看出，测试表hoegh符合查询条件的数据有16行，而执行计划提示的只有1行，出错了。这是怎么回事呢，也就是我们在开篇提到的选择率（selectivity）出了问题。
在这个多列条件查询语句中，选择率＝1/107＊1/107，返回行数＝1/107＊1/107*1712=16/107<1；由于表中存在符合条件的记录，并且返回行数不可能小于1，所以Oracle返回了1。

5.收集多列统计信息，再次查看两个条件的where语句的执行计划

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\',method_opt=>\'for columns(employee_id,email)\');
   
3. 
   
4. PL/SQL procedure successfully completed.
   
5. 
   
6. SQL> 
   
7. SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';
   
8. 
   
9. Explained.
   
10. 
    
11. SQL> select * from table(dbms_xplan.display);
    
12. 
    
13. PLAN_TABLE_OUTPUT
    
14. --------------------------------------------------------------------------------
    
15. Plan hash value: 774871165
    
16. 
    
17. ---------------------------------------------------------------------------
    
18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
19. ---------------------------------------------------------------------------
    
20. | 0 | SELECT STATEMENT | | 16 | 1152 | 8 (0)| 00:00:01 |
    
21. |* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1152 | 8 (0)| 00:00:01 |
22. ---------------------------------------------------------------------------
    
23. 
    
24. Predicate Information (identified by operation id):
    
25. ---------------------------------------------------
    
26. 
    
27. PLAN_TABLE_OUTPUT
    
28. --------------------------------------------------------------------------------
    
29. 
    
30.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')
    
31. 
    
32. 13 rows selected.
    
33. 
    
34. SQL>

从执行计划的结果来看，同样的一条sql查询语句，在收集多列统计信息后，Oracle的选择率（selectivity）由错变对，这是由于sql语句中的两个条件是有关联的，即employee_id和email在employees表中都是唯一的，都可以唯一标识一行记录；而在收集多列统计信息之前，Oracle并不知道这两个查询条件有关联，所以在计算选择率（selectivity）时，只是简单地采取了相乘的方法。

---

---

   之前和大家分享过Oracle 11g下的一个新特性——收集多列统计信息(http://blog.itpub.net/30162081/viewspace-1637387/)，今天和大家分享Oracle 12c的一个新特性——自动检测有用列组信息。二者相得益彰，大家可以具体情况酌情使用。
   言归正传，我们可以针对一个表，基于特定的工作负荷，通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定我们需要哪些列组。当你不清除需要创建哪个扩展统计信息时，这个技术是非常有用的。需要注意的是，这种技术不适用于包含表达式列的统计工作。
   接下来，我们通过例子来学习这个的新特性。

1.环境准备

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

点击(此处)折叠或打开

1. SQL> select banner from v$version;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
6. PL/SQL Release 12.1.0.2.0 - Production
   
7. CORE    12.1.0.2.0    Production
   
8. TNS for Linux: Version 12.1.0.2.0 - Production
   
9. NLSRTL Version 12.1.0.2.0 - Production
   
10. 
    
11. SQL> 
    
12. SQL> conn sh/sh@HOEGH
    
13. Connected.
    
14. SQL> 
    
15. SQL> DROP TABLE customers_test;
    
16. DROP TABLE customers_test
    
17.            *
    
18. ERROR at line 1:
    
19. ORA-00942: table or view does not exist
    
20. 
    
21. 
    
22. SQL> CREATE TABLE customers_test AS SELECT * FROM customers;
    
23. 
    
24. Table created.
    
25. 
    
26. SQL> select count(*) from customers_test;
    
27. 
    
28.   COUNT(*)
    
29. ----------
    
30.      55500
    
31. 
    
32. SQL>

2.收集统计信息

点击(此处)折叠或打开

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

3.开启负载监控

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

点击(此处)折叠或打开

1. SQL> show user
   
2. USER is “SYS”
   
3. SQL> BEGIN
   
4.   DBMS_STATS.SEED_COL_USAGE(null,null,300);
   
5. END;
   
6. / 2 3 4
   
7. 
   
8. PL/SQL procedure successfully completed.
   
9. SQL>

4.使用explain plan for查询执行计划

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> EXPLAIN PLAN FOR
   
3.   SELECT *
   
4.   FROM customers_test
   
5.   WHERE cust_city = 'Los Angeles'
   
6.   AND cust_state_province = 'CA'
   
7.   AND country_id = 52790; 2 3 4 5 6 
   
8. 
   
9. Explained.
   
10. 
    
11. SQL> 
    
12. SQL> SELECT PLAN_TABLE_OUTPUT 
    
13. FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 
    
14. 
    
15. PLAN_TABLE_OUTPUT
    
16. --------------------------------------------------------------------------------
    
17. Plan hash value: 2112738156
    
18. 
    
19. ----------------------------------------------------
    
20. | Id | Operation     | Name     | Rows |
    
21. ----����,����------------------------------------------------
    
22. | 0 | SELECT STATEMENT |         |     1 |
    
23. | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
    
24. ----------------------------------------------------
    
25. 
    
26. 8 rows selected.
    
27. 
    
28. SQL>

从执行计划来看，查询结果只有1列。我们暂且记下这个结果。

5.查看列使用信息

此时，我们可以通过REPORT_COL_USAGE来查看列的使用信息。
我们看到，Oracle帮我们检测到了一个有用的列组信息，包括customers_test、cust_city和cust_state_province三列。

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> SET LONG 100000
   
3. SQL> SET LINES 120
   
4. SQL> SET PAGES 0
   
5. SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
   
6.   2 FROM DUAL;
   
7. LEGEND:
   
8. .......
   
9. 
   
10. EQ     : Used in single table EQuality predicate
    
11. RANGE     : Used in single table RANGE predicate
    
12. LIKE     : Used in single table LIKE predicate
    
13. NULL     : Used in single table is (not) NULL predicate
    
14. EQ_JOIN : Used in EQuality JOIN predicate
    
15. NONEQ_JOIN : Used in NON EQuality JOIN predicate
    
16. FILTER     : Used in single table FILTER predicate
    
17. JOIN     : Used in JOIN predicate
    
18. GROUP_BY : Used in GROUP BY expression
    
19. ...............................................................................
    
20. 
    
21. ###############################################################################
    
22. 
    
23. COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
    
24. .........................................
    
25. 
    
26. 1. COUNTRY_ID             : EQ
    
27. 2. CUST_CITY             : EQ
    
28. 3. CUST_STATE_PROVINCE         : EQ
    
29. 4. (CUST_CITY, CUST_STATE_PROVINCE,
    
30.     COUNTRY_ID)          : FILTER
    
31. ###############################################################################
    
32. 
    
33. 
    
34. 
    
35. SQL>

6.创建扩展统计信息

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

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
   
3. ###############################################################################
   
4. 
   
5. EXTENSIONS FOR SH.CUSTOMERS_TEST
   
6. ................................
   
7. 
   
8. 1. (CUST_CITY, CUST_STATE_PROVINCE,
   
9.     COUNTRY_ID)          : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
   
10. ###############################################################################
    
11. 
    
12. 
    
13. 
    
14. SQL>

7.重新收集统计信息

点击(此处)折叠或打开

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

8.查看USER_TAB_COL_STATISTICS，确认列统计信息

通过查询USER_TAB_COL_STATISTICS，我们可以获取到刚刚创建的列组对象，和第6步的输出结果是一致的。

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> COL COLUMN_NAME FOR A30
   
3. SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   
4. FROM USER_TAB_COL_STATISTICS
   
5. WHERE TABLE_NAME = 'CUSTOMERS_TEST'
   
6. ORDER BY 1; 2 3 4 
   
7. COUNTRY_ID                 19 FREQUENCY
   
8. CUST_CITY                620 HYBRID
   
9. CUST_CITY_ID                620 NONE
   
10. CUST_CREDIT_LIMIT             8 NONE
    
11. CUST_EFF_FROM                 1 NONE
    
12. CUST_EFF_TO                 0 NONE
    
13. CUST_EMAIL             1699 NONE
    
14. CUST_FIRST_NAME          1300 NONE
    
15. CUST_GENDER                 2 NONE
    
16. CUST_ID              55500 NONE
    
17. CUST_INCOME_LEVEL             12 NONE
    
18. CUST_LAST_NAME                908 NONE
    
19. CUST_MAIN_PHONE_NUMBER         51344 NONE
    
20. CUST_MARITAL_STATUS             11 NONE
    
21. CUST_POSTAL_CODE            623 NONE
    
22. CUST_SRC_ID                 0 NONE
    
23. CUST_STATE_PROVINCE            145 FREQUENCY
    
24. CUST_STATE_PROVINCE_ID            145 NONE
    
25. CUST_STREET_ADDRESS         49900 NONE
    
26. CUST_TOTAL                 1 NONE
    
27. CUST_TOTAL_ID                 1 NONE
    
28. CUST_VALID                 2 NONE
    
29. CUST_YEAR_OF_BIRTH             75 NONE
    
30. SYS_STUMZ$C3AIHLPBROI#SKA58H_N        620 HYBRID
    
31. 
    
32. 24 rows selected.
    
33. 
    
34. SQL>

9.重新查询执行计划

我们看到，在第4步中查询执行计划中，Rows为1；现在呢，是867。这差距也忒大了点儿。

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> EXPLAIN PLAN FOR
   
3.   SELECT *
   
4.   FROM customers_test
   
5.   WHERE cust_city = 'Los Angeles'
   
6.   AND cust_state_province = 'CA'
   
7.   AND country_id = 52790; 2 3 4 5 6 
   
8. 
   
9. Explained.
   
10. 
    
11. SQL> 
    
12. SQL> SELECT PLAN_TABLE_OUTPUT 
    
13. FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 
    
14. Plan hash value: 2112738156
    
15. 
    
16. ----------------------------------------------------
    
17. | Id | Operation     | Name     | Rows |
    
18. ----------------------------------------------------
    
19. | 0 | SELECT STATEMENT |         | 867 |
    
20. | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |
    
21. ----------------------------------------------------
    
22. 
    
23. 8 rows selected.
    
24. 
    
25. SQL>