Mohamed Houri’s Oracle Notes

June 12, 2015

Why Dynamic Sampling has not been used?

Filed under: Oracle — hourim @ 10:15 am

Experienced tuning guys are known for their pronounced sense of looking at details others are very often ignoring. This is why I am always paying attention to their answers in otn and oracle-l list. Last week I have been asked to look at a query performing badly which has been monitored via the following execution plan:

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 SQL ID              :  8114dqz1k5arj
 SQL Execution ID    :  16777217            

Global Stats
=============================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Cluster  |  Other   | Buffer | Read  | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs  | Bytes |
=============================================================================================
|  141842 |  140516 |       75 |        5.82 |       69 |     1176 |    21G | 26123 | 204MB |
=============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3787402507)
===========================================================================================
| Id   |             Operation             |      Name       |  Rows   | Execs |   Rows   |
|      |                                   |                 | (Estim) |       | (Actual) |
===========================================================================================
|    0 | SELECT STATEMENT                  |                 |         |     1 |          |
|    1 |   SORT ORDER BY                   |                 |       1 |     1 |          |
|    2 |    FILTER                         |                 |         |     1 |          |
|    3 |     NESTED LOOPS                  |                 |         |     1 |        0 |
| -> 4 |      NESTED LOOPS                 |                 |       1 |     1 |       4G |
| -> 5 |       TABLE ACCESS BY INDEX ROWID | TABLEXXX        |       1 |     1 |     214K |
| -> 6 |        INDEX RANGE SCAN           | IDX_MESS_RCV_ID |      2M |     1 |     233K |
| -> 7 |       INDEX RANGE SCAN            | VGY_TEST2       |       1 |  214K |       4G |->
|    8 |      TABLE ACCESS BY INDEX ROWID  | T_TABL_YXZ      |       1 |    4G |        0 |->
|      |                                   |                 |         |       |          |
===========================================================================================

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:SYS_B_2,:SYS_B_3)<=TO_DATE(:SYS_B_4,:SYS_B_5))
   5 - filter(("TABLEXXX"."T_NAME"=:SYS_B_6 AND
              "TABLEXXX"."M_TYPE"=:SYS_B_0 AND
              "TABLEXXX"."A_METHOD"=:SYS_B_7 AND
              "TABLEXXX"."M_STATUS"<>:SYS_B_8))
   6 - access("TABLEXXX"."R_ID"=:SYS_B_1)
   7 - access("T_TABL_YXZ"."SX_DATE">=TO_DATE(:SYS_B_2,:SYS_B_3) AND
              "T_TABL_YXZ"."SX_DATE"<=TO_DATE(:SYS_B_4,:SYS_B_5))
   8 - filter("T_TABL_YXZ"."T_ID"="TABLEXXX"."T_ID")

Those 214K and 4G executions (Execs) of operations 7 and 8 respectively are the classical wrong NESTED LOOP join the CBO has decided to go with because of the wrong cardinality estimation at operation n° 5 (the double NESTED LOOP operation is the effect of the NLJ_BATCHING optimisation).
There was no previous historical plan_hash_value for this particular sql_id in order to compare with the current execution plan. But the report has certainly been executed in the past without any complaint from the end user.
The outline_data section of the execution plan is where I usually look when trying to understand what the optimizer has done behind the scene:

Outline Data
-------------
   /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('optimizer_dynamic_sampling' 4) ---------------------------> spot this
      OPT_PARAM('optimizer_index_cost_adj' 20)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TABLEXXX"@"SEL$1" ("TABLEXXX"."R_ID"))
      INDEX(@"SEL$1" "T_TABL_YXZ"@"SEL$1" ("T_TABL_YXZ"."SX_DATE"
              "T_TABL_YXZ"."GL_ACCOUNT_ID" "T_TABL_YXZ"."CASH_ACCOUNT_ID"))
      LEADING(@"SEL$1" "TABLEXXX"@"SEL$1" "T_TABL_YXZ"@"SEL$1")
      USE_NL(@"SEL$1" "T_TABL_YXZ"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "T_TABL_YXZ"@"SEL$1")
      END_OUTLINE_DATA
  */

As you can see apart from the optimizer_index_cost_adj parameter value we should never change, there is one thing that has kept my attention: optimizer_dynamic_sampling. Since the outline is showing that the optimizer has used dynamic sampling why then there is no Note about dynamic sampling at the bottom of the above corresponding execution plan?
I decided to run the same query in a CLONE data base (cloned via RMAN). Below is the corresponding execution plan for the same set of input parameters:

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 SQL ID              :  8114dqz1k5arj
 SQL Execution ID    :  16777217
 Duration            :  904s           

SQL Plan Monitoring Details (Plan Hash Value=2202725716)
========================================================================================
| Id |            Operation             |      Name       |  Rows   | Execs |   Rows   |
|    |                                  |                 | (Estim) |       | (Actual) |
========================================================================================
|  0 | SELECT STATEMENT                 |                 |         |     1 |      280 |
|  1 |   SORT ORDER BY                  |                 |    230K |     1 |      280 |
|  2 |    FILTER                        |                 |         |     1 |      280 |
|  3 |     HASH JOIN                    |                 |    230K |     1 |      280 |
|  4 |      TABLE ACCESS BY INDEX ROWID | T_TABL_YXZ      |    229K |     1 |     301K |
|  5 |       INDEX RANGE SCAN           | VGY_TEST2       |       1 |     1 |     301K |
|  6 |      TABLE ACCESS BY INDEX ROWID | TABLEXXX        |    263K |     1 |       2M |
|  7 |       INDEX RANGE SCAN           | IDX_MESS_RCV_ID |      2M |     1 |       2M |
========================================================================================

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:SYS_B_2,:SYS_B_3)<=TO_DATE(:SYS_B_4,:SYS_B_5))
   3 - access("T_TABL_YXZ"."T_ID"="TABLEXXX"."T_ID")
   5 - access("T_TABL_YXZ"."SX_DATE">=TO_DATE(:SYS_B_2,:SYS_B_3) AND
              "T_TABL_YXZ"."SX_DATE"<=TO_DATE(:SYS_B_4,:SYS_B_5))
   6 - filter(("TABLEXXX"."T_NAME"=:SYS_B_6 AND
              "TABLEXXX"."M_TYPE"=:SYS_B_0 AND
              "TABLEXXX"."A_METHOD"=:SYS_B_7 AND
              "TABLEXXX"."M_STATUS"<>:SYS_B_8))
   7 - access("TABLEXXX"."R_ID"=:SYS_B_1)

Note
-----
   - dynamic sampling used for this statement (level=4)

In this CLONED database, in contrast to the Production database, the optimizer has used dynamic sampling at its level 4 and has come up with a different estimation when visiting TABLEXXX (263K instead of 1) and T_TABL_YXZ (229K instead of 1) tables so that it has judiciously opted for a HASH JOIN instead of that dramatic production NESTED LOOP operation making the query completing in 904 seconds.

The fundamental question turns then from why the report is performing badly to why the optimizer has ignored using dynamic sampling at level 4?

There are several ways to answer this question (a) 10053 trace file, (b) 10046 or (c) trace file or tracing directly dynamic sampling as it has been suggested to me by Stefan Koehler

SQL> alter session set events 'trace[RDBMS.SQL_DS] disk=high';

The corresponding 10053 optimizer trace shows the following lines related to dynamic sampling:

10053 of the COPY database

*** 2015-06-03 11:05:43.701
** Executed dynamic sampling query:
    level : 4
    sample pct. : 0.000489
    actual sample size : 837
    filtered sample card. : 1
    orig. card. : 220161278
    block cnt. table stat. : 6272290
    block cnt. for sampling: 6345946
    max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.00000000
** Using single table dynamic sel. est. : 0.00119474
  Table: TABLEXXX  Alias: TABLEXXX
    Card: Original: 220161278.000000  Rounded: 263036  Computed: 263036.17  Non Adjusted: 263036.17

In the COPY data base, the optimiser has used dynamic sampling at level 4 and did come up with a cardinality estimation of TABLEXXX of be 263K which obviously has conducted the CBO to opt for a reasonable HASH JOIN operation.

10053 of the PRODUCTION database

*** 2015-06-03 13:39:03.992
** Executed dynamic sampling query:
    level : 4
    sample pct. : 0.000482
    actual sample size : 1151
    filtered sample card. : 0  ------------------>  spot this information
    orig. card. : 220161278
    block cnt. table stat. : 6272290
    block cnt. for sampling: 6435970
    max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.00000000
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=82x3mm8jqn5ah) -----
  Table: TABLEXXX  Alias: TABLEXXX
    Card: Original: 220161278.000000  Rounded: 1  Computed: 0.72  Non Adjusted: 0.72

In the PRODUCTION database, the CBO failed to use dynamic sampling at level 4 as clearly shown by the following line taken from the above 10053 trace file:

** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=82x3mm8jqn5ah)

PS: 10053 trace file has been applied on the important part of the query this is
    why the sql_id is not the same as the one mentioned above.

Thanks to Randolf Geist I learnt that the internal code of the Dynamic Sampling algorithm is so that when the predicate part has been applied on a sample of the TABLEXXX it returned 0 rows

filtered sample card. : 0

which is the reason why the optimizer has ignored Dynamic sampling at level 4 and falls back to the available object statistics producing a 1 row cardinality estimation and henceforth a dramatic wrong NESTED LOOP operation. By the way, should I have been in 12c database release the STATISTICS COLLECTOR placed above the first operation in the NESTED LOOP join would have reached the inflexion point and would have, hopefully, switched to a HASH JOIN operation during execution time.

A quick solution to this very critical report was to up the level of the dynamic sampling to a higher value. And, as far as this query belongs to a third party software I decided to use Kerry Osborne script in order to inject a dynamic sampling hint as shown below:

SQL>@create_1_hint_sql_profile.sql
Enter value for sql_id: 8114dqz1k5arj
Enter value for profile_name (PROFILE_sqlid_MANUAL):
Enter value for category (DEFAULT):
Enter value for force_matching (false): true
Enter value for hint: dynamic_sampling(6)
Profile PROFILE_8114dqz1k5arj_MANUAL created.

Once this done, the end user re-launched the report which completed within 303 seconds instead of those not ending 141,842 seconds


Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 SQL ID              :  8114dqz1k5arj
 SQL Execution ID    :  16777216
 Execution Started   :  06/10/2015 11:40:39
 First Refresh Time  :  06/10/2015 11:40:45
 Last Refresh Time   :  06/10/2015 11:45:39
 Duration            :  300s           

SQL Plan Monitoring Details (Plan Hash Value=2202725716)
========================================================================================
| Id |            Operation             |      Name       |  Rows   | Execs |   Rows   |
|    |                                  |                 | (Estim) |       | (Actual) |
========================================================================================
|  0 | SELECT STATEMENT                 |                 |         |     1 |     2989 |
|  1 |   SORT ORDER BY                  |                 |    234K |     1 |     2989 |
|  2 |    FILTER                        |                 |         |     1 |     2989 |
|  3 |     HASH JOIN                    |                 |    234K |     1 |     2989 |
|  4 |      TABLE ACCESS BY INDEX ROWID | T_TABL_YXZ      |    232K |     1 |     501K |
|  5 |       INDEX RANGE SCAN           | VGY_TEST2       |       1 |     1 |     501K |
|  6 |      TABLE ACCESS BY INDEX ROWID | TABLEXXX        |    725K |     1 |       2M |
|  7 |       INDEX RANGE SCAN           | IDX_MESS_RCV_ID |      2M |     1 |       2M |
========================================================================================

Note
-----
   - dynamic sampling used for this statement (level=6)
   - SQL profile PROFILE_8114dqz1k5arj_MANUAL used for this statement

8 Comments »

  1. Hi Mouhmad,

    Thanks for this sharing ,
    Did you havec the same issue when optimizer_dynamic_sampling=6.

    There is a confirmed bug :
    “Bug 16725982 Dynamic sampling does not occur for level 4 if filter predicate involves virtual expression”

    May be this behavior can be related this bug.

    Thanks,
    Cherif.

    Comment by cherif ben henda — June 12, 2015 @ 1:18 pm | Reply

  2. Cherif,

    As you can read above when I set dynamic sampling to level 6 Oracle optimizer has used it and came up with a good estimation

    Note
    -----
       - dynamic sampling used for this statement (level=6)
       - SQL profile PROFILE_8114dqz1k5arj_MANUAL used for this statement
    

    There is no virtual column involved here. But since you’ve mentionned it I have definitely set it in my mind and will take into account this virtual column bug when confronted to a similar issue
    Best regards

    Comment by hourim — June 12, 2015 @ 1:31 pm | Reply

  3. Hi Mohamed

    FWIW, the more I work with DS, the more I’m convinced that lower levels like 3-5 are useless. If you really need DS, plenty of blocks have to be accessed. This is the reason why I added the following text in TOP2 (page 284).

    “Depending on the data you’re working with, level 6 or 7 might be required to ensure that dynamic sampling
    generates useful insights. After all, even at level 7, at most 256 blocks are sampled. Depending on the amount of data
    and its distribution, sampling a small number of blocks might not correctly represent the whole content of a table.”

    And, of course, ADS solves (or, better, tries to solve) this problem in a very different way.

    Best,
    Chris

    Comment by Christian Antognini — June 15, 2015 @ 12:52 pm | Reply

  4. Thanks Christian for your comment

    This case is from a third party software where they have set dynamic_smpling at level 4 at system level.
    I am trying to convince them that they will benefit from upping it to level 6 which, as you’ve mentioned, can enhance
    the CBO estimation and generate henceforth optimal execution plans

    Best regards

    Comment by hourim — June 15, 2015 @ 2:46 pm | Reply

  5. […] between “not sampling when hinted” and “ignoring the sample” – but Mohamed Houri got there before […]

    Pingback by Dynamic Sampling | Oracle Scratchpad — June 15, 2015 @ 8:41 pm | Reply

  6. It seems that the 11.2 Auto-Adjustment of the Dynamic Sampling Level (Support Doc#1102413.1) works only with Parallel Query. Do you know what rules / heuristics Oracle uses there ? (I’ve see this Auto-Adjustment occurring in a couple of PQ cases)

    Comment by Hemant K Chitale — June 16, 2015 @ 6:58 am | Reply

  7. Hi Hermant

    I am sorry for this delay.

    Unfortunately I can’t answer your question. I haven’t been neither confronted to this issue nor I have planned to look at it. I have, however, read 3 articles about 12c Dynamic Sampling
    published in Toad World by Chinar Aliyev:

    http://www.toadworld.com/platforms/oracle/w/wiki/11022.automaticadaptive-dynamic-sampling-in-oracle-12c-part-1.aspx
    http://www.toadworld.com/platforms/oracle/w/wiki/11036.automaticadaptive-dynamic-sampling-in-oracle-12c-part-2.aspx
    http://www.toadworld.com/platforms/oracle/w/wiki/11052.automaticadaptive-dynamic-sampling-in-oracle-12c-part-3.aspx

    and has promessed myself to replay the examples and investigate the new AUTO level (11) but I have still not done that task.

    Best regards
    Mohamed

    Comment by hourim — June 17, 2015 @ 9:00 am | Reply

  8. As usual, very interesting. By the way I particularly like how you present your case and how you give credits to your sources, which is not so common, sadly.

    Comment by francoisgregoire — June 22, 2015 @ 12:18 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: