备案控制台
探索云世界
开发者社区 数据库 文章 正文

Oracle多列统计信息

2017-05-18 1237
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Oracle多列统计信息    通常，当我们将SQL语句提交给Oracle数据库时，Oracle会选择一种最优方式来执行，这是通过查询优化器Query Optimizer来实现的。

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;

  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

  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;

  7. Table created.

  9. SQL> select count(*) from hoegh;

  11.   COUNT(*)
  12. ----------
  13.        107

  15. SQL> 
  16. SQL> insert into hoegh select * from hoegh;

  18. 107 rows created.

  20. SQL> /

  22. 214 rows created.

  24. SQL> /

  26. 428 rows created.

  28. SQL> /

  30. 856 rows created.

  32. SQL> commit;

  34. Commit complete.

  36. SQL> select count(*) from hoegh;

  38.   COUNT(*)
  39. ----------
  40.       1712

  42. SQL>

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


点击(此处)折叠或打开

  1. SQL> 
  2. SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\');

  4. PL/SQL procedure successfully completed.

  6. SQL>

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


点击(此处)折叠或打开

  1. SQL> 
  2. SQL> explain plan for select * from hoegh where employee_id=110;

  4. Explained.

  6. SQL> select * from table(dbms_xplan.display);

  8. PLAN_TABLE_OUTPUT
  9. --------------------------------------------------------------------------------
  10. Plan hash value: 774871165

  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. ---------------------------------------------------------------------------

  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------

  22. PLAN_TABLE_OUTPUT
  23. --------------------------------------------------------------------------------

  25.    1 - filter(\"EMPLOYEE_ID\"=110)

  27. 13 rows selected.

  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\';

  4. Explained.

  6. SQL> 
  7. SQL> select * from table(dbms_xplan.display);

  9. PLAN_TABLE_OUTPUT
  10. --------------------------------------------------------------------------------
  11. Plan hash value: 774871165

  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. ---------------------------------------------------------------------------

  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  23. PLAN_TABLE_OUTPUT
  24. --------------------------------------------------------------------------------

  26.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')

  28. 13 rows selected.

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

点击(此处)折叠或打开

  1. SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN\';

  3.   COUNT(*)
  4. ----------
  5.         16

  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)\');

  4. PL/SQL procedure successfully completed.

  6. SQL> 
  7. SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';

  9. Explained.

  11. SQL> select * from table(dbms_xplan.display);

  13. PLAN_TABLE_OUTPUT
  14. --------------------------------------------------------------------------------
  15. Plan hash value: 774871165

  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. ---------------------------------------------------------------------------

  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------

  27. PLAN_TABLE_OUTPUT
  28. --------------------------------------------------------------------------------

  30.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')

  32. 13 rows selected.

  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;

  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

  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


  22. SQL> CREATE TABLE customers_test AS SELECT * FROM customers;

  24. Table created.

  26. SQL> select count(*) from customers_test;

  28.   COUNT(*)
  29. ----------
  30.      55500

  32. SQL>

2.收集统计信息

点击(此处)折叠或打开

  1. SQL> 
  2. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

  4. PL/SQL procedure successfully completed.

  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

  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 

  9. Explained.

  11. SQL> 
  12. SQL> SELECT PLAN_TABLE_OUTPUT 
  13. FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 

  15. PLAN_TABLE_OUTPUT
  16. --------------------------------------------------------------------------------
  17. Plan hash value: 2112738156

  19. ----------------------------------------------------
  20. | Id | Operation     | Name     | Rows |
  21. ----------------------------------------------------
  22. | 0 | SELECT STATEMENT |         |     1 |
  23. | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
  24. ----------------------------------------------------

  26. 8 rows selected.

  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. .......

  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. ...............................................................................

  21. ###############################################################################

  23. COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
  24. .........................................

  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. ###############################################################################



  35. SQL>

6.创建扩展统计信息

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

点击(此处)折叠或打开

  1. SQL> 
  2. SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
  3. ###############################################################################

  5. EXTENSIONS FOR SH.CUSTOMERS_TEST
  6. ................................

  8. 1. (CUST_CITY, CUST_STATE_PROVINCE,
  9.     COUNTRY_ID)          : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
  10. ###############################################################################



  14. SQL>

7.重新收集统计信息

点击(此处)折叠或打开

  1. SQL> 
  2. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

  4. PL/SQL procedure successfully completed.

  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

  32. 24 rows selected.

  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 

  9. Explained.

  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

  16. ----------------------------------------------------
  17. | Id | Operation     | Name     | Rows |
  18. ----------------------------------------------------
  19. | 0 | SELECT STATEMENT |         | 867 |
  20. | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |
  21. ----------------------------------------------------

  23. 8 rows selected.

  25. SQL>






About Me

...............................................................................................................................

● 本文整理自网络，http://blog.itpub.net/30162081/viewspace-1637387/

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

img_e3029f287d989cd04bd75432ecc1c172.png
 DBA笔试面试讲解
 欢迎与我联系

文章标签：
SQL
关系型数据库
Oracle
C语言
数据库
关键词：
Oracle数据库一键上云信息
Oracle数据库一键上云统计
Oracle数据库一键上云多列
Oracle数据库一键上云统计信息
小麦苗
目录
相关文章
穆雄雄.
|
1月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
穆雄雄.
13 1
程序媛三妹ya
|
SQL Oracle 关系型数据库
Oracle 统计某个字段下不同值的个数
Oracle 统计某个字段下不同值的个数
程序媛三妹ya
498 0
穆雄雄.
|
1月前
|
JavaScript Java 数据库连接
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
穆雄雄.
23 0
gamonxfdgmihc
|
Oracle 关系型数据库 大数据
oracle一次性说清楚，多种分隔符的一个字段拆分多行，再多行多列多种分隔符拆多行，最终处理超亿亿。。亿级别数据量（上）
笔记
gamonxfdgmihc
358 0
oracle一次性说清楚，多种分隔符的一个字段拆分多行，再多行多列多种分隔符拆多行，最终处理超亿亿。。亿级别数据量（上）
snowofsummer
|
安全 Oracle Java
oracle 11g 11204补丁信息
11204补丁信息
snowofsummer
767 0
姚远OracleACE
|
10月前
|
SQL 存储 Oracle
是否应该收集Oracle的系统统计信息？（译文）
您应该收集Oracle数据库的系统统计信息吗？如果您部署过Oracle数据库，那么您会对这个问题有自己的观点，但Oracle的建议什么呢？
姚远OracleACE
96 0
穆雄雄.
|
11月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
穆雄雄.
38 0
穆雄雄.
|
11月前
|
JavaScript Java 数据库连接
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
穆雄雄.
62 0
gamonxfdgmihc
|
机器学习/深度学习 存储 缓存
oracle一次性说清楚，多种分隔符的一个字段拆分多行，再多行多列多种分隔符拆多行，最终处理超亿亿。。亿级别数据量（下）
笔记
gamonxfdgmihc
541 0
oracle一次性说清楚，多种分隔符的一个字段拆分多行，再多行多列多种分隔符拆多行，最终处理超亿亿。。亿级别数据量（下）
小麦苗
|
Oracle 关系型数据库 OLAP
Oracle 11.2.0.4数据泵expdp导出报DBMS_AW_EXP等信息
Oracle 11.2.0.4数据泵expdp导出报DBMS_AW_EXP等信息
小麦苗
256 0

热门文章

最新文章

  • 1
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 2
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 3
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 4
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
  • 5
    oracle 访问控制
  • 6
    Oracle的三重奏：密码文件、警告文件与跟踪文件
  • 7
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
  • 8
    Oracle的PL/SQL表达式：数据的魔法公式
  • 9
    Oracle日志文件：数据王国的“记事本”
  • 10
    Oracle数据区：数据王国的黄金地段
  • 1
    Oracle数据守卫（DG）：数据的“守护者”与“时光机”
    10
  • 2
    Oracle 12c的内存列存储：数据的“闪电侠”
    14
  • 3
    Oracle 12c的多重索引：数据的“多维导航仪”
    8
  • 4
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
    13
  • 5
    Oracle 12c多租户架构：数据管理的“摩天大楼”
    12
  • 6
    Oracle数据泵：数据迁移的魔法棒
    5
  • 7
    Oracle中的触发器与序列：自增列的魔法组合
    10
  • 8
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
    27
  • 9
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
    10
  • 10
    Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
    12

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 使用SQL语句管理索引
  • SQL进阶之约束、索引

    • 推荐镜像

    更多
  • oracle
  • mysql
  • arch4edu
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）