oracle10g的dynamic sampling

dynamic sampling主要是用來改善oracle的效能，使其得到更為正確的執行計劃。oracle是透過optimizer_dynamic_sampling這個引數實現動態取樣，在9i裡面dynamic sampling需要在多表關聯的語句裡面才會起作用。在10g裡面optimizer_dynamic_sampling在2及以上級別時單表就能dynamic sampling。

[@more@]

You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10.

• A value of 0 means dynamic sampling will not be done.
• A value of 1 (the default) means dynamic sampling will be performed if all of the following conditions are true:
  • There is more than one table in the query.
  • Some table has not been analyzed and has no indexes.
  • The optimizer determines that a relatively expensive table scan would be required for this unanalyzed table.
• 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.

The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if set to a version prior to 9.0.2.

另外使用DYNAMIC_SAMPLING hint同樣可以做動態取樣：

SELECT /*+ dynamic_sampling(4) */ * 
FROM ...

enables dynamic sampling if all of the following conditions are true:

• There is more than one table in the query.
• Some table has not been analyzed and has no indexes.
• The optimizer determines that a relatively expensive table scan would be required for this table that has not been analyzed.