Friday, June 13, 2008

Dynamic Sampling Levels

Dynamic sampling is enabled whenever OPTIMIZER_DYNAMIC_SAMPLING is set to a rather than 0. (i.e between 1 and 10).
To know the current settings just issue,
SQL> show parameter OPTIMIZER_DYNAMIC_SAMPLING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
Here level is 2 that means dynamic sampling is enabled.
Though level can be specified between 0 to 10 and different level indicates different meaning. In the following section it is specified what they mean.

•Level 0: Do not use dynamic sampling.

•Level 1: Sample all tables that have not been analyzed. In this case 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 two times 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. For unanalyzed tables, the number of blocks sampled is two times 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. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

•Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

•Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

No comments:

Post a Comment