【sql調優】動態取樣

1. Dynamic Sampling （動態取樣）
    The purpose of dynamic sampling is to improve server performance by determining more accurate selectivity and cardinality estimates.
    More accurate selectivity and cardinality estimates allow the optimizer to produce better performing plans.
    （動態取樣的目的是為了透過更精確的seletivity值cardinality值來提高伺服器效能，更精確的seletivity值cardinality值可以讓最佳化器提供更好的執行計劃。）
    Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.
    Estimate table cardinality for tables without statistics or for tables whose statistics are too out of date to trust.
    （當沒有使用statistics統計資訊或者可能導致評估錯誤的時候，可以提前預估出來單表的selectivities值。
      當表沒有收集統計資訊時，或者表的統計資訊過期的時候，可以估算出表的cardinality值。）
  
2. How Dynamic Sampling Works（動態取樣如何工作）
   The primary performance attribute is compile time.
   Oracle determines at compile time whether a query would benefit from dynamic sampling.
   If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks,
   and to apply the relevant single table predicates to estimate predicate selectivities.
   The sample cardinality can also be used, in some cases, to estimate table cardinality.
   （主要的效能影響被歸因於編譯時間。ORACLE來判斷在編譯的時候，動態取樣是否對查詢是否有意。如果是，那麼sql語句會發起
   相對應表的快的小部分隨機取樣，然後應用相關的單表去前瞻性預估相應的selectivities值。
3. When to Use Dynamic Sampling（什麼時候使用動態取樣）
   (1) A better plan can be found using dynamic sampling.
       使用動態取樣可以更好的生成執行計劃
   (2) The sampling time is a small fraction of total execution time for the query.
       動態取樣的時間佔查詢執行的時間一小部分
   (3) The query will be executed many times.
       查詢語句將被執行許多次
 
4. How to Use Dynamic Sampling to Improve Performance
   （如何使用動態取樣提高效能）
 
   Level 0: dynamic sampling will not be done.
            （動態取樣不會收集）
 
   Level 1: (default value) dynamic sampling will be performed if all of the following conditions are true:
             預設值，如果如下的條件全部滿足的時候，那麼動態取樣將被執行
           (1) There is more than one table in the query.
             有超過一個表的查詢
           (2) Some table has not been analyzed and has no indexes.
             一些表沒有被分析，而且沒有index
           (3) The optimizer determines that a relatively expensive table scan would be required for this unanalyzed table.        最佳化器認為這個沒有被分析的表會消耗相當昂貴的表掃描資源）
             
   Level 2: Apply dynamic sampling to all unanalyzed tables.
            The number of blocks sampled is the default number of dynamic sampling blocks.
            （針對所有沒有被分析的表應用動態取樣，取樣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.
            （根據level2的標準，應用動態取樣到所有的表，以及為一些標準selectivity值的表使用一些取樣預測，取樣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.
            （根據level3的標準，應用動態取樣到所有的表，以及一些大於2列的單表的預測。取樣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.
           （根據level4的標準，應用動態取樣到所有的表，並且取樣blocks的數量是預設的動態取樣的數量的2倍）          
 
   Level 6: Apply dynamic sampling to all tables that meet the Level 5 criteria using 4 times the default number of dynamic sampling blocks.
           （根據level5的標準，應用動態取樣到所有的表，並且取樣blocks的數量是預設的動態取樣的數量的4倍）
 
   Level 7: Apply dynamic sampling to all tables that meet the Level 6 criteria using 8 times the default number of dynamic sampling blocks.
            （根據level6的標準，應用動態取樣到所有的表，並且取樣blocks的數量是預設的動態取樣的數量的8倍）
 
   Level 8: Apply dynamic sampling to all tables that meet the Level 7 criteria using 32 times the default number of dynamic sampling blocks.
            （根據level7的標準，應用動態取樣到所有的表，並且取樣blocks的數量是預設的動態取樣的數量的32倍）
 
   Level 9: Apply dynamic sampling to all tables that meet the Level 8 criteria using 128 times the default number of dynamic sampling blocks.
            （根據level8的標準，應用動態取樣到所有的表，並且取樣blocks的數量是預設的動態取樣的數量的128倍）

   Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
            （根據level9的標準，應用動態取樣到所有的表，並且取樣表中所有的blocks）
 
 
   Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.
   Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled.
   （增加這個引數的值，從表的取樣和I/O消耗的角度來說，動態取樣將導致更多資源的徵用。
     在被取樣的表中，即使沒有記錄被insert, deleted, update，取樣的操作仍會被重複。）
  來自：http://space.itpub.net/9252210/viewspace-608724