Dynamic Sampling - What's That?

Many of our DBA friends would have used dbms_xplan.display_cursor to know the execution path of a particular troublesome SQL cursor, and in some cases, the troublesome SQL's take a different path than an expected one. 

Something like this.

    CPU      Elapsed                  CPU per          % Total             

  Time (s)   Time (s)  Executions     Exec (s) % Total DB Time    SQL Id   

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

    39,621     37,137    1,542,605        0.03    10.0     2.1 260cnxfjvb6am

Module: jdbc.EFS.DataSource

select /*+ FIRST_ROWS(1) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, t

ab.delay, tab.expiration, tab.retry_count, tab.exception_qschema, tab.except

ion_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_in

fo, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address,

In such cases,  The most common misconception is that Dynamic Sampling is a replacement for optimizer statistics. But actually it helps optimizer statistics when regular statistics are not good enough good quality cardinality estimates.

Secondly, dynamic sampling kicks of when the SQL has a pretty much complex predicates(Also displayed when dbms_xplan.display_cursor is used). Complex predicates can be like where clause predicates on two correlated columns standard statistics would not be sufficient and will require extended statistics. 

 The Optimizer estimates the cardinality as 1 rows with Standard statistics. But the actual number of rows returned by this query is 2084. By setting OPTIMIZER_DYNAMIC_SAMPLING to level 4, the optimizer will be forced to use dynamic sampling to gather additional information about the complex predicate expression. The additional information provided by dynamic sampling allows the optimizer to generate a more accurate estimate of cardinality and will choose a better execution plan.

Plan hash value: 2338978088
---------------------------------------------------------------------------------------------
|Id   | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)|Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |       |     1 (100)|         |          
|   1 |  FOR UPDATE                  |               |       |       |            |         | 
|   2 |  TABLE ACCESS BY INDEX ROWID | XX_FLOW_AQ    |     1 |  9929 |     1   (0)| 00:00:01|
|   3 |    INDEX RANGE SCAN          | AQ$_FLOW_AQ_I |     1 |       |     1   (0)| 00:00:01|
---------------------------------------------------------------------------------------------


Alter session set optimizer_dynamic_sampling=4;

Plan hash value: 4208087888

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

| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |                  |       |       |     3 (100)|          |

|   1 |  FOR UPDATE         |                  |       |       |            |          |

|   2 |   SORT ORDER BY     |                  |     1 |  29K  |     3  (34)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| XX_FLOW_AQ       |  1518 |  29K  |     2   (0)| 00:00:01 |

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

Note

----

 - dynamic sampling used for this statement

Now below table can explain the various levels of dynamic sampling and, what are the scenario's they kicked off.

HAPPY LEARNING!