使用multicolumns statistics幫助Optimizer計算出更準確的cardinality

dbhelper發表於2015-01-23
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。

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

相關文章