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.
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;
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!
