Mohamed Houri’s Oracle Notes

January 2, 2014

SQL Plan Management: what’s new in 12c

Filed under: Sql Plan Managment — hourim @ 9:25 am

Don’t be disappointed by the title. This is a simple note to show one difference I have noticed in how a stored SPM execution plan is managed when a dependent object is dropped or renamed. To keep it simple, let me start by an existing SPM baseline as shown below

 select sql_text, plan_name
 from dba_sql_plan_baselines
 where signature = '1292784087274697613';

 SQL_TEXT                                            PLAN_NAME
 --------------------------------------------------- ------------------------------
 select count(*), max(col2) from t1 where flag = :n  SQL_PLAN_13w748wknkcwd8576eb1f
 

If I want to display the execution plan of this stored SPM baseline I will proceed as follow:

 select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_13w748wknkcwd8576eb1f'));

 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd8576eb1f         Plan id: 2239163167
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 3625400295
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |     1 |    59 |   124   (2)| 00:00:01 |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

It is a baseline using an index range scan. What happen to that stored execution plan when I drop or rename that index

 drop index i1;

 select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_13w748wknkcwd8576eb1f'));
 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n

 --------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd8576eb1f         Plan id: 2239163167
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 3724264953

 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     1 |    59 |   241   (3)| 00:00:01 |
 |   1 |  SORT AGGREGATE    |      |     1 |    59 |            |          |
 |*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1440K|   241   (3)| 00:00:01 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - filter("FLAG"=:N)
 

The baseline stored execution plan has been automatically updated to reflect the disappeared index.  But wait; this is an 11g behavior. Do the same thing in 12c and you will get a different functioning.

 SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_13w748wknkcwd7823646b'));

 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd7823646b         Plan id: 2015585387
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
 Plan rows: From dictionary
 --------------------------------------------------------------------------------

 Plan hash value: 497086120

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE                      |      |     1 |    30 |     0   (0)|          |
 |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

This time dropping the index in a 12c release will not influence the stored SPM execution plan as shown below:


 SQL> drop index I1;

 SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_13w748wknkcwd7823646b'));

 SQL_PLAN_13w748wknkcwd8576eb1f
 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd7823646b         Plan id: 2015585387
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD

 Plan rows: From dictionary
 --------------------------------------------------------------------------------
 Plan hash value: 497086120

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE                      |      |     1 |    30 |     0   (0)|          |
 |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)
 

The interesting question which motivated this post is: is this change good or bad for our proper use/debug of SPM baseline?

When I was deeply investigation, in the last release, how a baseline is chosen or discarded following a drop of a dependent object, I have already remarked that kind of automatic update of the stored execution plan. I was having two stored baselines one using full table scan and the other one was using the index range scan. In such a situation this index drop let me with the two different stored baselines but this time having both an identical full table scan plan. I knew why, because I originated the two baselines and I provoked that change. But a new developer who might come after me would then question what are those two different baselines having the same execution plan. Would he be able to understand that this situation has been made as such because someone in the past has dropped and index? Not sure.

So, saving definitely the execution plan of the stored SPM baseline as it was at its creation time is a good news in my honest opinion. Why?  Because if you see your query not using the baseline your want it to be used, you will dig into its stored plan and you will remark that this stored plan is using an index named I1. It is then easy to go to that table and verify if the I1 index exists or has been dropped which might immediately explain why your stored baseline is not anymore selected.

Bottom line: I think that the most important thing to remember when looking to the stored SPM execution plan is that, starting from 12c, the displayed execution plan is the one that was available at the baseline captured time. It could be now still available and it might be not anymore reproducible

About these ads

5 Comments »

  1. > The baseline stored execution plan has been automatically updated to reflect the disappeared index
    Perhaps I’m misunderstanding what you’re suggesting but this doesn’t make sense.

    Without further investigation, I’m guessing that you’re showing me a bug/oversight in DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.

    I presume that you had two plans in the baseline already? The index scan and the full?

    How can the baseline plan be automatically updated?

    The baseline is for a specific plan based on the plan id which maps to plan_hash_2 in v$sql_plan.other_xml.
    In 11g, nothing else about the plan other than the hints and this plan_hash_2 is stored.

    SQL_PLAN_13w748wknkcwd8576eb1f should not have the same plan id for both the index range scan and the full scan.

    I believe, but I’ve yet to investigate, that 12c has the actual plan steps as well so I’m guessing it is not affected by the bug.

    So… I wonder what DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE in 11g does behind the scenes for the execution plan?

    Perhaps something nasty like an explain plan using the hints in the baseline which would explain the misinformation?

    Comment by Dom Brooks — January 15, 2014 @ 11:20 am | Reply

  2. Dom,
    Thanks for reading this post and the other one.

    No, I have only one plan in the sql_plan_baseline; it is the plan with the index range scan. Let me present the example

    create table t2
            (col1  number
            ,col2  varchar2(50)
            ,flag  varchar2(2));
    
      insert into t2
            select rownum
            ,      lpad('X',50,'X')
            ,      case when rownum = 1
                        then 'Y1'
                        when rownum = 2
                        then 'Y2'
                        when mod(rownum,2) = 0
                        then 'N1'
                      else 'N2'
                 end
          from   dual
         connect by rownum <= 100000;
    
    create index i1 on t2 (flag);
    
    exec dbms_stats.gather_table_stats(user, 't2');
    
    select count(1) from dba_sql_plan_baselines;
    
      COUNT(1)
    ----------
             0  ---> no baseline
    
    var n varchar2(2)
    exec :n := 'Y1';
    alter session set optimizer_capture_sql_plan_baselines = TRUE;
    
    select count(1) from t2 where flag = :n;
    
      COUNT(1)
    ----------
             1
    select count(1) from t2 where flag = :n;
    
      COUNT(1)
    ----------
             1
    
    alter session set optimizer_capture_sql_plan_baselines = FALSE;
    
    select plan_name from dba_sql_plan_baselines;
    
    PLAN_NAME
    ------------------------------
    SQL_PLAN_3jjzm5fb07xfs3f0011ba   ---> one plan in SPM baseline
    
    SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format => 'ADVANCED'));
    
    --------------------------------------------------------------------------------
    SQL handle: SQL_38c7f32b9603f5d8
    SQL text: select count(1) from t2 where flag = :n
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba         Plan id: 1056969146
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Outline Data from SMB:
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.3')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    --------------------------------------------------------------------------------
    
    Plan hash value: 3900446664 --> note this
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |    13   (8)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
    |*  2 |   INDEX RANGE SCAN| I1   | 25000 | 75000 |    13   (8)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       2 - SEL$1 / T2@SEL$1
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this. Outline Data and Outline from SMB are identical
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.3')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("FLAG"=:N)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - (#keys=0) COUNT(*)[22]
    
    select hash_value, plan_hash_value from v$sql_plan where plan_hash_value = 3900446664; --> the above plan hash value
    
    HASH_VALUE PLAN_HASH_VALUE
    ---------- ---------------
    3335798963      3900446664
    3335798963      3900446664
    3335798963      3900446664
    

    No, I will just drop the index and get the execution plan of the already existing sql_plan_baseline

    drop index i1;
    SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format => 'ADVANCED'));
    --------------------------------------------------------------------------------
    SQL handle: SQL_38c7f32b9603f5d8
    SQL text: select count(1) from t2 where flag = :n
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba         Plan id: 1056969146
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Outline Data from SMB:
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) ---> spot this
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.3')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    --------------------------------------------------------------------------------
    
    Plan hash value: 3321871023
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     3 |   241   (3)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
    |*  2 |   TABLE ACCESS FULL| T2   | 25000 | 75000 |   241   (3)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       2 - SEL$1 / T2@SEL$1
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          FULL(@"SEL$1" "T2"@"SEL$1") --> spot this change from INDEX to FULL
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.3')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("FLAG"=:N)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - (#keys=0) COUNT(*)[22]
    
    65 rows selected.
    
    SQL> select hash_value, plan_hash_value from v$sql_plan where plan_hash_value = 3321871023;
    
    no rows selected 
    

    And now I repeat exactly the same thing in 12c and here below what I have
    In Presence of the index

    SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format=>'ADVANCED'));
    
    --------------------------------------------------------------------------------
    SQL handle: SQL_38c7f32b9603f5d8
    SQL text: select count(1) from t2 where flag = :n
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba         Plan id: 1056969146
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    Plan rows: From dictionary
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Outline Data from SMB:
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG"))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('12.1.0.1')
          OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    --------------------------------------------------------------------------------
    
    Plan hash value: 3900446664
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |    49   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
    |*  2 |   INDEX RANGE SCAN| I1   | 25000 | 75000 |    49   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       2 - SEL$1 / T2@SEL$1
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG"))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('12.1.0.1')
          OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Peeked Binds (identified by position):
    --------------------------------------
    
       1 - :N (VARCHAR2(30), CSID=178): 'Y1'
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("FLAG"=:N)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - (#keys=0) COUNT(*)[22]
    

    In the absence of the index

    SQL> drop index i1;
    
    Index dropped.
    
    select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format=>'ADVANCED'));
    --------------------------------------------------------------------------------
    SQL handle: SQL_38c7f32b9603f5d8
    SQL text: select count(1) from t2 where flag = :n
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba         Plan id: 1056969146
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    Plan rows: From dictionary
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    Outline Data from SMB:
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('12.1.0.1')
          OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    --------------------------------------------------------------------------------
    
    Plan hash value: 3900446664
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |    49   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
    |*  2 |   INDEX RANGE SCAN| I1   | 25000 | 75000 |    49   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       2 - SEL$1 / T2@SEL$1
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this: They are the same in contrast to 11g
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('12.1.0.1')
          OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Peeked Binds (identified by position):
    --------------------------------------
    
       1 - :N (VARCHAR2(30), CSID=178): 'Y1'
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("FLAG"=:N)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - (#keys=0) COUNT(*)[22]
    
    71 rows selected.
    select plan_name, enabled, accepted from dba_sql_plan_baselines;
    
    PLAN_NAME                        ENA ACC
    ------------------------------- ---- ---
    SQL_PLAN_3jjzm5fb07xfs3f0011ba  YES YES   ---> baseline is still accepted 
     

    And if I execute again my initial query here below what I obtain

    SQL> select count(1) from t2 where flag = :n;
      COUNT(1)
    ----------
             1
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    SQL_ID  9zr7y0b3d8h5m, child number 0
    
    An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
    
    NOTE: cannot fetch plan for SQL_ID: 9zr7y0b3d8h5m, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)
    
    
    SQL> select count(1) from t2 where flag = :n;
    
      COUNT(1)
    ----------
             1
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    SQL_ID  9zr7y0b3d8h5m, child number 0
    -------------------------------------
    select count(1) from t2 where flag = :n
    
    Plan hash value: 3321871023
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
    |*  2 |   TABLE ACCESS FULL| T2   | 25000 | 75000 |   273   (1)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("FLAG"=:N)
    

    This is what I wanted to show in this blog. No more no less. Starting from 12c when you want to see the plan of a baseline dbms_xplan.display_plan_baseline will show you the plan as it was at the baseline creation time. While in 11g it will show you the plan as it is (or might be) at the moment you asked to see it.

    Best regards

    Comment by Mohamed — January 16, 2014 @ 2:51 pm | Reply

    • Ignore the conjecture about you having two plans, I’ve since tested it.

      But my point remains.

      Think about it.
      If the baseline was actually changed that defeats the whole purpose of baselines!

      The point is that in 11g, DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE is unable to tell you for definite what the execution plan is in the baseline.
      It only has
      a) The outline hints
      b) The plan id

      If you look at the underlying information in SQLOBJ$, etc you will see this.

      So, all DBMS_XPLAN can do – I’m guessing without a sql trace – is something like apply the hints to the SQL and see what happens.
      It would be great if it then had a little note which said “I couldn’t reproduce the right plan id”.

      The significant change in 12c is that the actual execution plan steps are stored as well.
      So DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can show the plan which it’s meant to reproduce.

      Comment by Dom Brooks — January 16, 2014 @ 3:07 pm | Reply

      • Thanks Dom. This is clear. And this ” So DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can show the plan which it’s meant to reproduce” is cristal clear.

        Comment by Mohamed — January 16, 2014 @ 3:29 pm

  3. I see also that coskan has already blogged about something similar

    http://coskan.wordpress.com/2011/08/13/dbms_xplan-display_sql_plan_baseline-can-also-lie/

    Best regards

    Comment by Mohamed — January 16, 2014 @ 2:56 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

The Rubric Theme. Blog at WordPress.com.

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

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

Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

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

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: