Oracle Scratchpad

November 4, 2013

Outline hassle

Filed under: CBO,Execution plans,Oracle — Jonathan Lewis @ 6:17 pm BST Nov 4,2013

Here’s the output I got from a 10.2.0.5 system after generating a stored outline on a query – then dropping the index that was referenced by the stored outline and creating an alternative index. Spot the problem:


Index dropped.

Index created.

-- run query
-- call to dbms_xplan.display_cursor()

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  bwkujqpbbanq4, child number 0
-------------------------------------
select * from t1 where n1 = :b1 and n1 + n2 = :"SYS_B_0"

Plan hash value: 3299499710

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     1 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   118 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_F |    27 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      OPT_PARAM('optimizer_index_cost_adj' 65)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" "T1_F")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=:B1)
   2 - access("T1"."SYS_NC00004$"=:SYS_B_0)

Note
-----
   - outline "SYS_OUTLINE_13110414022564008" used for this statement

According to the note the optimizer used an outline to optimize this query – which is odd since I’ve done something that made the outline invalid. Perhaps there’s another outline available, so let’s check:

SQL>select * from user_outline_hints;

NAME                                 NODE      STAGE   JOIN_POS HINT
------------------------------ ---------- ---------- ---------- --------------------------------------------------
SYS_OUTLINE_13110414022564008           1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
SYS_OUTLINE_13110414022564008           1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_13110414022564008           1          1          0 FIRST_ROWS(10)
SYS_OUTLINE_13110414022564008           1          1          0 OPT_PARAM('optimizer_dynamic_sampling' 0)
SYS_OUTLINE_13110414022564008           1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_13110414022564008           1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

No. The outline “used” was the outline that specified the now-dropped index.

Is this a bug ? After a few minutes of thinking it was, I decided it probably wasn’t. Technically if you “use” that outline you will get that plan; it’s just bad luck that the outline can’t reproduce the original plan. I suspect that if I read the documentation I will find that this is, indeed, expected behaviour.

SQL Baselines, of course, are much fussier – if a baseline doesn’t reproduce the original plan Oracle is aware of that fact – my initial response to this oddity with outlines was simply applying baseline thinking to old technology.

9 Comments »

  1. Jonathan,

    I have investigated this kind of situations with SQL Baseline and found that the CBO is sometimes doing superfluous work trying to reproduce a non reproducible plan because of an index drop for example (it has to know that it is impossible to reproduce the plan because of the index drop).

    http://hourim.wordpress.com/2013/05/05/spm-baseline-selection-how-it-works/

    We will observe such a kind of trace in the 10053 trace file where the CBO will try to reproduce the plan baseline using outline and the session OFE

    SPM: planId's of plan baseline are: 2239163167 1634389831
    SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
    SPM: plan reproducibility round 0 (plan outline + session OFE)
    SPM: using qksan to reproduce accepted plan, planId = 2239163167 
    

    And curiously when I dropped an index I1 (on which a baseline exists) and replace it with a new index I2, the CBO tried to reproduce the I1 index plan using a hint on index I2!!! Unless I missed something I am unable to understand (a) why the CBO tries to reproduce the I1 index and (b) why it hinted the index I2 when trying to reproduce the Base lined Index I1 plan

    Bottom line: when using SPM baseline to guarantee plan stability, be warn that when you have several enabled and accepted plan for the same SQL matching signature and, if for any reason those plans become non reproducible, you might pay a parsing time penalty because the CBO will use two rounds trying to reproduce all SPM plans – even though they are impossible to reproduce–

    Best regards
    Mohamed Houri

    Comment by Mohamed — November 5, 2013 @ 1:03 pm BST Nov 5,2013 | Reply

    • Mohamed,

      Nice article, thanks for the link.
      I’ve added a little note that MIGHT be a partial explanation of the next bit of the puzzle.

      Comment by Jonathan Lewis — November 8, 2013 @ 9:50 am BST Nov 8,2013 | Reply

  2. It’s not a bug, just a reason why Outlines are SPM 1.0 and Baselines are SPM 3.0.

    The fact that CBO does not “reject” the Outline completely in the same way as it does an SPM with the “reproducible” tests, should be considered very much a threat to anyone still running older versions (outlines being officially deprecated from 11g onwards) and using outlines (a small subset of systems?)

    e.g.

    SQL_ID  abf69rrjxn6v2, child number 0
    -------------------------------------
    select * from   t1 ,      t2 where  t1.flag = t2.flag and    t2.flag = 
    'Y1'
     
    Plan hash value: 352953256
     
    --------------------------------------------------------------------------------------
    | Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |      |       |       |     3 (100)|          |
    |   1 |  MERGE JOIN CARTESIAN         |      |     1 |    86 |     3  (34)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID | T1   |     1 |    43 |     1   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN           | I1   |     1 |       |    49   (0)| 00:00:01 |
    |   4 |   BUFFER SORT                 |      |     1 |    43 |     2  (50)| 00:00:01 |
    |   5 |    TABLE ACCESS BY INDEX ROWID| T2   |     1 |    43 |     1   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN          | I2   |     1 |       |    49   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - access("T1"."FLAG"='Y1')
       6 - access("T2"."FLAG"='Y1')
     
    Note
    -----
       - outline "SYS_OUTLINE_13110711013929222" used for this statement
    
    

    Then

    alter index i1 invisible;
    alter index i2 invisible;
    
    SQL_ID  abf69rrjxn6v2, child number 0
    -------------------------------------
    select * from   t1 ,      t2 where  t1.flag = t2.flag and    t2.flag = 
    'Y1'
     
    Plan hash value: 787647388
     
    -----------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |       |       |   447 (100)|          |
    |   1 |  MERGE JOIN CARTESIAN|      |   667K|    54M|   447   (2)| 00:00:06 |
    |*  2 |   TABLE ACCESS FULL  | T1   |   817 | 35131 |   221   (1)| 00:00:03 |
    |   3 |   BUFFER SORT        |      |   817 | 35131 |   225   (3)| 00:00:03 |
    |*  4 |    TABLE ACCESS FULL | T2   |   817 | 35131 |   221   (1)| 00:00:03 |
    -----------------------------------------------------------------------------
     
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("T1"."FLAG"='Y1')
       4 - filter("T2"."FLAG"='Y1')
     
    Note
    -----
       - outline "SYS_OUTLINE_13110711013929222" used for this statement
    
    

    Comment by Dom Brooks — November 7, 2013 @ 11:17 am BST Nov 7,2013 | Reply

    • Dom,

      Okay – you’ve hooked me, now reel me in: What happened to SPM 2.0 ? Is that the 11g version, with 12c being the 3.0 ?

      I actually started writing this note with the title “Outline Bug” and included it in the category Bug – it wasn’t until I remembered that the outln tables didn’t hold any information about the expected execution plan that I realised that “using” an outline didn’t have to mean you got the same execution plan as last time.

      I think the same is true for SQL Baselines (in a few cases) until 12c where the actual execution plan is stored as part of the SQL Baseline (hence my 2.0 comment above).

      Comment by Jonathan Lewis — November 8, 2013 @ 9:59 am BST Nov 8,2013 | Reply

      • I wasn’t trying to hook you… I was thinking that SQL Profiles were SPM 2.0. And some people would argue that they prefer using 2.0 to 3.0 when using COE-style sql profiles.

        11g SQL Baselines at least have the plan_hash_2 to be able to enforce the plan reproducibility.

        I’ve not even started looking at 12c properly yet – shame on me – but one of the first things I want to look at will be SPM changes.

        Comment by Dom Brooks — November 8, 2013 @ 3:03 pm BST Nov 8,2013 | Reply

  3. Hi Jonathan,

    I have one doubt on functionality of outline. Say in my query one table was doing FTS and with outline I’m forcing sql to pick right index. Now in case of data change is it required to make changes in outline. In my opinion it should pick same plan in any data change. Can you please shed your view in this?

    Regards,
    Avi

    Comment by Avi — February 25, 2014 @ 3:07 pm BST Feb 25,2014 | Reply

    • Avi,

      On a simple data change I wouldn’t normally expect the execution plan to change with an outline in place. However (a) outlines are not perfectly implemented and (b) “data” change may also mean the statistics change or the queries change.

      If you’ve changed the data without updating the index it’s possible that queries targetting the new data are now using “out of range” values, which make them subject to the “linear decay” problem. This could have such a significant effect on the estimated cardinalities that an imperfect outline could allow Oracle to find a new execution plan while following all the (legal) hints in the outline.

      If you’ve changed the data and updated the stats it’s possible that the new stats have some other effect on the cardinality calculations that also allow Oracle to obey the (legal) hints in the outline without reproducing the original plan.

      I don’t think I have a convenient example to hand, but there are various hints that don’t get into an outline, and if you’ve got a plan which (for example) assumes that a “push_subq” has occurred but hasn’t captured that as a directive through a hint then a change in cardinality estimates may leave the optimizer deciding to postpone the subquery which, as a side effect, results in a plan that has to ignore the index hint because it has either become illegal or out of context.

      As a quick sanity check, use dbms_xplan.display_cursor() with the ‘outline’ option to see what the stored outline for the new plan is, and you may find that it appears to be obeying almost all the hints in the stored outline, and you may find a reason why some hint is apparently being ignored.

      Comment by Jonathan Lewis — February 25, 2014 @ 6:18 pm BST Feb 25,2014 | Reply

      • Hi Jonathan,

        Thanks a lot for your time. What I wanted to ask, if a table has many distinct values and with outline it is working fine, i.e., picking index. Now I changed these values to less distinct values for some time (due to some business requirement) and my query is selecting a particular value which is now more than 90% total rows in table. Ideally, for this value FTS will be good and for rest other values index scan will be good. I take latest stats this time for table and indexes. Now having a outline in my system, will my sql go for index scan for this value too or it will choose to ignore outline for this value?

        Regards,
        Avi

        Comment by Avi — February 27, 2014 @ 1:03 pm BST Feb 27,2014 | Reply

        • Avi,

          As I indicated in my previous reply – in principle the plan ought to stay the same, but in practice the outline technology is not perfect, so you could find that a sufficiently large change in the statistics manages to make the optimizer find a different path that is still obeying all the (legal) hints that have been captured in the stored outline.

          Comment by Jonathan Lewis — February 27, 2014 @ 7:34 pm BST Feb 27,2014


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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,013 other followers