聊聊Cardinality Feedback——一種CBO優化器嘗試

 

從Oracle優化器發展歷程上看，CBO已經取代RBO，成為今後優化器發展的主流。在CBO時代，執行計劃SEP生成更加靈活、更加符合實際資料情況，執行效率更高。

 

在筆者之前的系列文章中，反覆強調過CBO工作三個重要輸入：資料統計量、優化器成本公式和內部調節引數。三者應該說是會直接影響到CBO生成執行計劃的準確性。

 

在三個重要輸入中，資料統計量是可變性最大的一方面因素。統計量缺失、過期和失真，是我們進入10g之後遇到執行計劃問題中最常見的部分。為了應對這些問題，Oracle也在不斷進行一些嘗試手段，本篇就介紹其中一個Cardinality Feedback。

 

1、失真的成本計算值

 

我們從一個簡單問題談起：究竟什麼樣的情況會引起執行計劃的錯誤？從筆者角度來看兩方面的原因都可能造成問題：統計量缺失失真和SQL語句本身特性。

 

ü  統計量失真：例如新資料表從來沒有進行過統計量收集，或者距離上次收集之後，資料取值和結構發生了很大的變化。失真的統計量計算出錯誤的cost值，引起SEP錯誤；

ü  SQL語句本身特性：絕大多數的統計量資訊都是基於獨立列統計的。而我們的SQL語句，很多時候where條件是相關的，或者連線引起的相關性。在這樣的情況下，CBO估算結果集合的時候，是不可能得到準確的結果的；

 

基於這些特殊的情況，Oracle實際上是進行了很多的優化手段。動態取樣（dynamic sampling）可以解決統計量缺失和相關列取值問題，而Oracle 11g推出的組合統計量（Multi-Column Statistics）也在試圖解決相關列問題。

 

其實，在11g中，Oracle同時還開啟了一個新的功能Cardinality Feedback，提供自適應方式的執行計劃調節。

 

Cardinality是執行計劃的一個重要要素，最直接的表示就是某個特定操作獲取到的資料集合行數。Cardinality在SEP成本計算過程中十分重要，直接決定了IO資料量。

 

所謂的Cardinality Feedback，本質上就是一種基於自適應模型的執行計劃調整機制。當對一個SQL語句，第一次生成執行計劃之後，其計算的成本cost是依據估算出的cardinality得出的。當執行這個計劃後，Oracle就會得到真正這個SQL的執行計劃結果，並且用真實的結果集合來更新執行計劃中的Cardinality。在第二次生成時候，就可以使用更加真實的結果來確定了。

 

在Oracle 11g中，Cardinality Feedback功能預設開啟，控制引數是一個隱含引數“_optimizer_use_feedback”。下面，我們根據一系列的實驗，來進行測試該功能。

 

2、環境準備

 

我們選擇Oracle 11gR2進行測試，構建實驗資料表T。

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

 

--構建資料表T

SQL> create table t as select * from dba_objects where 1=0;

Table created.

 

SQL> create index idx_t_owner on t(owner);

Index created.

 

SQL> insert into t select * from dba_objects;

72461 rows created.

 

SQL> commit;

Commit complete.

 

 

此時，我們檢查隱含引數“_optimizer_use_feedback”，預設值為true。表示啟用cardinality feedback。

 

 

SQL> select

  2   　　x.ksppinm name,

  3   　　y.ksppstvl value,

  4   　　y.ksppstdf isdefault,

  5   　　decode(bitand(y.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismod,

  6   　　decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj

  7   　　from

  8   　　sys.x$ksppi x,

  9   　　sys.x$ksppcv y

 10   　　where

 11   　　x.inst_id = userenv('Instance') and

 12   　　y.inst_id = userenv('Instance') and

 13   　　x.indx = y.indx and

 14       x.ksppinm like '%use_feedback%'

 15   　　order by

 16   　　translate(x.ksppinm, ' _', ' ');

 

NAME                           VALUE      ISDEFAULT ISMOD      ISADJ

------------------------------ ---------- --------- ---------- -----

_optimizer_use_feedback        TRUE       TRUE      FALSE      FALSE

 

 

3、無統計量時執行計劃

 

Cardinality Feedback起作用的兩個時點，首先是沒有統計量，其次是SQL估算Row數值困難。我們先看無統計量的情況。

 

 

--無統計量

SQL> select SAMPLE_SIZE, LAST_ANALYZED from dba_tables where wner='SYS' and table_name='T';

 

SAMPLE_SIZE LAST_ANALYZED

----------- -------------

 

--Autotrace跟蹤

SQL> select /*+ DEMO */ * from t where wner='SCOTT';

6 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1516787156

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |     6 |  1242 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     6 |  1242 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

SQL> select version_count from v$sqlarea where sql_text like 'select /*+ DEMO */%';

 

VERSION_COUNT

-------------

            1

 

 

注意，此時雖然沒有統計量，但是Dynamic Sampling功能被啟用。Cardinality Feedback是不會出現的！

 

4、關閉Dynamic Sampling

 

那麼，如果我們統計量，關閉Dynamic Sampling，是否就可以啟用Cardinality Feedback了呢？

 

首先，我們清理一下shared Pool，刪除統計量。

 

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_columns => true,cascade_indexes => true);

PL/SQL procedure successfully completed

 

 

關閉動態統計功能。

 

 

SQL> alter session set optimizer_dynamic_sampling=0;

Session altered.

 

 

執行新的SQL語句，檢視執行計劃情況。

 

 

--第一次執行

SQL> select /*+ DEMO-2 */ * from t where wner='SCOTT';

6 rows selected.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1516787156

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |   840 |   169K|     5   (0)|

 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

 

注意：此時沒有Dynamic Sampling資訊！！

 

--第二次執行

SQL> select /*+ DEMO-2 */ * from t where wner='SCOTT';

6 rows selected.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1516787156

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |   840 |   169K|     5   (0)|

 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

 

 

從autotrace中，我們沒有看到任何不同。但是，我們從shared pool中，卻看到了不同的情況。

 

 

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select /*+ DEMO-2 */%';

SQL_ID        VERSION_COUNT

------------- -------------

dttcb0t4drju2             2

 

 

出現了兩個子游標。在相同的父遊標下，存在了兩個子游標。說明生成了兩個執行計劃。我們直接從shared pool中抽取出來，如下：

 

--0號子游標；

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'dttcb0t4drju2',cursor_child_no => 0,format => 'advanced'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  dttcb0t4drju2, child number 0

-------------------------------------

select /*+ DEMO-2 */ * from t where wner='SCOTT'

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |       |       |     5 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

--------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      OPT_PARAM('optimizer_dynamic_sampling' 0)

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

Column Projection Information (identified by operation id):

（篇幅原因，有省略……）

 

52 rows selected

 

--1號子游標

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'dttcb0t4drju2',cursor_child_no => 1,format => 'advanced'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  dttcb0t4drju2, child number 1

-------------------------------------

select /*+ DEMO-2 */ * from t where wner='SCOTT'

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     6 |  1242 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |     6 |       |     1   (0)|

--------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      OPT_PARAM('optimizer_dynamic_sampling' 0)

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

Column Projection Information (identified by operation id):

 

Note

-----

   - cardinality feedback used for this statement

 

56 rows selected

 

 

上面的資訊已經比較清楚了。雖然兩次執行的都是索引路徑，但是執行計劃中的Rows（Cardinality）進行了調整，進而cost也發生了變化。

 

在兩個執行計劃的Outline中，都明確的寫清楚Dynamic Sampling沒有使用。而且，在第二個執行計劃中，存在有“cardinality feedback used for this statement”的字樣。說明：第二個執行計劃是使用了Cardinality Feedback產生的。

 

5、關閉Cardinality Feedback

 

更加極端，我們關閉了Dynamic Sampling和Cardinality Feedback，看看執行計劃情況。

 

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

SQL> alter session set "_optimizer_use_feedback"=false;

Session altered.

 

 

 

執行SQL語句，執行兩遍。

 

SQL> select /*+ DEMO-3 */ * from t where wner='SCOTT';

6 rows selected.

 

 

檢視shared pool中的情況。

 

 

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select /*+ DEMO-3 */%';

 

SQL_ID        VERSION_COUNT

------------- -------------

10cgfzba17t9g             1

 

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '10cgfzba17t9g',cursor_child_no => 0,format => 'advanced'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  10cgfzba17t9g, child number 0

-------------------------------------

select /*+ DEMO-3 */ * from t where wner='SCOTT'

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |       |       |     5 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      OPT_PARAM('optimizer_dynamic_sampling' 0)

      OPT_PARAM('_optimizer_use_feedback' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

 

（篇幅原因，省略部分……）

53 rows selected

 

 

6、結論

 

應該說，在有Dynamic Sampling和Multi-Column Statistic的情況下，Cardinality Feedback這種自適應反饋方法是沒有過多的用武之地，特別是筆者實驗的無統計量情況。

 

在另一個方面，Cardinality Feedback也許會更加有效，就是複雜SQL條件下的Cardinality估算。如果一個SQL十分複雜，不斷的進行Rows Source調整也是才是該方法的真正價值所在。