Oracle動態(tài)采樣學(xué)習(xí)

     動態(tài)采樣(Dynamic Sampling)是在ORACLE 9i Release 2中開始引入的一個技術(shù)，引入它的目的是為了應(yīng)對數(shù)據(jù)庫對象沒有分析（統(tǒng)計信息缺失）的情況下，優(yōu)化器生成更好的執(zhí)行計劃。簡單的說，在數(shù)據(jù)庫段（表、索引、分區(qū)）對象沒有分析的情況下，為了使CBO優(yōu)化器得到足夠多的信息以保證優(yōu)化器做出正確執(zhí)行計劃而發(fā)明的一種技術(shù)。它會分析一定數(shù)量段對象上的數(shù)據(jù)塊獲取CBO需要的統(tǒng)計信息。動態(tài)采樣技術(shù)僅僅是統(tǒng)計信息的一種補(bǔ)充，它不能完全替代統(tǒng)計信息分析。

注意：動態(tài)采樣在Oracle 11g之前稱為 Dynamic Sampling, ORACLE 12c之后改名為Dynamic Statistic.

Oracle11G R2 默認(rèn)的采樣級別：

SQL> show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE

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

optimizer_dynamic_sampling           integer     2

SQL> show parameter Dynamic Statistic

NAME                                 TYPE        VALUE

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

optimizer_dynamic_sampling           integer     2

動態(tài)采樣的級別有11個級別：請自行查看官方文檔

http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101

動態(tài)采樣實(shí)驗(yàn)：

1、創(chuàng)建測試表test

SQL> create table test as select * from dba_objects;          

Table created.

SQL> select count(1) from test;

  COUNT(1)

----------

     86259

2、不使用動態(tài)采樣，查看執(zhí)行計劃

SQL> set autotrace traceonly explain;

SQL> select /*+ dynamic_sampling(test 0) */ * from test;   

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |   100K|    19M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |   100K|    19M|   336   (1)| 00:00:05 |

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

Note

-----

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

從上面可以看出，次數(shù)優(yōu)化器估計表test的行數(shù)顯示為100K，我們再看下面使用動態(tài)采樣的執(zhí)行計劃，優(yōu)化器會估算多少行：

3、使用動態(tài)采樣，查看執(zhí)行計劃（下面是直接查詢的，因?yàn)樵?1G 是默認(rèn)啟用動態(tài)采樣的）

SQL> select * from test;

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      | 72258 |    14M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST | 72258 |    14M|   336   (1)| 00:00:05 |

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

Note

-----

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

如果啟用動態(tài)采樣(默認(rèn)情況下，動態(tài)采樣級別為2)，優(yōu)化器根據(jù)動態(tài)采樣得到一些數(shù)據(jù)信息猜測、估計表TEST的記錄行數(shù)為86259，已經(jīng)接近實(shí)際記錄行數(shù)72258了。比不做動態(tài)采樣分析要好很多了。

如果我們將動態(tài)采樣的級別提高為3，如下所示，發(fā)現(xiàn)優(yōu)化器根據(jù)動態(tài)采樣得到的信息比默認(rèn)（默認(rèn)情況下，動態(tài)采樣級別為2）情況獲得的信息更準(zhǔn)確。優(yōu)化器估計表TEST的行數(shù)為92364，比72258又接近實(shí)際情況一步了。

SQL> select /*+ dynamic_sampling(test 3) */ * from test;

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      | 92364 |    18M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST | 92364 |    18M|   336   (1)| 00:00:05 |

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

Note

-----

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

4、在Tom大師的這篇文章中提到，在沒有動態(tài)采樣的情況下，如果刪除了該表數(shù)據(jù)，CBO優(yōu)化器估算的結(jié)果集和沒有刪除之前是一樣的。

    這是因?yàn)楫?dāng)一個表的數(shù)據(jù)被刪除后，這個表所分配的extent和block是不會自動回收的（高水位線不變），所以CBO如果沒有采樣數(shù)據(jù)塊做分析，只是從數(shù)據(jù)字典中獲取extend等信息，就會誤認(rèn)為任然還有那么多數(shù)據(jù)。下面我們把test表數(shù)據(jù)清空，看看執(zhí)行計劃如何

SQL> delete from test;

86259 rows deleted.

SQL> commit;

SQL> select /*+ dynamic_sampling(test 0) */ * from test;    ----不使用動態(tài)采樣

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |   100K|    19M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |   100K|    19M|   336   (1)| 00:00:05 |

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

SQL> select * from test;                   -----使用動態(tài)采樣

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |   207 |   335   (0)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |     1 |   207 |   335   (0)| 00:00:05 |

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

Note

-----

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

從上面的查看可以看出，不采用動態(tài)采樣和采用動態(tài)采樣的區(qū)別；

5、我們對test表收集下統(tǒng)計信息：再次查詢，該表的執(zhí)行計劃就會少了：dynamic sampling 

SQL> select * from test;

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |   207 |   335   (0)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |     1 |   207 |   335   (0)| 00:00:05 |

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

SQL> 

第二種情況：當(dāng)表TEST即使被分析過，如果查詢腳本里面包含臨時表，就會使用動態(tài)采樣技術(shù)。因?yàn)榕R時表是不會被分析，它是沒有統(tǒng)計信息的。如下所示：

SQL> drop table test;

SQL> create table test as select * from dba_objects;

SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname =>'TEST',cascade=>TRUE);

SQL> create global temporary table tmp (object_type varchar2(19));

SQL> insert into tmp select distinct object_type from dba_objects;

44 rows created.

SQL> commit;

然后查看下面查詢語句的執(zhí)行計劃：

SQL> select t.owner,l.object_type from test t inner join tmp l on t.object_type=l.object_type;

Execution Plan

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

Plan hash value: 19574435

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    26 |   338   (1)| 00:00:05 |

|*  1 |  HASH JOIN         |      |     1 |    26 |   338   (1)| 00:00:05 |

|   2 |   TABLE ACCESS FULL| TMP  |     1 |    11 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST | 86260 |  1263K|   336   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - access("T"."OBJECT_TYPE"="L"."OBJECT_TYPE")

Note

-----

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

SQL> 

從上面可以看到 雖然是對tmp表執(zhí)行的而是全表掃描，但是優(yōu)化器只是估算了1行數(shù)據(jù)

6、動態(tài)采樣還有一個獨(dú)特能力，可以對不同列之間的相關(guān)性做統(tǒng)計。

   表統(tǒng)計信息都是相對獨(dú)立的。當(dāng)查詢涉及列之間的相關(guān)性時,統(tǒng)計信息就顯得有些不足了,請看Tom大師的例子

6.1、創(chuàng)建一個特殊的表t，然后對字段flag1、flag2創(chuàng)建索引t_idx，然后分析收集統(tǒng)計信息

SQL> create table t as select decode(mod(rownum,2),0,'N', 'Y') flag1, decode(mod(rownum,2),0,'Y', 'N') flag2, a.* from all_objects a;

SQL> create index t_idx on t(flag1, flag2);

SQL> begin

    dbms_stats.gather_table_stats(user, 'T',      

          method_opt =>'for all indexed columns size 254');

    end;

    /

PL/SQL procedure successfully completed.

6.2、查看表的行數(shù)：

SQL> select num_rows, num_rows/2, num_rows/2/2 from user_tables  where table_name='T';

  NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2

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

     84396      42198        21099

6.3、看看對flag1過濾條件的SQL語句的執(zhí)行計劃：

SQL> select * from t where flag1='N';

Execution Plan

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 42937 |  4276K|   342   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T    | 42937 |  4276K|   342   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - filter("FLAG1"='N')

從上面的執(zhí)行計劃可以看出：CBO優(yōu)化器猜測、估計的行數(shù)42937， 相當(dāng)接近42198記錄數(shù)了

6.4、看看對flag2過濾條件的SQL語句的執(zhí)行計劃：

SQL> select * from t where flag2='N';

Execution Plan

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 41459 |  4129K|   342   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T    | 41459 |  4129K|   342   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - filter("FLAG2"='N')

從上面的執(zhí)行計劃可以看出：CBO優(yōu)化器猜測、估計的行數(shù)41459， 相當(dāng)接近42198記錄數(shù)了

6.5、如果條件flag1 = 'N' and flag2 = 'N',我們根據(jù)邏輯推理判斷這樣的記錄肯定是不存在的，這也是苦心構(gòu)造這個特例的初衷。下面看看CBO優(yōu)化器怎么探測、預(yù)測的

SQL> select * from t where flag1 = 'N' and flag2 = 'N';

Execution Plan

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 21093 |  2101K|   342   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T    | 21093 |  2101K|   342   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - filter("FLAG2"='N' AND "FLAG1"='N')

從上面看：CBO估計的記錄數(shù)為12468，和實(shí)際情況相差非常遠(yuǎn)。其實(shí)是CBO優(yōu)化器這樣估算來的：

flag1=‘N' 的記錄數(shù)占總數(shù)的1/2

flag2= 'N' 的記錄數(shù)占總數(shù)的1/2

6.6、根據(jù)NUM_ROWS/2/2 =12468.這樣顯然是不合理的。下面我們通過提升動態(tài)采樣級別，來看看動態(tài)采樣是否能避免CBO的錯誤：

SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';

Execution Plan

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

Plan hash value: 470836197

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

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

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

|   0 | SELECT STATEMENT            |       |     6 |   612 |     2   (0)| 00:00:01 |

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

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

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

Predicate Information (identified by operation id):

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

   2 - access("FLAG1"='N' AND "FLAG2"='N')

Note

-----

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

注意：

①：采樣級別越高，采樣的數(shù)據(jù)塊越多，得到的分析數(shù)據(jù)就越接近于真實(shí)，但同時伴隨著資源消耗的開銷也增加了。這時一個需要權(quán)衡考慮的東西。ORACLE 10 g & 11g的默認(rèn)采樣級別都為2,一般使用在會話中使用dynamic_sampling提示來修改動態(tài)采樣級別。

②：凡事有利必有弊，動態(tài)采樣也不是神器。它采樣的數(shù)據(jù)塊越多，系統(tǒng)開銷就越大，這樣會增加SQL硬解析的時間，如果是數(shù)據(jù)庫倉庫（DW、OLAP）環(huán)境，SQL執(zhí)行時間相當(dāng)長，硬解析時間只占整個SQL執(zhí)行時間的一小部分，那么可以適當(dāng)?shù)奶岣邉討B(tài)采樣級別，這樣是有利于優(yōu)化器獲取更加正確的信息。一般設(shè)置為3或4比較合適。

③：在并發(fā)比較嚴(yán)重的OLTP系統(tǒng)中，每秒中有成千上萬的SQL語句執(zhí)行，它要求SQL語句短小、執(zhí)行時間短，所以在OLTP系統(tǒng)中應(yīng)該減低動態(tài)采樣級別或不用動態(tài)采樣。