Oracle Scratchpad

January 5, 2015

Re-optimization

Filed under: 12c,Oracle,Performance — Jonathan Lewis @ 12:54 pm GMT Jan 5,2015

The spelling is with a Z rather than an S because it’s an Oracle thing.

Tim Hall has just published a set of notes on Adaptive Query Optimization, so I thought I’d throw in one extra little detail.

When the optimizer decides that a query execution plan involves some guesswork the run-time engine can monitor the execution of the query and collect some information that may allow the optimizer to produce a better execution plan. The interaction between all the re-optimization mechanisms can get very messy, so I’m not going to try to cover all the possibilities – read Tim’s notes for that – but one of the ways in which this type of information can be kept is now visible in a dynamic performance view.


SQL> select hash_value, sql_id, child_number, hint_text from V$sql_reoptimization_hints;

HASH_VALUE SQL_ID        CHILD_NUMBER HINT_TEXT
---------- ------------- ------------ ----------------------------------------------------------------
3680288808 d5k20s7dpth18            0 OPT_ESTIMATE (@"SEL$6E65FD6A" GROUP_BY ROWS=28.000000 )

1 row selected.

SQL> select sql_id, child_number, is_reoptimizable, sql_text from v$sql where sql_id = 'd5k20s7dpth18';

SQL_ID        CHILD_NUMBER I SQL_TEXT
------------- ------------ - --------------------------------------------------
d5k20s7dpth18            0 Y select * from V$OPTIMIZER_PROCESSING_RATE
d5k20s7dpth18            1 N select * from V$OPTIMIZER_PROCESSING_RATE

2 rows selected.

To get the results above I had flushed the shared pool and then (using the SYS schema) executed the indicated query twice. Pulling the execution plans from memory I got (with a little cosmetic cleaning) these results:


SQL> select * from table(dbms_xplan.display_cursor('d5k20s7dpth18',null));

SQL_ID  d5k20s7dpth18, child number 0
-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |       |       |     1 (100)|          |
|   1 |  VIEW                | GV$OPTIMIZER_PROCESSING_RATE |    28 |  2072 |     1 (100)| 00:00:01 |
|   2 |   HASH GROUP BY PIVOT|                              |    28 |  1568 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FULL  | X$OPTIM_CALIB_STATS          |    28 |  1568 |     0   (0)|          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("INST_ID"=USERENV('INSTANCE'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL_ID  d5k20s7dpth18, child number 1
-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |       |       |     1 (100)|          |
|   1 |  VIEW                | GV$OPTIMIZER_PROCESSING_RATE |    28 |  2072 |     1 (100)| 00:00:01 |
|   2 |   HASH GROUP BY PIVOT|                              |    28 |  1568 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FULL  | X$OPTIM_CALIB_STATS          |    28 |  1568 |     0   (0)|          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("INST_ID"=USERENV('INSTANCE'))

Note
-----
   - statistics feedback used for this statement

The Note section for child zero tells us that we used “dynamic statistics” (and the other_xml column in v$sql_plan still calls it dynamic sampling) as part of the statement optimization; but the Note section for child one tells us that we used “statistics feedback” (and the other_xml column in v$sql_plan still calls it cardinality feedback) – in other words we didn’t do dynamic sampling, we re-used the sample (saved as that in-memory opt_estimate hint) from the previous child.

There’s no great significance in the SQL statement I chose to demonstrate this point – I just happened to be doing some investigation into the Optimizer Processing Rates when I paused to read Tim’s notes, so I based this post on one of the statements I had run a few minutes before.

11 Comments »

  1. Jonathan,
    thank you for the information on v$sql_reoptimization_hints – this seems to be an answer to a question I asked recently on Oracle-L (http://www.freelists.org/post/oracle-l/OPTIMIZER-DYNAMIC-SAMPLING-Level-11): is there an interface to see the persisted information from dynamic statistics?

    But still the result of your test is a surprise to me – though I can reproduce it with sys and V$OPTIMIZER_PROCESSING_RATE: since I used something similar (or at I least I think it’s similar) to check the reuse of dynamic statistics:

    -- in a pdb in 12.1.0.2
    drop table t;
    
    create table t
    as
    select rownum id
         , case when rownum <= 100 then 1 else 0 end col1
      , lpad('*', 50, '*') padding
      from dual  
    connect by level <= 100000;  
    
    create index t_idx on t(col1);
    
    -- force sampling (by explicit order or missing statistics)
    -- alter session set OPTIMIZER_DYNAMIC_SAMPLING = 11;
    exec dbms_stats.delete_table_stats(user, 'T')
    
    alter system flush shared_pool;
    
    -- query execution
    select count(*) from t where col1 = 1;
    
    -- repeated query execution
    select count(*) from t where col1 = 1;
    
    select * from table(dbms_xplan.display_cursor('ctkftgtjq8v7z',null));
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    SQL_ID  ctkftgtjq8v7z, child number 0
    -------------------------------------
    select count(*) from t where col1 = 1
    
    Plan hash value: 293504097
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
    |   1 |  SORT AGGREGATE   |       |     1 |    13 |            |          |
    |*  2 |   INDEX RANGE SCAN| T_IDX |   100 |  1300 |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("COL1"=1)
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    

    In my example there is only one child cursor and (as far as I can see) no sign of persisted information – of course X$ memory structures are something different than tables, but do you see other differences I am missing?

    Comment by Martin Preiss — January 5, 2015 @ 6:09 pm GMT Jan 5,2015 | Reply

    • sorry – obviously I just wrote nonsense: my initial example flushed the shared pool between the executions to check if there is something persisted. Without the flushing the cursor is just reused.

      Comment by Martin Preiss — January 5, 2015 @ 6:32 pm GMT Jan 5,2015 | Reply

      • Martin,

        You’ve answered your own question.

        Just to add some detail: the optimizer needs a reason to decide that the first execution plan was bad; in your case with no stats is had to sample before optimizing, so “knew” it didn’t need to re-optimize to re-execute.

        I re-used your example, leaving the stats in place (no delete_table_stats) and the optimizer_dynamic_sampling at 2, but changing the query to make the estimates questionable:

        select count(padding) from t where col1 = 0 and sign(col1) != 1;
        select count(padding) from t where col1 = 0 and sign(col1) != 1;
        select * from table(dbms_xplan.display_cursor('a0wqpdz7d9r7n',null));
        

        The optimizer wouldn’t sample BEFORE optimising unless the optimizer_dynamic_sampling was set to 3 or above. This resulted in re-optimisation (as the optimizer guessed the effect of the second predicate and used cardinality feedback after the event).

        The inital plan was an index range scan with estimated cardinality of 2,500 (index and table), but re-optimisation occurred the second child produced a tablescan with estimated cardinality of 99,900; with a note section saying: ” – statistics feedback used for this statement”.

        Here’s what appeared in v$sql_reoptimization_hints:

        
        HASH_VALUE SQL_ID        CHILD_NUMBER    HINT_ID HINT_TEXT
        ---------- ------------- ------------ ---------- --------------------------------------------------------------------------------
        3470056692 a0wqpdz7d9r7n            0          1 OPT_ESTIMATE (@"SEL$1" TABLE "T"@"SEL$1" ROWS=99900.000000 )
        3470056692 a0wqpdz7d9r7n            0          2 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "T"@"SEL$1" "T_IDX" ROWS=99900.000000 )
        3470056692 a0wqpdz7d9r7n            0          3 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "T"@"SEL$1" "T_IDX" MIN=99900.000000 )
        
        

        Comment by Jonathan Lewis — January 9, 2015 @ 11:03 am GMT Jan 9,2015 | Reply

        • Jonathan,
          thank you for the additional details. So in this case the (relatively) complex additional predicate “and sign(col1) != 1” makes the optimizer aware of potential problems and triggers a monitoring of the execution statistics that are added as reoptimization hints into v$sql_reoptimization_hints after the (first) execution of the query?

          Comment by Martin Preiss — January 10, 2015 @ 4:16 pm GMT Jan 10,2015

        • Martin,

          I don’t think I want to make any very strict statements since I have looked closely at what does and doesn’t trigger the population of this view; but bear in mind that optimizer_dynamic_sampling = 3 triggers sampling when Oracle has to “guess”, e.g. where you have “function(column) = const”, and optimizer_dynamic_sampling = 4 triggers sampling when you have two predicates on the same table and Oracle has no information about correlation effects.

          Possibly this view is populated when a higher level for optimizer_dynamic_sampling would have cause the optimizer to sample before generating an execution plan.

          Comment by Jonathan Lewis — January 14, 2015 @ 2:35 pm GMT Jan 14,2015

        • thanks, again. This seems to be an area in which a lot of testing and research could be done – but I guess the interferences between the different features would make it very difficult to get definite results – if possible at all.

          Another difficult area seems to be the comment handling in wordpress – so I add this answer just somewhere…

          Comment by Martin Preiss — January 14, 2015 @ 6:07 pm GMT Jan 14,2015

  2. The dynamic performance view V$sql_reoptimization_hints gets populated only if dynamic sampling is performed as part of re-optimization. If dynamic sampling is performed as a result of setting the parameter OPTIMIZER_DYNAMIC_SAMPLING to 11, sampling information is not preserved.

    Comment by Anju Garg — January 8, 2015 @ 12:21 pm GMT Jan 8,2015 | Reply

    • Anju,

      Thanks for the comment. I think it captures a significant point about the view and the requirement for re-optimization to be indicated, but I’m not sure that it’s entirely accurate.

      Admittedly my initial example was against an X$ which didn’t have stats collected and included a group by, either of which may point to a special case, but v$sql_reoptimization_hints was populated during production of the first execution plan, not on the second (checked by a separate test).

      Comment by Jonathan Lewis — January 9, 2015 @ 11:13 am GMT Jan 9,2015 | Reply

  3. Respected Sir,

    I reproduced Martin’s example with a few modifications. After second execution, I flushed out the cursor
    from shared pool. It caused dynamic sampling info stored in V$SQL_REOPTIMIZATION_HINTS to be aged out too.

    drop table t;
     
    create table t
    as
    select rownum id
         , case when rownum &lt;= 100 then 1 else 0 end col1
      , lpad('*', 50, '*') padding
      from dual  
    connect by level  'ALLSTATS LAST'));
    
    
    PLAN_TABLE_OUTPUT
    -----------------------------
    SQL_ID  3bz4jn2vftxc9, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ count(padding) from t where col1 =
    0 and sign(col1) != 1
    
    Plan hash value: 1339972470
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |       |      1 |        |      1 |00:00:00.06 |    1057 |
    |   1 |  SORT AGGREGATE                      |       |      1 |      1 |      1 |00:00:00.06 |    1057 |
    |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |  88958 |  99900 |00:00:00.18 |    1057 |
    |*  3 |    INDEX RANGE SCAN                  | T_IDX |      1 |   4448 |  99900 |00:00:00.07 |     182 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("COL1"=0)
           filter(SIGN("COL1")1)
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    
    -- chk that dynamic sampling info preserved in v$sql_reoptimization_hints
    
    SQL> select hash_value, sql_id, child_number, hint_text from V$sql_reoptimization_hints where sql_id = '3bz4jn2vftxc9';
    
    HASH_VALUE SQL_ID        CHILD_NUMBER HINT_TEXT
    ---------- ------------- ------------ ----------------------------------------------------------------------------
    3068982665 3bz4jn2vftxc9            0 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "T"@"SEL$1" "T_IDX" ROWS=99900.000000 )
    3068982665 3bz4jn2vftxc9            0 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "T"@"SEL$1" "T_IDX" MIN=99900.000000 )
    
    
    --    Execute the statement again while cursor is in cache
    --    stats feedback used - could have used execution stats
    --    stored in the cursor or sampling stats stored in V$sql_reoptimization_hints 
    
    select /*+ gather_plan_statistics */ count(padding) from t where col1 = 0 and sign(col1) != 1;
    
    select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));
    
    PLAN_TABLE_OUTPUT
    -----------------
    SQL_ID  3bz4jn2vftxc9, child number 1
    -------------------------------------
    select /*+ gather_plan_statistics */ count(padding) from t where col1 =
    0 and sign(col1) != 1
    
    Plan hash value: 2966233522
    
    -------------------------------------------------------------------------------------
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.03 |    880 |
    |   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |    880 |
    |*  2 |   TABLE ACCESS FULL| T    |      1 |     36 |  99900 |00:00:00.07 |    880 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(("COL1"=0 AND SIGN("COL1")1))
    
    Note
    -----
       - statistics feedback used for this statement
    
    
    24 rows selected.
    
    -- Flush statement only out of shared pool
    
    SQL>select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = '3bz4jn2vftxc9';
    
    
    ADDRESS          HASH_VALUE
    ---------------- ----------
    0000000067F9F2F8 3068982665
    
    exec DBMS_SHARED_POOL.PURGE ('0000000067F9F2F8, 3068982665', 'C');
    
    -- chk that cursor no longer cached
    
      col sql_text for a45
          select sql_id, sql_text from v$sql 
          where sql_id = '3bz4jn2vftxc9';
    
    -- no rows selected
    
    -- chk that reoptimizn hint ages out along with the cursor
    
    select hash_value, sql_id, child_number, hint_text from V$sql_reoptimization_hints where sql_id = '3bz4jn2vftxc9';
    
    no rows selected
    
    

    Doubts:
    – How do we know that second execution used dynamic sampling stats stored in v$sql_reoptimization_hints . It could have used execution stats stored in the cursor itself.

    – If dynamic sampling info ages out along with the cursor, what is the purpose of storing it? Subsequent executions can use execution stats stored in the cursor only.

    Regards
    Anju Garg

    Comment by Anju Garg — January 12, 2015 @ 5:31 am GMT Jan 12,2015 | Reply

    • Anju,

      1 – I think we’d have to do some experiments with more complex queries to get a better idea of the details of how this works. v$sql_reoptimization_hints seems to be tied closely to a child cursor, so perhaps it is just a way of presenting the rowsource execution statistics for that cursor, but possibly there’s an separate structure which holds the rowsource execution statistics for just those operations that the optimizer felt it needed to monitor, rather than all of them.

      If it was reusing the execution stats we might expect to see those stats visible in view v$sql_plan_statistics even when we hadn’t enabled stats collection – and that didn’t happen in a quick test I’ve just run. (We could start dumping some library cache objects to see if we find that duplicaiton, I suppose.)

      2 – The whole question of “which feature helps in which cases on what type of system at what cost” is messy, and Oracle Corp. seems to be evolving towards solutions based on market feedback rather than having a complete pre-determined set of targets; so I think we shouldn’t be too surprised if we think that some of the options don’t look completely consistent, or if we find it hard to understand the mix-and-match thoughts that the Oracle developer may have had when constructing the feature.

      Since I don’t have access to design decisions and code specs I can’t answer your question with anything other than guesses, but here’s one thought – if the sampling information is associated with the (discarded) child cursor after it has been used to generate a new (more efficient) cursor, isn’t it possible for the discarded child cursor to be aged out of the cursor cache while the new child persists ? It’s only if the same statement HAS to be re-optimized that we would have to worry about the original child cursor being lost.

      Comment by Jonathan Lewis — January 14, 2015 @ 3:40 pm GMT Jan 14,2015 | Reply

  4. […] is indeed already resolved. Using Jonathan Lewis’s recent notes on reoptimisation I checked what the optimiser worked […]

    Pingback by Adaptive plans and v$sql_plan and related views « Martins Blog — January 13, 2015 @ 10:14 am GMT Jan 13,2015 | 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.