9i,10g 優化器動態取樣級別引數 -- optimizer_dynamic_sampling (統計優化)

             我們經常會碰到由於一些表沒有分析導致執行計劃錯誤的情況，但是Oracle9.2 中dynamic sampling的出現一定程度的減少了錯誤的產生。dynamic sampling針對沒有分析過的表可以取樣估計表的選擇性，對於生產正確的執行計劃有一定的幫助。 

           優化器動態取樣級別引數 optimizer_dynamic_sampling ，是在Oracle9.2.0 版本之後才有的。設定級別可以由0到10， 0表示不進行動態取樣， 10 表示取樣級別最高。 Oracle9.2或以上(9i)下引數 optimizer_dynamic_sampling 預設值是1，Oracle10g 下預設值是2 。

Level 0:   Do not dynamically sample the table(s)   不進行動態取樣 。

Level 1： Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects  

Level 2:    Sample all unanalyzed tables referenced in teh query using default sampling amounts(small sample)

Level 3 -- Level 10     更詳細的sample 。

              如果一個表沒有統計資訊，那麼，CBO優化器將採用預設的統計資料，這樣是很不準確的， 會直接導致錯誤的執行計劃 。利用optimizer_dynamic_sampling引數，CBO優化器可收集引用物件上足夠的統計資訊得出更加準確的執行計劃。 不過在Oracle9中需要有多表關聯的時候optimizer_dynamic_sampling才會起作用，單個表不起作用。在10g中在optimizer_dynamic_sampling >=2  (10g預設是2) 的時候單個表就能實現優化器動態取樣 。 

              我們知道，在Oracle9i, 10g中如果選擇的是CHOOSE優化模式，那麼在沒有分析統計資訊的時候，Oracle會選擇使用RULE 方式，Oracle9i中，如果使用HINT強制用CBO的話，系統會讀取一點資訊來模擬實際的統計資訊。當然Oracle10g 預設的優化模式是CBO ，所以除非人為更改，一般不會走RULE模式。 

               Oracle9i中如果關聯的兩個表都沒有分析，那麼  optimizer_dynamic_sampling 不起作用(可以sql trace 測試一下) ， 關聯的表中有一個分析過了，那麼優化器動態取樣就起作用了。

              但是Oracle10g中如果兩個表都沒有分析，optimizer_dynamic_sampling 也會起作用。 以上應該都是由於Oracle10g中已經沒有了基於RULE的優化器，在9i中如果一個查詢中所有的表都沒有分析，那麼它會選擇rule優化器，而忽略掉  dynamic  sampling  。10g中已經沒有了rule ,   沒有選擇，只有 dynamic  sampling。 

 

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

以下的解釋還在理解中，有朋友能很好理解關於取樣資料塊的請幫忙解釋一下 ： 

Level 1:    Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query;    (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view;   (3) this unanalyzed table has no indexes;    (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).  

Level 2:      Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is the default number of dynamic sampling blocks.

 

 

 

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

更加詳細的Level :

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

Level 0:    Do not use dynamic sampling.
Level 1:    Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2:      Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 3:      Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 4:      Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 5:      Apply dynamic sampling to all tables that meet the Level 4 criteria using 2 times the default number of dynamic sampling blocks.
Level 6:      Apply dynamic sampling to all tables that meet the Level 5 criteria using 4 times the default number of dynamic sampling blocks.
Level 7:      Apply dynamic sampling to all tables that meet the Level 6 criteria using 8 times the default number of dynamic sampling blocks.
Level 8:      Apply dynamic sampling to all tables that meet the Level 7 criteria using 32 times the default number of dynamic sampling blocks.
Level 9:     Apply dynamic sampling to all tables that meet the Level 8 criteria using 128 times the default number of dynamic sampling blocks.
Level 10:    Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-592375/，如需轉載，請註明出處，否則將追究法律責任。