First page Back Continue Last page Overview Graphics
…Dynamic Sampling…
Control with optimizer_dynamic_sampling parameter or dynamic_sampling hint
- 0 – no sampling
- 1 through 10 – Various levels of sampling;
- 10 most aggressive, does most work
Notes:
Dynamic sampling is controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter and the DYNAMIC_SAMPLING hint. Set to 0 to turn of the feature. Set to 1 through 10 to control the level of sampling.
In the first example above, table-level sampling level 10 is specified. This will cause Oracle to read all blocks in the “T” table to collect statistics during parse.
In the second example, cursor-level sampling level 1 is specified. (Note the absence of a table name in the hint; this creates cursor-level sampling, which has different sampling criteria.) According to this quote from the Oracle Performance and Tuning manual, sampling will be done if:
“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. “
Refer to the Oracle manual for complete descriptions of the sampling levels.
Supplemental Notes
1 is the default OPTIMIZER_DYNAMIC_SAMPLING. In essence, this causes Oracle to sample if there is an unanalyzed table in the query.