Oracle動態采樣學習

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

公司主營業務：網站制作、成都網站制作、移動網站開發等業務。幫助企業客戶真正實現互聯網宣傳，提高企業的競爭能力。創新互聯是一支青春激揚、勤奮敬業、活力青春激揚、勤奮敬業、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業文化，感謝他們對我們的高要求，感謝他們從不同領域給我們帶來的挑戰，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。創新互聯推出杭錦免費做網站回饋大家。

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

Oracle11G R2 默認的采樣級別：

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

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

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

動態采樣實驗：

1、創建測試表test

SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(1) from test;

COUNT(1)

----------

86259

2、不使用動態采樣，查看執行計劃

SQL> set autotrace traceonly explain;

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

Execution Plan

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

Plan hash value: 1357081020

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

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

| 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)

從上面可以看出，次數優化器估計表test的行數顯示為100K，我們再看下面使用動態采樣的執行計劃，優化器會估算多少行：

3、使用動態采樣，查看執行計劃（下面是直接查詢的，因為在11G 是默認啟用動態采樣的）

SQL> select * from test;

Execution Plan

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

Plan hash value: 1357081020

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

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

| 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)

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

如果我們將動態采樣的級別提高為3，如下所示，發現優化器根據動態采樣得到的信息比默認（默認情況下，動態采樣級別為2）情況獲得的信息更準確。優化器估計表TEST的行數為92364，比72258又接近實際情況一步了。

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

Execution Plan

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

Plan hash value: 1357081020

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

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

| 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大師的這篇文章中提到，在沒有動態采樣的情況下，如果刪除了該表數據，CBO優化器估算的結果集和沒有刪除之前是一樣的。

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

SQL> delete from test;

86259 rows deleted.

SQL> commit;

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

Execution Plan

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

Plan hash value: 1357081020

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

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

| 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; -----使用動態采樣

Execution Plan

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

Plan hash value: 1357081020

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

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

| 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)

從上面的查看可以看出，不采用動態采樣和采用動態采樣的區別；

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

SQL> select * from test;

Execution Plan

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

Plan hash value: 1357081020

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

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

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

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

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

SQL>

第二種情況：當表TEST即使被分析過，如果查詢腳本里面包含臨時表，就會使用動態采樣技術。因為臨時表是不會被分析，它是沒有統計信息的。如下所示：

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;

然后查看下面查詢語句的執行計劃：

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

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

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

| 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表執行的而是全表掃描，但是優化器只是估算了1行數據

6、動態采樣還有一個獨特能力，可以對不同列之間的相關性做統計。

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

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

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、查看表的行數：

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語句的執行計劃：

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

Execution Plan

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

Plan hash value: 1601196873

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

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

| 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')

從上面的執行計劃可以看出：CBO優化器猜測、估計的行數42937，相當接近42198記錄數了

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

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

Execution Plan

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

Plan hash value: 1601196873

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

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

| 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')

從上面的執行計劃可以看出：CBO優化器猜測、估計的行數41459，相當接近42198記錄數了

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

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

Execution Plan

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

Plan hash value: 1601196873

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

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

| 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估計的記錄數為12468，和實際情況相差非常遠。其實是CBO優化器這樣估算來的：

flag1=‘N' 的記錄數占總數的1/2

flag2= 'N' 的記錄數占總數的1/2

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

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

Execution Plan

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

Plan hash value: 470836197

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

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

| 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)

注意：

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

②：凡事有利必有弊，動態采樣也不是神器。它采樣的數據塊越多，系統開銷就越大，這樣會增加SQL硬解析的時間，如果是數據庫倉庫（DW、OLAP）環境，SQL執行時間相當長，硬解析時間只占整個SQL執行時間的一小部分，那么可以適當的提高動態采樣級別，這樣是有利于優化器獲取更加正確的信息。一般設置為3或4比較合適。

③：在并發比較嚴重的OLTP系統中，每秒中有成千上萬的SQL語句執行，它要求SQL語句短小、執行時間短，所以在OLTP系統中應該減低動態采樣級別或不用動態采樣。

分享名稱：Oracle動態采樣學習
瀏覽地址：http://m.kartarina.com/article14/piphge.html

成都網站建設公司_創新互聯，為您提供網站設計公司、網站維護、微信公眾號、小程序開發、網站制作、企業網站制作

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容