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.

-----------------------------------------------------------------------------------------------------------

The optimizer_dynamic_sampling parameter in Oracle Database controls the level of dynamic sampling performed by the Oracle Optimizer during query parsing. 

Dynamic sampling helps the optimizer gather additional runtime statistics when regular (stored) statistics are inadequate or missing, allowing it to make better decisions about query execution plans.

---

How It Works

When the optimizer encounters situations where it cannot rely on existing statistics (e.g., due to stale, missing, or insufficient statistics), it samples the data at runtime to estimate cardinalities (row counts), selectivities, and other key metrics.

---

Parameter Levels

The optimizer_dynamic_sampling parameter can be set to a value from 0 to 11, with each level defining the amount of sampling the optimizer performs.

Levels Overview:

The optimizer_dynamic_sampling parameter can be set to a value from 0 to 11, with each level defining the amount of sampling the optimizer performs.

Levels Overview:

---

Default Value

• Default Value in 19c and Later: 2 for complex queries.
• For simple queries or when sufficient statistics are available, Oracle may automatically adjust sampling to 0 (no sampling).

---

When Is It Used?

Dynamic sampling is automatically triggered:

1. When Statistics Are Missing:

   • If a query accesses a table or partition without statistics.

2. For Complex Queries:

   • Complex SQL involving joins, subqueries, or filters may benefit from dynamic sampling.

3. When Data Volumes Are Large:

• Oracle may sample to avoid underestimating or overestimating row counts, especially for large datasets.

---

Setting the Parameter

• Session-Level Setting:

  
  ALTER SESSION SET optimizer_dynamic_sampling = 3;
  

• System-Level Setting:

  
  ALTER SYSTEM SET optimizer_dynamic_sampling = 3 SCOPE=BOTH;
  

• At Query Level (using a hint):

  
  SELECT /*+ dynamic_sampling(4) */ * FROM orders WHERE region = 'WEST';
  

---

Best Practices

1. Set to Default (2) for most environments:

   • Suitable for moderately complex queries and when statistics are mostly accurate.

2. Increase to 3 or Higher:

   • For highly complex queries, data skew, or when query plans are suboptimal due to inaccurate estimates.

3. Avoid Overhead:

   • Higher levels (e.g., 10 or 11) can introduce significant overhead, as they may involve extensive sampling.

4. Combine with Regular Statistics:

• Use DBMS_STATS to gather and maintain statistics proactively. Dynamic sampling should complement, not replace, proper statistics.

---

Monitoring and Debugging

• Check Sampling in Execution Plan:

  
  SELECT * FROM v$sql_plan WHERE operation = 'DYNAMIC SAMPLING';
  

• Enable Optimizer Trace:

  
  ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
  

Dynamic sampling ensures that the Oracle Optimizer makes more informed decisions, particularly in environments with large or complex datasets, reducing the likelihood of inefficient query plans.