> 最近在閱讀崔華老師《基於Oracle的SQL最佳化》，測試其中動態取樣以及多列統計資訊用例時，在自己測試環境結果與書中有出入，進行了一些研究，發現直方圖是對測試影響的根因，特將研究過程以及基於Oracle的多列關聯查詢相關知識整理一下，分享出來。

1. 測試版本為11.2.0.4.190115 PSU版本
2. 測試用例以及一些知識來自崔華老師《基於Oracle的SQL最佳化》
3. 下文主要涉及Oracle多列統計資訊、基數反饋。

# 1.建立測試表收集統計資訊

```sql
SQL> create table t2 (c1 varchar2(1),c2 char(2000),n1 number, n2 number);
SQL> insert into t2 select 'a','a',trunc(dbms_random.value(0,20)),trunc(dbms_random. value(0,25)) from dba_objects where rownum<10001;
SQL> commit;

SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T2',CASCADE=>true, estimate_percent=>100);
```

# 2 執行SQL，檢視cardinality

## 2.1 T2表統計資訊

```sql
***********
Table Level
***********

Table                       Number                 Empty Average    Chain Average Global User               Sample Date
Name                       of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats                Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2                          10,000    3,394            0       0        0    #### YES    NO                 10,000 04-16-2021
Elapsed: 00:00:00.03

Column                    Column                       Distinct          Number     Number Global User               Sample Date
Name                      Details                        Values Density Buckets      Nulls Stats  Stats                Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
C1                        VARCHAR2(1)                         1       1       1          0 YES    NO                 10,000 04-16-2021
C2                        CHAR(2000)                          1       1       1          0 YES    NO                 10,000 04-16-2021
N1                        NUMBER(22)                         20       0       1          0 YES    NO                 10,000 04-16-2021
N2                        NUMBER(22)                         25       0       1          0 YES    NO                 10,000 04-16-2021

```

## 2.2 執行SQL，檢視cardinality

```sql
SQL> select * from t2 where n1=3 and n2=3 and c1='a';

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    20 | 40180 |   922   (1)| 00:00:12 |
|*  1 |  TABLE ACCESS FULL| T2   |    20 | 40180 |   922   (1)| 00:00:12 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("N2"=3 AND "N1"=3 AND "C1"='a')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3360  consistent gets
          0  physical reads
          0  redo size
       5069  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

--可以看到cardinality為20，計算公式為cardinality =  num_rows*selectivity= t2_num_rows(n1_selectivity*n2_selectivity)=10000*(1/20*1/25)=20，這在n1 n2列沒有任何聯絡，即不存在任何可以大量篩選資料的關聯條件。
```

## 2.3 關聯列創造聯絡，再次測試SQL，計算cardinality

```sql
SQL> update t2 set n2=n1;
SQL> commit;
--經過上述update，n1與n2產生了聯絡，即n1列與n2列使用and關聯查詢時，兩列組合篩選條件已經不能獨立使用兩列各自selectivity相乘的方法了，會造成cardinality會比實際返回結果小很多。

--再次收集統計資訊，確保統計資訊準確性。
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T2',CASCADE=>true, estimate_percent=>100,method_opt=>'for all columns size 1');
--清空shared pool，防止之前執行計劃干擾測試
SQL> alter system flush shared_pool;
SQL> alter session set statistics_level=all;
SQL> select * from t2 where n1=3 and n2=3 and c1='a';
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  92nytgpv0mb76, child number 0
-------------------------------------
select * from t2 where n1=3 and n2=3 and c1='a'

Plan hash value: 1513984157

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    500 |00:00:00.01 |    3383 |
|*  1 |  TABLE ACCESS FULL| T2   |      1 |     25 |    500 |00:00:00.01 |    3383 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(("N1"=3 AND "N2"=3 AND "C1"='a'))
--可以看到cardinality為25，即cardinality = t2_num_rows (n1_selectivity*n2_selectivity)=10000(1/20*1/20)=25
--實際返回501行，顯然CBO預估已經嚴重不準了，因為CBO並不知道兩列其實是有聯絡的。
```

# 3. 解決辦法

## 3.1 建立組合索引

```sql
SQL> create index idx_t2 on t2(n1,n2);
--不收集直方圖
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T2',CASCADE=>true, estimate_percent=>100,method_opt=>'for all columns size 1');
SQL> alter system flush shared_pool;
SQL> alter session set statistics_level=all;
SQL> select * from t2 where n1=3 and n2=3 and c1='a';
SQL > select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  92nytgpv0mb76, child number 0
-------------------------------------
select * from t2 where n1=3 and n2=3 and c1='a'

Plan hash value: 2008370210

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |    500 |00:00:00.01 |     514 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2     |      1 |    500 |    500 |00:00:00.01 |     514 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |      1 |    500 |    500 |00:00:00.01 |      37 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C1"='a')
   2 - access("N1"=3 AND "N2"=3)
   
--可以看到cardinality已經正確，估算是按照idx_t2的distinct值參考的selectivity，即cardinality = t2_num_rows * (1/20) = 500
--估算已經非常準確了。
```

## 3.2 收集T2表直方圖，再次測試

```sql
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T2',CASCADE=>true, estimate_percent=>100,method_opt=>'for all columns size auto');
SQL> select table_name,column_name,HISTOGRAM from user_tab_columns where table_name='T2';

Table                                    Column
Name                                     Name                      HISTOGRAM
---------------------------------------- ------------------------- ---------------------------------------------
T2                                       C1                        FREQUENCY
T2                                       C2                        NONE
T2                                       N1                        FREQUENCY
T2                                       N2                        FREQUENCY

SQL> alter system flush shared_pool;
SQL> select * from t2 where n1=3 and n2=3 and c1='a';
SQL> select * from t2 where n1=3 and n2=3 and c1='a';
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  92nytgpv0mb76, child number 0
-------------------------------------
select * from t2 where n1=3 and n2=3 and c1='a'
Plan hash value: 2008370210

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |    500 |00:00:00.01 |     514 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2     |      1 |     25 |    500 |00:00:00.01 |     514 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |      1 |     25 |    500 |00:00:00.01 |      37 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C1"='a')
   2 - access("N1"=3 AND "N2"=3)
   
--可以看到列上有了直方圖之後，cbo無法參考複合索引統計資訊了，所以cardinality已經不準確了，所以要注意直方圖對這種情況的影響。
```

## 3.3 建立多列統計資訊

```sql
--建立虛擬列組合
set serveroutput on
declare
 cg_name varchar2(30);
begin
    cg_name := sys.dbms_stats.create_extended_stats('HR','T2','(n1,n2)');
    dbms_output.put_line(cg_name);
end;
/

--檢視虛擬列組合
SQL> select extension_name, extension from dba_stat_extensions where table_name=upper('&tbl_name');
EXTENSION_NAME                 EXTENSION
------------------------------ -------------
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS ("N1","N2")
SQL> select sys.dbms_stats.show_extended_stats_name('HR','T2','(n1,n2)') col_group_name
from dual;
COL_GROUP_NAME
--------------------------------
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS

--檢視虛擬列統計資訊
SQL> select e.extension col_group, t.num_distinct, t.histogram
  from user_stat_extensions e, user_tab_col_statistics t
 where e.extension_name = t.column_name
   and e.table_name = t.table_name
   and t.table_name = 'T2';  
                         Distinct
COL_GROUP                  Values HISTOGRAM
-------------------- ------------ ---------------------------------------------
("N1","N2")                    20 FREQUENCY

--重新收集統計資訊(只單獨針對n1,n2組合列)，重新執行SQL，檢視cardinality
SQL> alter system flush shared_pool;
SQL> select * from t2 where n1=3 and n2=3 and c1='a';
SQL> exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'T2',method_opt => 'for columns(n1,n2) size auto',estimate_percent => 100,no_invalidate=>false);
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  92nytgpv0mb76, child number 0
-------------------------------------
select * from t2 where n1=3 and n2=3 and c1='a'
Plan hash value: 2008370210
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |    500 |00:00:00.01 |     514 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2     |      1 |    500 |    500 |00:00:00.01 |     514 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |      1 |    500 |    500 |00:00:00.01 |      37 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C1"='a')
   2 - access("N1"=3 AND "N2"=3)

--刪除虛擬列
SQL> exec dbms_stats.drop_extended_stats('hr','t2','(n1,n2)');
```

## 3.4 測試結論

```sql
--經過測試，細分為以下幾種情形：
(1)當列n1,n2組合未被使用記錄如$col_usage時，不會收集n1,n2組合列直方圖資訊，此時如果n1,n2列各自有直方圖資訊時，第一次執行會參考n1,n2各自列selectivity，導致第一次執行上面SQL時，cardinality=10000*(1/20*1/20)=25，第二次執行時，cbo會自動使用cardinality feedback修正cardinality。
(2)當列n1,n2組合使用過一次$col_usage有虛擬組合列使用記錄時，再收集統計資訊，n1,n2組合虛擬列會收集直方圖，此時cardinality會參考n1,n2組合列直方圖以及統計資訊，cbo預估cardinality基本準確。
(3)當表缺失統計資訊時，會採用動態取樣，cardinality預估也會較為準確。
(4)當表有統計資訊，沒有虛擬列時，也會有cardinality feedback特性修正cardinality，當SQL反覆執行時，cardinality也不會太偏離準確結果。
```

Oracle多列統計資訊與直方圖對有關聯多列查詢影響

相關文章