優化器革命之-Dynamic Sampling（四）
我們來看下錶級的動態取樣DYNAMIC_SAMPLING(alias,
不過有趣的是，這種表級別的動態取樣跟session/system和cursor level是不同的。
第一個不同：session/system和cursor level的動態取樣不是一定發生的，而是需要滿足條件才發生的。
第二個不同：取樣的塊數也與session/system和cursor level不同。
session/system和cursor level的取樣塊數前面已經有過表格說明，這裡不再給出。我們看下錶級別的取樣塊數，一個簡單的公式：
2^(level - 1)*_optimizer_dyn_smp_blks (_optimizer_dyn_smp_blks的預設值為 32)
因此level 1的取樣塊數為
2^0*32 = 1*32 = 32
level 4的取樣塊數為
2^3*32 = 8 * 32 = 256
level 9的取樣塊數為
2^8*32 = 256 * 32 = 8192
level 10再次出現了跳躍，有2的32次方個塊，也就是一個表中所有的塊。讀者可以自己對比下，表級別的取樣塊數與session/system和cursor level的不同。
這裡還需要注意一點，不知道是不是由於BUG，在使用表級的動態取樣後，執行計劃的輸出Note部分並不能真正的顯示SQL使用的取樣級別，而只是預設顯示系統當前的optimizer_dynamic_sampling引數設定的取樣級別。
test@DLSP>alter session set events '10053 trace name context forever, level 1';
Session altered.
test@DLSP>select /*+ dynamic_sampling(t,5) */
2 count(*) as cnt
3 from
4 t
5 where
6 attr1 = 19
7 and id>1;
alter session set events '10053 trace name context off';
CNT
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3536 (1)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| T | 22 | 572 | 3536 (1)| 00:00:43 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ATTR1"=19 AND "ID">1)
Note
-----
- dynamic sampling used for this statement (level=2)
test@DLSP>show parameter dy
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_dynamic_sampling integer 2
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=19 AND "T"."ID">1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (3.186183 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 16:33:08.779
** Executed dynamic sampling query:
level : 5
sample pct. : 3.186183
actual sample size : 31062
filtered sample card. : 0
orig. card. : 1309984
block cnt. table stat. : 16038
block cnt. for sampling: 16038
max. sample block cnt. : 512
sample block cnt. : 511
min. sel. est. : 0.00050000
** Using dynamic sampling card. : 974897
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00002231
Table: T Alias: T
Card: Original: 974896.978474 Rounded: 22 Computed: 21.75 Non Adjusted: 21.75
雖然我說過表級別的動態取樣會無條件的被執行，但是取樣的結果會被做檢查，如果發現不符合要求，取樣的結果會被拋棄。
最簡單的看到這種現象發生的情況是，構造一個表，分析統計資訊，然後SQL中不要任何謂詞如：
sys@DLSP>@tabstat
Please enter Name of Table Owner: test
Please enter Table Name : t
**********************************************************
Table Level
**********************************************************
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
T 1,000,000 160,38 0 0 109 YES 1,000,000 07-17-2014
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID 1,000,000 .00000100 1 0 1,000,000 07-17-2014
ATTR1 10 .10000000 1 0 1,000,000 07-17-2014
FILLER 1 1.00000000 1 0 1,000,000 07-17-2014
alter session set events '10053 trace name context forever, level 1';
select /*+ DYNAMIC_SAMPLING(t 4) */
count(*) as cnt
from
t;
CNT
----------
1000000
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3531 (1)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1000K| 3531 (1)| 00:00:43 |
-------------------------------------------------------------------
alter session set events '10053 trace name context off';
好像是動態取樣沒有發生，但是事實不是這樣的，由於是表級別的動態取樣，所以一定會發生，會無條件的發生！我們看下trace檔案的資訊：
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM "TEST"."T" SAMPLE BLOCK (1.589974 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 13:56:08.564
** Executed dynamic sampling query:
level : 4
sample pct. : 1.589974
actual sample size : 4159
filtered sample card. : 4159
orig. card. : 1000000
block cnt. table stat. : 16038
block cnt. for sampling: 16038
max. sample block cnt. : 256
sample block cnt. : 255
min. sel. est. : -1.00000000
** Not using dynamic sampling for single table sel. or cardinality.
請注意跟蹤檔案裡的輸出，確實做了塊取樣，取樣的塊數為255，但是塊取樣的結果並沒有被採納。
還可以舉出一些其他案例，例如，動態取樣在處理一些表中極小的結果集的時候可能會存在一些異常，這些異常可能會導致非常不準確的基數估計或者動態取樣的結果不被採用。
select distinct attr1 from t;
ATTR1
----------
1
6
2
4
5
8
3
7
9
10
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 't',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for columns attr1 size 254',
cascade => true);
end;
/
收集了直方圖
alter session set optimizer_dynamic_sampling=4;
alter session set events '10053 trace name context forever, level 1';
count(*) as cnt
from
t
where
attr1 = 19
and id>1;
CNT
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 801713572
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | INDEX RANGE SCAN| AAAA | 1 | 16 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ATTR1"=19 AND "ID">1 AND "ID" IS NOT NULL)
alter session set events '10053 trace name context off';
我們看到輸出結果也沒有使用動態取樣。
*** 2014-07-17 16:13:31.640
** Performing dynamic sampling initial checks. **
Column (#1): ID( NO STATISTICS (using defaults)
AvgLen: 13 NDV: 31250 Nulls: 0 Density: 0.000032
Column (#2):
NewDensity:0.050000, OldDensity:0.000000 BktCnt:1000000, PopBktCnt:1000000, PopValCnt:10, NDV:10
Column (#2): ATTR1(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.050000 Min: 1 Max: 10
Histogram: Freq #Bkts: 10 UncompBkts: 1000000 EndPtVals: 10
Using prorated density: 0.000000 of col #2 as selectvity of out-of-range/non-existent value pred
** Dynamic sampling initial checks returning TRUE (level = 4).
Using prorated density: 0.000000 of col #2 as selectvity of out-of-range/non-existent value pred
*** 2014-07-17 16:13:31.640
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=19 AND "T"."ID">1 THEN 1 ELSE 0 END AS C2, CASE WHEN "T"."ID">1 AND "T"."ATTR1"=19 THEN 1 ELSE 0 END AS C3 FROM "TEST"."T" SAMPLE BLOCK (0.193291 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 16:13:31.646
** Executed dynamic sampling query:
level : 4
sample pct. : 0.193291
actual sample size : 2458
filtered sample card. : 0
filtered sample card. (index AAAA): 0
orig. card. : 1000000
block cnt. table stat. : 16038
block cnt. for sampling: 16038
max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.00000002
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=1bx1yccvbnspn) -----
從trace檔案可以看到動態取樣實際發生了，但是並沒有採納動態取樣結果。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1221265/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 優化器革命之-Dynamic Sampling（二）優化
- 優化器革命之-Dynamic Sampling（五）優化
- 優化器革命之-Dynamic Sampling（三）優化
- 優化器革命之- adaptive cursor sharing （三）優化APT
- 優化器革命之- adaptive cursor sharing （二）優化APT
- 最佳化器革命之- adaptive cursor sharing （四）APT
- 9i,10g 優化器動態取樣級別引數 -- optimizer_dynamic_sampling (統計優化)優化
- oracle10g的dynamic samplingOracle
- Pipelined table function statistics and dynamic samplingFunction
- optimizer_dynamic_sampling引數的理解
- oracle動態取樣_optimizer_dynamic_samplingOracle
- oracle動態取樣_optimizer_dynamic_sampling(二)Oracle
- ptimizer_dynamic_sampling設定為4的作用。
- sql profile禁用oracle動態取樣dynamic samplingSQLOracle
- 最佳化器革命之- adaptive cursor sharing （三）APT
- Android 效能優化（四）之記憶體優化實戰Android優化記憶體
- 前端效能優化之桌面瀏覽器優化策略前端優化瀏覽器
- dbms_stats.lock_table_stats與動態取樣（Dynamic Sampling）
- CUDA優化之執行配置和暫存器優化優化
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- VuePress 部落格之 SEO 優化（四） Open Graph protocolVue優化Protocol
- oracle動態取樣dynamic sampling hint為何不生效一點思考Oracle
- 前端效能優化之移動端瀏覽器優化策略前端優化瀏覽器
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- MSSQL優化之索引優化SQL優化索引
- CUDA優化之指令優化優化
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- # Kotlin使用優化（四）Kotlin優化
- 優化器優化
- Linux效能優化實戰CPU篇之總結（四）Linux優化
- 前端效能優化 之 瀏覽器快取前端優化瀏覽器快取
- 前端效能JQuery篇之選擇器優化前端jQuery優化
- sql優化之邏輯優化SQL優化
- MySQL 效能優化之索引優化MySql優化索引
- MySQL 效能優化之SQL優化MySql優化
- Oracle 11g中dynamic sampling自動調節（auto-adjusted）機制Oracle
- Flutter 的革命之處Flutter
- 《MSSQL2008高階教程》之四“SQL優化”SQL優化