優化器革命之-Dynamic Sampling（三）
表上存在過時的統計資訊，採用動態取樣情況下，也會表現出一些異常情況：
drop table t purge;
create table t
as
select
rownum as id
, mod(rownum, 10) + 1 as attr1
, rpad('x', 100) as filler
from
dual
connect by
level <= 10
;
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 't',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for all columns size 1',
cascade => false);
end;
/
插入了10條記錄，並對錶做了統計資訊收集。
insert /*+ append */ into t (id, attr1, filler)
select
rownum as id
, mod(rownum, 10) + 1 as attr1
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000 - 10
;
commit;
往表裡灌入了大量的資料，沒有做統計資訊的收集，也就是說，現在資料字典裡的統計資訊過期了。
test@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 10 0,04 0 0 107 YES 10 07-17-2014
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID 10 .10000000 1 0 10 07-17-2014
ATTR1 10 .10000000 1 0 10 07-17-2014
FILLER 1 1.00000000 1 0 10 07-17-2014
我們來看下，在表裡統計資訊過期的情況下，動態取樣會發生哪些異常。
alter session set events '10053 trace name context forever, level 1';
select /*+ dynamic_sampling(5) */
count(*) as cnt
from
t
where
attr1 = 1
and id > 0
;
CNT
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
alter session set events '10053 trace name context off';
執行計劃顯示出的基數為1，而不是與100000接近的數字！！WHY？
我們來看看trace檔案的輸出：
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"=1 AND "T"."ID">0 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392793 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 12:00:59.073
** Executed dynamic sampling query:
level : 5
sample pct. : 0.392793
actual sample size : 3405
filtered sample card. : 341
orig. card. : 10
block cnt. table stat. : 4
block cnt. for sampling: 16039
max. sample block cnt. : 64
sample block cnt. : 63
min. sel. est. : 0.10000000
** Using single table dynamic sel. est. : 0.10014684
Table: T Alias: T
Card: Original: 10.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
我們發現優化器雖然採用了動態取樣獲得的選擇率，但是卻沒有采用動態選擇的基數，而是選用了表的統計資訊裡記錄的基數：10（最後一行）
如何解決這個問題？我們可以通過HINT dynamic_sampling_est_cdn來解決：
alter session set events '10053 trace name context forever, level 1';
test@DLSP> select /*+ dynamic_sampling(5) dynamic_sampling_est_cdn(t) */
2 count(*) as cnt
3 from
4 t
5 where
6 attr1 = 1
7 and id > 0
8 ;
CNT
----------
100000
alter session set events '10053 trace name context off';
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3519 (1)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 86814 | 508K| 3519 (1)| 00:00:43 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ATTR1"=1 AND "ID">0)
Note
-----
- dynamic sampling used for this statement (level=5)
我們看到使用了HINT dynamic_sampling_est_cdn後，優化器已經能使用動態取樣提供的基數資訊。
** 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 /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=1 AND "T"."ID">0 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392793 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 15:24:14.159
** Executed dynamic sampling query:
level : 5
sample pct. : 0.392793
actual sample size : 3843
filtered sample card. : 388
orig. card. : 10
block cnt. table stat. : 16039
block cnt. for sampling: 16039
max. sample block cnt. : 64
sample block cnt. : 63
min. sel. est. : 0.10000000
** Using dynamic sampling card. : 978379
** Using single table dynamic sel. est. : 0.10096279
Table: T Alias: T
Card: Original: 978379.000000 Rounded: 98780 Computed: 98779.87 Non Adjusted: 98779.87
trace檔案的輸出Card: Original: 978379.000000也說明使用了動態取樣的基數，而非表的統計資訊裡記錄的基數10.
drop table t purge;
create table t
as
select
rownum as id
, mod(rownum, 10) + 1 as attr1
, rpad('x', 100) as filler
from
dual
connect by
level <= 10
;
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 't',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for all columns size 1',
cascade => false);
end;
/
插入了10條記錄，並對錶做了統計資訊收集。
insert /*+ append */ into t (id, attr1, filler)
select
rownum as id
, mod(rownum, 10) + 1 as attr1
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000 - 10
;
commit;
往表裡灌入了大量的資料，沒有做統計資訊的收集，也就是說，現在資料字典裡的統計資訊過期了。
test@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 10 0,04 0 0 107 YES 10 07-17-2014
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID 10 .10000000 1 0 10 07-17-2014
ATTR1 10 .10000000 1 0 10 07-17-2014
FILLER 1 1.00000000 1 0 10 07-17-2014
我們來看下，在表裡統計資訊過期的情況下，動態取樣會發生哪些異常。
alter session set events '10053 trace name context forever, level 1';
select /*+ dynamic_sampling(5) */
count(*) as cnt
from
t
where
attr1 = 1
and id > 0
;
CNT
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
alter session set events '10053 trace name context off';
執行計劃顯示出的基數為1，而不是與100000接近的數字！！WHY？
我們來看看trace檔案的輸出：
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"=1 AND "T"."ID">0 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392793 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 12:00:59.073
** Executed dynamic sampling query:
level : 5
sample pct. : 0.392793
actual sample size : 3405
filtered sample card. : 341
orig. card. : 10
block cnt. table stat. : 4
block cnt. for sampling: 16039
max. sample block cnt. : 64
sample block cnt. : 63
min. sel. est. : 0.10000000
** Using single table dynamic sel. est. : 0.10014684
Table: T Alias: T
Card: Original: 10.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
我們發現優化器雖然採用了動態取樣獲得的選擇率，但是卻沒有采用動態選擇的基數，而是選用了表的統計資訊裡記錄的基數：10（最後一行）
如何解決這個問題？我們可以通過HINT dynamic_sampling_est_cdn來解決：
alter session set events '10053 trace name context forever, level 1';
test@DLSP> select /*+ dynamic_sampling(5) dynamic_sampling_est_cdn(t) */
2 count(*) as cnt
3 from
4 t
5 where
6 attr1 = 1
7 and id > 0
8 ;
CNT
----------
100000
alter session set events '10053 trace name context off';
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3519 (1)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 86814 | 508K| 3519 (1)| 00:00:43 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ATTR1"=1 AND "ID">0)
Note
-----
- dynamic sampling used for this statement (level=5)
我們看到使用了HINT dynamic_sampling_est_cdn後，優化器已經能使用動態取樣提供的基數資訊。
** 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 /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=1 AND "T"."ID">0 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392793 , 1) SEED (1) "T") SAMPLESUB
*** 2014-07-17 15:24:14.159
** Executed dynamic sampling query:
level : 5
sample pct. : 0.392793
actual sample size : 3843
filtered sample card. : 388
orig. card. : 10
block cnt. table stat. : 16039
block cnt. for sampling: 16039
max. sample block cnt. : 64
sample block cnt. : 63
min. sel. est. : 0.10000000
** Using dynamic sampling card. : 978379
** Using single table dynamic sel. est. : 0.10096279
Table: T Alias: T
Card: Original: 978379.000000 Rounded: 98780 Computed: 98779.87 Non Adjusted: 98779.87
trace檔案的輸出Card: Original: 978379.000000也說明使用了動態取樣的基數，而非表的統計資訊裡記錄的基數10.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1221196/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 優化器革命之-Dynamic Sampling（二）優化
- 優化器革命之-Dynamic Sampling（五）優化
- 優化器革命之-Dynamic Sampling（四）優化
- 優化器革命之- adaptive cursor sharing （三）優化APT
- 優化器革命之- adaptive cursor sharing （二）優化APT
- 9i,10g 優化器動態取樣級別引數 -- optimizer_dynamic_sampling (統計優化)優化
- 最佳化器革命之- adaptive cursor sharing （三）APT
- 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
- MySQL之SQL優化詳解（三）MySql優化
- 《敏捷革命》（三）敏捷
- 前端效能優化之桌面瀏覽器優化策略前端優化瀏覽器
- VuePress 部落格之 SEO 優化（三）標題、連結優化Vue優化
- dbms_stats.lock_table_stats與動態取樣（Dynamic Sampling）
- CUDA優化之執行配置和暫存器優化優化
- 【MySQL】三、效能優化之 覆蓋索引MySql優化索引
- oracle動態取樣dynamic sampling hint為何不生效一點思考Oracle
- 前端效能優化之移動端瀏覽器優化策略前端優化瀏覽器
- MultiDex（三）之非同步載入優化IDE非同步優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- MSSQL優化之索引優化SQL優化索引
- CUDA優化之指令優化優化
- 第三次技術革命：軟體革命
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Android效能優化（三）之記憶體管理Android優化記憶體
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- 優化器優化
- 前端效能優化 之 瀏覽器快取前端優化瀏覽器快取
- 前端效能JQuery篇之選擇器優化前端jQuery優化
- sql優化之邏輯優化SQL優化
- MySQL 效能優化之索引優化MySql優化索引
- MySQL 效能優化之SQL優化MySql優化