動態(tài)采樣(Dynamic Sampling)是在ORACLE 9i Release 2中開始引入的一個技術,引入它的目的是為了應對數(shù)據(jù)庫對象沒有分析(統(tǒng)計信息缺失)的情況下,優(yōu)化器生成更好的執(zhí)行計劃。簡單的說,在數(shù)據(jù)庫段(表、索引、分區(qū))對象沒有分析的情況下,為了使CBO優(yōu)化器得到足夠多的信息以保證優(yōu)化器做出正確執(zhí)行計劃而發(fā)明的一種技術。它會分析一定數(shù)量段對象上的數(shù)據(jù)塊獲取CBO需要的統(tǒng)計信息。動態(tài)采樣技術僅僅是統(tǒng)計信息的一種補充,它不能完全替代統(tǒng)計信息分析。
公司主營業(yè)務:網(wǎng)站制作、成都網(wǎng)站制作、移動網(wǎng)站開發(fā)等業(yè)務。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳,提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化,感謝他們對我們的高要求,感謝他們從不同領域給我們帶來的挑戰(zhàn),讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)推出杭錦免費做網(wǎng)站回饋大家。
注意:動態(tài)采樣在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
動態(tài)采樣的級別有11個級別:請自行查看官方文檔
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101
動態(tài)采樣實驗:
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í)行計劃(下面是直接查詢的,因為在11G 是默認啟用動態(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)采樣(默認情況下,動態(tài)采樣級別為2),優(yōu)化器根據(jù)動態(tài)采樣得到一些數(shù)據(jù)信息猜測、估計表TEST的記錄行數(shù)為86259,已經(jīng)接近實際記錄行數(shù)72258了。比不做動態(tài)采樣分析要好很多了。
如果我們將動態(tài)采樣的級別提高為3,如下所示,發(fā)現(xiàn)優(yōu)化器根據(jù)動態(tài)采樣得到的信息比默認(默認情況下,動態(tài)采樣級別為2)情況獲得的信息更準確。優(yōu)化器估計表TEST的行數(shù)為92364,比72258又接近實際情況一步了。
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)化器估算的結果集和沒有刪除之前是一樣的。
這是因為當一個表的數(shù)據(jù)被刪除后,這個表所分配的extent和block是不會自動回收的(高水位線不變),所以CBO如果沒有采樣數(shù)據(jù)塊做分析,只是從數(shù)據(jù)字典中獲取extend等信息,就會誤認為任然還有那么多數(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>
第二種情況:當表TEST即使被分析過,如果查詢腳本里面包含臨時表,就會使用動態(tài)采樣技術。因為臨時表是不會被分析,它是沒有統(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)采樣還有一個獨特能力,可以對不同列之間的相關性做統(tǒng)計。
表統(tǒng)計信息都是相對獨立的。當查詢涉及列之間的相關性時,統(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, 相當接近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, 相當接近42198記錄數(shù)了
6.5、如果條件flag1 = 'N' and flag2 = 'N',我們根據(jù)邏輯推理判斷這樣的記錄肯定是不存在的,這也是苦心構造這個特例的初衷。下面看看CBO優(yōu)化器怎么探測、預測的
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,和實際情況相差非常遠。其實是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ù)就越接近于真實,但同時伴隨著資源消耗的開銷也增加了。這時一個需要權衡考慮的東西。ORACLE 10 g & 11g的默認采樣級別都為2,一般使用在會話中使用dynamic_sampling提示來修改動態(tài)采樣級別。
②:凡事有利必有弊,動態(tài)采樣也不是神器。它采樣的數(shù)據(jù)塊越多,系統(tǒng)開銷就越大,這樣會增加SQL硬解析的時間,如果是數(shù)據(jù)庫倉庫(DW、OLAP)環(huán)境,SQL執(zhí)行時間相當長,硬解析時間只占整個SQL執(zhí)行時間的一小部分,那么可以適當?shù)奶岣邉討B(tài)采樣級別,這樣是有利于優(yōu)化器獲取更加正確的信息。一般設置為3或4比較合適。
③:在并發(fā)比較嚴重的OLTP系統(tǒng)中,每秒中有成千上萬的SQL語句執(zhí)行,它要求SQL語句短小、執(zhí)行時間短,所以在OLTP系統(tǒng)中應該減低動態(tài)采樣級別或不用動態(tài)采樣。
分享名稱:Oracle動態(tài)采樣學習
瀏覽地址:http://muchs.cn/article14/piphge.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站設計公司、網(wǎng)站維護、微信公眾號、小程序開發(fā)、網(wǎng)站制作、企業(yè)網(wǎng)站制作
聲明:本網(wǎng)站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經(jīng)允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)