Mohamed Houri’s Oracle Notes

May 5, 2013

SPM baseline selection: how it works?

Filed under: Sql Plan Managment — hourim @ 4:20 pm

In my last post about SQL Plan Management (SPM) I investigated the behavior of Adaptive Cursor Sharing (ACS) feature in the presence of SPM baselines. I will now start focusing my interests on the interaction between the CBO and the SPM plan selection steps using the model of the last post.  During this entire blog article I will be working with an existing SPM baseline which contains two accepted and enabled plans as shown below:

 SIGNATURE            SQL_HANDLE               PLAN_NAME                      ENA ACC  PLAN_ID    DESCRIPTION
 -------------------- ------------------------ ------------------------------ --- --- ---------- -------------------
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES 1634389831 FULL TABLE SCAN(T1)
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES 2239163167 INDEX RANGE SCAN(i1)
 

And I will try to investigate the following issues

  1. SPM contains two plans (1634389831, 2239163167) and CBO comes up with a plan that matches one of these existing SPM plans
  2. SPM contains two plans (1634389831, 2239163167) and I drop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the bind variable value that favor the index range scan
  3. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline.
  4. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline but I drop also the existing index i1 (making the plan 2239163167 not anymore reproducible)

In order to follow my investigation without a lot of difficulties, please would you mind to remember that plan id finishing by 831 (1634389831) corresponds to T1 Table FULL SCAN while the plan id finishing by 167 (2239163167) corresponds to the index i1 RANGE SCAN.

Let’s now embark in the investigations starting by case number 1.

Case1: SPM contains two plans and CBO comes up with a plan that matches one of these existing SPM plans

I executed my query using bind variable = ‘Y1’ which is the bind variable that favor the i1 index range scan. The CBO should come up with the index i1 range scan plan matching the existing plan in the baseline (2239163167). Let’s see how the selection occurs via the 10053 trace file

 SPM: statement found in SMB

The first thing the CBO does is signaling that it realizes the presence of a SPM baseline. Then, follows the classical CBO query optimization

 ****************
 QUERY BLOCK TEXT
 ****************
 select count(*), max(col2) from t1 where flag = :n

 Access path analysis for T1
 ***************************************
 SINGLE TABLE ACCESS PATH
 Single Table Cardinality Estimation for T1[T1]

 Table: T1  Alias: T1
 Card: Original: 100000.000000  Rounded: 9  Computed: 9.10  Non Adjusted: 9.10
 Access Path: TableScan
 Cost:  275.38  Resp: 275.38  Degree: 0
 Cost_io: 272.00  Cost_cpu: 31121440
 Resp_io: 272.00  Resp_cpu: 31121440

 Access Path: index (AllEqRange)
 Index: I1
 resc_io: 2.00  resc_cpu: 18993
 ix_sel: 0.000091  ix_sel_with_filters: 0.000091
 Cost: 2.00  Resp: 2.00  Degree: 1
 Best:: AccessPath: IndexRange                 ---> Best Access Path Index I1 Range Scan with cost =2
 Index: I1
 Cost: 2.00
 ***************************************
 

Where the CBO found that the best access path is the Index Range scan (Index I1 with a cost of 2). However, as far as the CBO has already signaled the presence of SPM plan it knows that it is constrained. It can’t decide to use the plan it comes up with without comparing it to the existing SPM plans. This is why we see the following lines into the same 10053 trace file

 SPM: cost-based plan found in the plan baseline, planId = 2239163167 ---> 167 is the index range scan
 SPM: cost-based plan was successfully matched, planId = 2239163167 --> CBO comes up with a plan that matches a SPM plan
 

That’s all for this case:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case2: SPM contains two plans but Idrop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the index bind variable value

The 10053 trace file in this case looks as follows

 SPM: statement found in SMB

****************
QUERY BLOCK TEXT
****************
select count(*), max(col2) from t1 where flag = :n

Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 1  Computed: 0.50  Non Adjusted: 0.50
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Best:: AccessPath: TableScan        ---> CBO comes up with a FULL TABLE SCAN plan having cost = 273
Cost: 272.96
***************************************

In the absence of the i1 index, the CBO produces a FULL TABLE SCAN as the best access path. As far as this generated plan exists in the baseline it will be used as shown below:

SPM: cost-based plan found in the plan baseline, planId = 1634389831 ---> T1 FULL TABLE SCAN plan
SPM: cost-based plan was successfully matched, planId = 1634389831 --> CBO comes up with a plan that matches a SPM plan

That’s all for this case also:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case 3: SPM contains two plans and I add a new index i2 that will produce a new plan which is not in the SPM baseline

In this case I executed my query with a bind variable that favors a fast full scan of the newly created index i2.  The CBO comes up with a plan that doesn’t match any plan in the SPM baseline. This is confirmed here below:

 Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 50563  Computed: 50563.46  Non Adjusted: 50563.46
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Access Path: index (index (FFS))
Index: I2
resc_io: 44.00  resc_cpu: 18103823
ix_sel: 0.000000  ix_sel_with_filters: 1.000000

Access Path: index (FFS)
Cost:  44.56  Resp: 44.56  Degree: 1
Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (AllEqRange)
Index: I1
resc_io: 995.00  resc_cpu: 26806643
ix_sel: 0.505635  ix_sel_with_filters: 0.505635
Cost: 995.83  Resp: 995.83  Degree: 1
******** End index join costing ********

Best:: AccessPath: IndexFFS    ---> I2 Index FFS of cost 44 is the best access path
Index: I2
Cost: 44.56
***************************************

The CBO comes up with an INDEX FAST FULL SCAN on the newly created index i2. Sure this will not match an existing plan baseline because we keep repeating that we have only two enabled and accepted plan baselines one for index i1 range scan and the other one for t1 table full scan. It is then very interesting to see how the CBO will react in such a situation.

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 --> I1 INDEX RANGE SCAN (167)

Since the CBO realizes that it has produced a non matching plan,  It will try to reproduce the existing SPM plan baseline using outline hint and session OFE.

Why the CBO tries to reproduce one of the existing SPM plan?  And why the CBO started by trying to reproduce the index range scan plan first? Why not simply use one of the existing SPM plan?

When the CBO comes up with a plan that is not in the SPM baseline, it considers that something has changed and it is not anymore sure that the existing SPM plans are still reproducible. It also considers that even if the SPM plans are reproducible their corresponding “stored” cost might have changed. This is why, as will we see later, the CBO will not only tries to reproduce the I1 index range scan plan but it will also try to reproduce the full table scan plan in order to compare their costs using current optimizer parameters and table/index statistics. If the two plans are reproducible, then the one having the best current re-computed cost will be used.

We can see this in the 10053 trace file

SELECT /*+ INDEX_RS_ASC ("T1" "I1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MHOURI"."T1" "T1" WHERE "T1"."FLAG"=:B1

Spot how the CBO is injecting the I1 index range scan hint in order to reproduce the SPM index i1 range scan plan

Access path analysis for T1

***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 25000  Computed: 25000.00  Non Adjusted: 25000.00

Access Path: index (AllEqRange)
Index: I1
resc_io: 492.00  resc_cpu: 13254598
ix_sel: 0.250000  ix_sel_with_filters: 0.250000
Cost: 492.41  Resp: 492.41  Degree: 1

Best:: AccessPath: IndexRange
Index: I1
Cost: 492.41  Degree: 1  Resp: 492.41  Card: 25000.00  Bytes: 0
***************************************

SPM: planId in plan baseline = 2239163167, planId of reproduced plan = 2239163167 ---> INDEX_RS plan reproduced
SPM: best cost so far = 492.41, current accepted plan cost = 492.409691           ---> cost = 492
***************************************

Since it has successfully reproduced the I1 index range scan plan and recomputed its corresponding cost, the second step, as indicated above, will be to reproduce the full table scan plan (1634389831) and it corresponding cost

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831  ---> T1 FULL TABLE SCAN (831)

CBO succeed to reproduce the FULL table scan using the plan outline hint and calculated the “new” cost to be the 272 as shown below:

SPM: planId in plan baseline = 1634389831, planId of reproduced plan = 1634389831
SPM: best cost so far = 272.96, current accepted plan cost = 272.961944

So far, the CBO succeeded to reproduce the two SPM plans baseline and to calculate their corresponding cost. It found that the FULL TABLE SCAN cost (272) is better than the cost of the I1 INDEX RANGE SCAN (492). As such, it has decide to use the SPM FULL TABLE SCAN plan.

The above preceding 10053 trace file lines show clearly  how accepted SPM plans enter in competition when the generated CBO plan is not in the SPM baseline. The CBO doesn’t rely on the cost of the plan stored in the baseline. It has to reproduce all enabled and accepted plans and compares their costs using the current session CBO parameters. 

Now that the CBO succeeded to reproduce both plans and decide to use the FULL TABLE SCAN which is the plan with the smaller cost, there is bizarrely, a supplementary step that consist of re-parsing to generate the best costed reproduced plan i.e. the T1 FULL TABLE SCAN as shown below:

SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan, planId = 1634389831

Frankly speaking I didn’t understand the need of this last re-parsing step.

That’s all for this case also:  when the cost-based generated plan doesn’t match one of the existing SPM plans, the CBO will reproduce all the SPM plans and compare their cost. The reproduced plan having the best cost will be used.

Case4: SPM contains two plans and I added a new index i2 that will produce a new plan but I also dropped the existing index i1 (making the plan 2239163167 not anymore reproducible)

In this case, I dropped the i1 (flag) index and created a new index i2 (flag, col2) and executed the query with a bind variable that usually was favoring the INDEX i1 RANGE SCAN. Before exploring the corresponding 10053 trace file, let me tell you that the CBO in this configuration will comes up with a new plan using i2 INDEX RANGE SCAN which doesn’t exist in the SPM baseline as shown below:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
kxsbbbfp=0d019fe0  bln=32  avl=02  flg=05
value="Y1"   -----> this bind value was favoring I1 INDEX RANGE SCAN before I dropped this index
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Table: T1  Alias: T1
Access Path: TableScan                     ----> Full table scan ----> cost 272
Cost:    275.38  Resp: 275.38  Degree: 0
Cost_io: 272.00  Cost_cpu: 31121440
Resp_io: 272.00  Resp_cpu: 31121440

Access Path: index (index (FFS))           ----> I2 index fast full scan ----> cost 44
Index: I2
Access Path: index (FFS)
Cost:  45.97

Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (Index Only)           ----> I2 index range scan  ---> cost 3
Index: I2
resc_io: 3.00  resc_cpu: 21564
ix_sel: 0.000005  ix_sel_with_filters: 0.000005
Cost: 3.00  Resp: 3.00  Degree: 1

Best: Access Path: Index Range            ----> best access path index range scan I2
Index: I2
Cost: 3.00
***************************************

Now that the CBO comes up with a non matching plan, as always, it will start trying to reproduce all  SPM plans. But this time, despite the index i1 range scan plan is not reproducible because I dropped that i1 index, the CBO will, nevertheless, try to reproduce this plan as shown below:

SPM: plan Ids of plan baseline are: 1634389831 2239163167
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, plan Id = 1634389831

During the first round, CBO is trying to reproduce the full table scan

SPM: plan Id in plan baseline = 1634389831, plan Id of reproduced plan = 1634389831
SPM: best cost so far = 275.38, current accepted plan cost = 275.379078

I will skip the  part showing  the FULL TABLE SCAN reproduction because the CBO has successfully reproduced it and there is no added value to present it here.

The next step in this round is to reproduce the I1 index range scan which is in fact impossible (How the CBO can ignore it?)

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167

Look below how the CBO is hinting an index I2 while trying to reproduce a plan with index I1 that is not any more present in the database

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX_RS_ASC ("T1" "I2") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1

And how naturally it will be impossible to reproduce the I1 INDEX RANGES SCAN plan

SPM: plan Id in plan baseline = 2239163167, plan Id of reproduced plan = 3187078153
SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167  ---> I1 INDEX RANGE SCAN
plan_baseline hintset    :

hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
hint num  3 len 22 text: DB_VERSION('11.2.0.1')
hint num  4 len  8 text: ALL_ROWS
hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num  6 len 49 text: INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))
SPM: generated non-matching plan:

The CBO failed to reproduce the I1 index range scan and it produces an I2 index range scan instead. However the CBO is still considering that the game is not over and that there is a second chance. This is why we see the following lines about round 2 in the same  10053 trace file

SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167  ---> I1 INDEX RANGE SCAN

And the evident conclusion of a non reproducible plan even in this round 2

SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167
plan_baseline hintset    :
hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

SPM: generated non-matching plan:

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    42 |           |
| 1   |  SORT AGGREGATE    |         |     1 |    54 |       |           |
| 2   |   INDEX RANGE SCAN | I2      |   24K | 1318K |    42 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 2583336616
plan_hash_2    : 3187078153 ----> I2 INDEX RANGE SCAN

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG" "T1"."COL2"))  ---> (FLAG,COL2) are the I2 index columns
END_OUTLINE_DATA
*/
------- END SPM Plan Dump -------

Finally, after two impossible rounds, the CBO recognizes, what it should have recognized much earlier, that it is impossible to reproduce the I1 index range scan and decided to use the unique SPM plan it succeeded to reproduce .i.e. T1 TABLE FULL SCAN not without a extra re-parsing step

SPM: re-parsing to generate selected accepted plan, plan Id = 1634389831 ---> FULL TABLE SCAN

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("T1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   275 |           |
| 1   |  SORT AGGREGATE     |         |     1 |    54 |       |           |
| 2   |   TABLE ACCESS FULL | T1      |     1 |    54 |   275 |  00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 3724264953
plan_hash_2    : 1634389831
Peeked Binds

============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=178
char format=1
max length=32
value=Y1

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

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–

About these ads

4 Comments »

  1. Mohamed,

    Nice article – I like the way you’ve managed to strip out some of the volume that we didn’t need to see while keeping the points that were important so clear.

    I an going to have a guess at the answer to your last question about “unnecessary” optimisations. The BASELINE doesn’t name indexes, it describes them – so Oracle can’t assume that the absence of an index named i1 makes the baseline invalid because the name is (usually) not present in the baseline.

    So the generic optimizer code has to apply the hint set to the query and construct the plan for every index that matches the description from the baseline – i.e. every index that starts with (flag), although it would stop if it found an exact match. In your case there is only one matching index – I am a little surprised at this point that the unparsed query decides to display the actual index name for the hint it shows rather than using the description, but I guess retrieving the description would be a little more work.

    As a further test it would be interesting to see what happened if you created TWO different indexes that started with the (flag) column – I think you would see both being tested at that point.

    There are further experiments you could then do into this area based on a query with two predicates and creating indexes which reverse the order of the columns after accepting plans which use the single column (flag) index.

    Comment by Jonathan Lewis — November 8, 2013 @ 9:48 am | Reply

  2. Jonathan,

    Thanks for your encouraging comment

    Best regards

    Comment by Mohamed Houri — November 9, 2013 @ 7:56 am | Reply

  3. Houri,

    Very Good Explanation!!!.

    Thanks,

    Yousuf,

    Comment by Yousuf — November 24, 2013 @ 4:40 pm | Reply

  4. Nice explanation … thanks for sharing.

    Comment by goran — December 4, 2013 @ 3:22 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: