Oracle多列統計資訊

lhrbest發表於2017-05-18
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;

  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群，學習最實用的資料庫技術。

Oracle多列統計資訊
 DBA筆試面試講解
 歡迎與我聯絡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2139297/，如需轉載，請註明出處，否則將追究法律責任。

相關文章