SQL Tuning : Dynamic sampling

• Optimizer_dynamic_sampling parameter

Default value 2, 0 to 10

• Goal

Create more accurate selectivity and cardinality estimates

• Level
• 1 : sample tables that appear in join or subquery conditions that have no indexes
• 2 : Samples all unanalyzed tables that have more than 32 blocks
• 3 : Samples using a column that applies the selectivity of the table
• 4 : 2 or more columns 
• 5 : 64 blocks used
• 6 : 128 blocks

Table with >32 blocks

• dynamic_sampling hint

select /* dynamic_sampling (customer 4) */
  customer_name ...

Useful for tables that are created dynamically such as GTT

• Scope

Ideal for a query that are going to be executed many times
When there is complex where clause