Mohamed Houri’s Oracle Notes

January 2, 2014

SQL Plan Management and indexes Part I: What matters? The index name or the indexed columns?

Filed under: Sql Plan Managment — hourim @ 1:09 pm

Here’s a point that I wanted to investigate since several months. Hopefully time comes when I deigned digging into it. The issue could be stated as follows: If I have an accepted SQL plan baseline using a given index, is this SPM reproducibility depending on that index name or on its indexed columns?

Let’s see

As always when blogging about SPM I use the same model and the same SPM baseline

  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
  

The following select will use the above stored Baseline as mentioned by the Note at the end of the execution plan

  SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)

 ---------- --------------------------------------------------

 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 3625400295

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   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)

 Note
 -----
 - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement

 

The most important object in this baseline is the index I1 which has been defined with a single column as shown below:

 SQL> select table_name, index_name,column_name, column_position
 from user_ind_columns
 where table_name = 'T1';

 TABLE_NAME   INDEX_NAME  COLUMN_NAME  COLUMN_POSITION
 ------------ ----------- ------------ ---------------
 T1           I1         FLAG           1
 

Let’s first rename it and see the consequences

 SQL> alter index i1 rename to i2;
 Index altered.

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n

 Plan hash value: 718843153

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

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

The SQL plan baseline has not been used thanks to this index name change. The index name matters.

Let’s now go back to the original I1 index but this time I will add to it an extra column

 SQL> drop index i1;

 SQL> create index i1 on t1(flag,col1);

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 3625400295

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   130 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   130   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    19   (6)| 00:00:01 |
 -------------------------------------------------------------------------------------

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

 Note
 -----
 - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
 

The SQL plan baseline has been used in this situation (a) the new index has the same name as the index name in the stored SPM plan i.e. I1 (b) and the new index has an extra column but it is still starting with the same column as the index originally used during the baseline capture.

Finally let’s change the index columns order and use the same index name.

 SQL> drop index i1;

 SQL> create index i1 on t1(col1,flag);

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL> select count(*), max(col2) from t1 where flag = :n;

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 SQL> select * from table(dbms_xplan.display_cursor);

 SQL_ID  731b98a8u0knf, child number 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 2289867667

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   123 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   123   (2)| 00:00:01 |
 |*  3 |    INDEX SKIP SCAN           | I1   | 25000 |       |    67   (2)| 00:00:01 |
 -------------------------------------------------------------------------------------

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

Look now how it is the indexed column position that matters in this situation. The SPM baseline is not anymore used because the leading column of the I1 index (col1) is not the same leading column of the I1 index (flag) when compared to the index definition at the baseline capture time.

Bottom line: Baseline selection depends on both the index name and its leading columns as it was during the baseline capture.

  1. If you change the index name, whatever the index definition will be, the baseline will not be selected
  2. If you keep the index name and you change the leading column (s) of the index, the baseline will not be selected
  3. If you keep the index name and you add an extra column at the trailing edge of the index the baseline might be selected

When we are in the third case above, the clustering factor of the I1 index might change to a bad value making the index cost bigger than the full table scan cost. In such a situation, choosing to use the SPM baseline might not be the good choice.  This is why it is nice to have an extra accepted baseline that foresee an extra plan (full table scan for example)  in order to not see the CBO plan constrained by this index SPM baseline.

PS : I might have been very quick making the above conclusions. I will update this post if needed when I will investigate a little bit further or when comments will introduce doubts on what I have stated above

About these ads

4 Comments »

  1. Hi Mohamed,

    In baselines, the index hints that I’ve seen have always been the INDEX_RS_ASC format using column position not index name so this would suggest that an index name change will not affect the usage of a baseline but that adding columns or changing column order would.

    However … if we then take into account the plan_id enforcement mechanism (which maps to plan_hash_2 in v$sql_plan.other_xml) then this would account for why the baseline is not used as we get a different PHV2 for the two different indexes.

    Cheers,
    Dominic

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

  2. Dom,

    Not only changing columns order would affect the usage of the baseline but also the change of the type of index would affect the baseline too. I have created a baseline using a global non prefixed index on a range partitioned table. Then i dropped that index and re-create it with:

    (a) the same name
    (b) the same column
    (c) but local.

    The result is that the baseline ceases to be used

    Best regards

    Comment by Mohamed — January 16, 2014 @ 4:14 pm | Reply

    • Quite – anything which means that the baselined plan id has changed means that the baseline is rejected.

      I don’t think I’ve ever seen documented the workings behind plan_hash_2 but it’s interesting to note that changes in index name change this phv2 but, for example, different recursive temp table names for materialised subqueries give a different phv but not phv2.

      Comment by Dom Brooks — January 16, 2014 @ 4:44 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

Theme: Rubric. Get a free blog at WordPress.com

Oracle Diagnostician

Performance troubleshooting as exact science

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

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

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 104 other followers

%d bloggers like this: