Dynamic Sampling - optimizer_dynamic_sampling- in Oracle

 

Dynamic Sampling is an Oracle feature that collects statistical information on query-related objects at runtime, which helps the optimizer make better decisions for query execution plans. When the optimizer encounters insufficient or stale statistics, it uses dynamic sampling to gather additional data to estimate the most efficient execution plan.

What Dynamic Sampling Level 2 Means

The level=2 indicates the degree to which Oracle will collect this extra information:

• Level 2 tells Oracle to sample all unanalyzed tables involved in the query.
• This means that if your query includes tables without statistics (e.g., newly created tables), Oracle will perform a quick scan on those tables to gather data needed to estimate cardinality (row counts) and selectivity (filter effectiveness).

Dynamic sampling levels range from 0 to 11, with each level adjusting the scope and depth of sampling:

• Level 0: No dynamic sampling (Oracle does not collect additional information).
• Level 1: Samples a small portion of unanalyzed tables.
• Level 2: Samples unanalyzed tables, performing more in-depth sampling than Level 1.
• Levels 3-11: Increase in depth and complexity, sampling more tables and considering more extensive combinations of joins, aggregations, and filters.

When Dynamic Sampling is Triggered

Dynamic sampling generally activates in these situations:

1. Insufficient Statistics: When Oracle detects missing or outdated statistics on tables, indexes, or joins used in the query.
2. Complex Queries: For complex queries involving multiple tables, subqueries, or joins, Oracle may automatically apply dynamic sampling.
3. Optimizer Hints or Settings: Specific optimizer hints (e.g., DYNAMIC_SAMPLING) and session/system parameters can explicitly set the dynamic sampling level.

Benefits of Dynamic Sampling

Dynamic sampling helps to:

• Improve query performance by providing the optimizer with accurate row estimates.
• Avoid full table scans or inefficient join methods in cases of missing statistics.
• Make runtime adjustments to execution plans, especially in data warehouses or environments with volatile data.

Example

If dynamic sampling (Level 2) is in use for a query, it means Oracle is gathering some statistics during query execution, specifically on tables that lack statistics, to make a more informed choice of the execution plan.

Note: Although helpful, high levels of dynamic sampling can introduce some overhead, especially on large datasets, so it's often best to use it selectively.