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

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–

April 4, 2013

What can impeach Adaptive Cursor Sharing kicking off?

Filed under: Sql Plan Managment — hourim @ 10:54 am

I ended my last post about the interaction between ACS and SPM by the following observation

How could a creation of an extra index disturb the ACS behavior?

Well, it seems that there is a different combination which leads to this situation. Instead of jumping to a conclusion that might be wrong I prefer presenting my demo upon which I will make a proposition and let you (readers thanks in advance for that) criticizing what I tend to affirm.

For sake of simplicity, the following sql against v$sql will be referred to as is_bind_aware.sql.

SQL > select sql_id
2    , child_number
3    , is_bind_aware
4    , is_bind_sensitive
5    , is_shareable
6    , to_char(exact_matching_signature) sig
7    , executions
8    , plan_hash_value
9    from v$sql
10    where sql_id = '731b98a8u0knf';

The model used for this demo can be found in Dominic Brook’s article and I will start from here

SQL > exec :n := 'N2'; --> FULL TABLE SCAN

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

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

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

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

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50110 |  2642K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

SQL > exec :n := 'Y2'; --> INDEX RANGE SCAN

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 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     9 |   486 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

After several executions of the above sql, using alternately index and full table scan bind variables, I ended up with the following situation:

SQL > @is_bind_aware.sql

SQL_ID        CHILD_NUMBER I I I SIG                EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ------------------ ----------- -----------------
731b98a8u0knf 0            N Y N 1292784087274697613 2          3724264953
731b98a8u0knf 1            Y Y N 1292784087274697613 1          3625400295
731b98a8u0knf 2            Y Y Y 1292784087274697613 1          3724264953   --> TABLE FULL SCAN
731b98a8u0knf 3            Y Y Y 1292784087274697613 1          3625400295   --> INDEX RANGE SCAN

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            3          3066078819 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            2          3938583969 Y          1          50000
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

SQL > select * from v$sql_cs_histogram where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
22A4D04C 2443201166 731b98a8u0knf            3          0          1 --> rows_processed < 1,000
22A4D04C 2443201166 731b98a8u0knf            3          1          0
22A4D04C 2443201166 731b98a8u0knf            3          2          0
22A4D04C 2443201166 731b98a8u0knf            2          0          0
22A4D04C 2443201166 731b98a8u0knf            2          1          1 --> 1,000 <rows_processed <1,000,000
22A4D04C 2443201166 731b98a8u0knf            2          2          0
22A4D04C 2443201166 731b98a8u0knf            1          0          1
22A4D04C 2443201166 731b98a8u0knf            1          1          0
22A4D04C 2443201166 731b98a8u0knf            1          2          0
22A4D04C 2443201166 731b98a8u0knf            0          0          1
22A4D04C 2443201166 731b98a8u0knf            0          1          1
22A4D04C 2443201166 731b98a8u0knf            0          2          0

Two child cursor(2 and 3) that are (a) shareable (b) bind sensitive and (c) bind aware so that ACS can associate each bind variable to it’s a corresponding child number and hence the execution plan that best fits each bind variable.

Up to this point,  ACS is working very well in presence of a SPM baseline

SQL > select
2      to_char(signature) signature
3    , sql_handle
4    , plan_name
5    , enabled
6    , accepted
7    from dba_sql_plan_baselines
8    where signature = 1292784087274697613;

SIGNATURE                                SQL_HANDLE                     PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ ------------------------------ --- ---
1292784087274697613                      SYS_SQL_11f0e4472549338d       SQL_PLAN_13w748wknkcwd616acf47 YES YES
1292784087274697613                      SYS_SQL_11f0e4472549338d       SQL_PLAN_13w748wknkcwd8576eb1f YES YES

I have two enabled and accepted sql plan baseline (one, SQL_PLAN …eb1f, for the index range scan and the other one, SQL_PLAN … acf47, for the table full scan). Now, I will create an extra index(i2) in addition to the existing i1 index  and I will continue my selects


SQL > create index i2 on t1(flag,col2) compress;

Index created.

I will then first execute my query for FULL TABLE SCAN bind variable

SQL > exec :n := 'N1';

PL/SQL procedure successfully completed.

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

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

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

SQL_ID  731b98a8u0knf, child number 2
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 2
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)

This error is an  indication that something went abnormally as already notified by the Oracle Optimizer blog.


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

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

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

SQL_ID  731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49872 |  2629K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

For the sake of clarity I will present below the results of is_bind_aware.sql before the creation of the I2 index and after its creation

SQL > @is_bind_aware.sql  --> before the index creation

SQL_ID        CHILD_NUMBER I I I SIG                EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ------------------ ----------- -----------------
731b98a8u0knf 0            N Y N 1292784087274697613 2          3724264953
731b98a8u0knf 1            Y Y N 1292784087274697613 1          3625400295
731b98a8u0knf 2            Y Y Y 1292784087274697613 1          3724264953   --> TABLE FULL SCAN
731b98a8u0knf 3            Y Y Y 1292784087274697613 1          3625400295   --> INDEX i1 RANGE SCAN

SQL > @is_bind_aware.sql  --> after the index creation

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953
731b98a8u0knf            1 Y Y N 1292784087274697613                               1      3625400295
731b98a8u0knf            2 N N Y 1292784087274697613                               1      3724264953 --> TABLE FULL SCAN

Wow!!! Child cursor n° 3 has gone while child cursor n° 2, despite it is still shareable, becomes however not bind sensitive and not bind aware. And how this has influenced the ACS view?


SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; --> before the index creation

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            3          3066078819 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            2          3938583969 Y          1          50000
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; --> after the index creation

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

No trace of child cursor n ° 2 or n ° 3 in this view while the presence of child cursor n ° 0 and n ° 1 can be considered as obsolete because they represent a non shareable cursors.

Let’s continue executing the query this time using the INDEX RANGE SCAN bind variable

SQL > exec :n := 'Y1'

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 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49872 |  2629K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

Repeat the same sql several times to see if  ACS will kick off and produce the INDEX RANGE SCAN plan  (the one identified into the SPM baseline SQL_PLAN …eb1f)

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

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

SQL > /

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

SQL > /

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

… Execute this several times

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 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49872 |  2629K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

No way for the CBO to produce the INDEX RANGE SCAN Plan so that the SPM will select it. And why the CBO is unable to produce the INDEX RANGE SCAN plan? There might be two answers to that question (a) either the ACS is working well but it is producing a plan that is not in the SPM and hence it is constrained or (b) the ACS is not working and the CBO is always sharing the existing FULL TABLE SCAN until a hard parse occurs. Let see first if the ACS is working well

SQL> @is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - -------------------- ----------- -----------------
731b98a8u0knf  0           N Y N 1292784087274697613  2           3724264953
731b98a8u0knf  1           Y Y N 1292784087274697613  1           3625400295
731b98a8u0knf  2           N N Y 1292784087274697613  18          3724264953

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

ACS is not working!!!

What if I disable the use of sql baseline?

SQL > show parameter '%baseline%'

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL > alter session set optimizer_use_sql_plan_baselines = FALSE;

Session 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 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |   972 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

SQL > @is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953
731b98a8u0knf            1 Y Y N 1292784087274697613                               1      3625400295
731b98a8u0knf            2 N N Y 1292784087274697613                              18      3724264953
731b98a8u0knf            3 N Y Y 1292784087274697613                               1      3625400295

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            3          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

The new plan is not due to ACS because the corresponding child cursor n°3 is not yet bind aware. So this new plan is due to a hard parse. Let’s continue with the FULL TABLE SCAN bind variable

SQL > exec :n := 'N1'

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

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

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

SQL_ID  731b98a8u0knf, child number 3
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |   972 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

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

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

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    46 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |    54 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I2   | 49872 |  2629K|    46   (5)| 00:00:01 |
------------------------------------------------------------------------------

It seems that ACS is back.


SQL >@is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - -------------------- ----------- ----------------
731b98a8u0knf    0         N Y N 1292784087274697613  2           3724264953
731b98a8u0knf    1         Y Y N 1292784087274697613  1           3625400295
731b98a8u0knf    2         N N Y 1292784087274697613  18          3724264953
731b98a8u0knf    3         N Y Y 1292784087274697613  2           3625400295
731b98a8u0knf    4         Y Y Y 1292784087274697613   1          2348726875  --> INDEX FAST FULL SCAN

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            4          1896453392 Y          1          50000 -->INDEX FAST FULL S.
22A4D04C 2443201166 731b98a8u0knf            3          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

ACS is in fact really back. After several executions I have the following ACS picture


SQL >@is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                     EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ----------------------------------- ----- ---------
731b98a8u0knf            0 N Y N 1292784087274697613      2          3724264953
731b98a8u0knf            1 Y Y N 1292784087274697613      1          3625400295
731b98a8u0knf            2 N N Y 1292784087274697613      18         3724264953
731b98a8u0knf            3 N Y N 1292784087274697613      2          3625400295
731b98a8u0knf            4 Y Y Y 1292784087274697613      1          2348726875 --> INDEX i2 FAST FULL SCAN
731b98a8u0knf            5 Y Y Y 1292784087274697613      1          3625400295 --> INDEX i1 RANGE SCAN

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            5          3066078819 Y          1              3  --> INDEX i1 RANGE SCAN
22A4D04C 2443201166 731b98a8u0knf            4          1896453392 Y          1          50000  --> INDEX i2 FFS
22A4D04C 2443201166 731b98a8u0knf            3          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

SQL > select * from v$sql_cs_histogram where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
22A4D04C 2443201166 731b98a8u0knf            5          0          1   ---> bucket id 0 incremented
22A4D04C 2443201166 731b98a8u0knf            5          1          0
22A4D04C 2443201166 731b98a8u0knf            5          2          0
22A4D04C 2443201166 731b98a8u0knf            4          0          0
22A4D04C 2443201166 731b98a8u0knf            4          1          1   ---> bucket id 1 incremented
22A4D04C 2443201166 731b98a8u0knf            4          2          0
22A4D04C 2443201166 731b98a8u0knf            3          0          1
22A4D04C 2443201166 731b98a8u0knf            3          1          1
22A4D04C 2443201166 731b98a8u0knf            3          2          0
22A4D04C 2443201166 731b98a8u0knf            1          0          1
22A4D04C 2443201166 731b98a8u0knf            1          1          0
22A4D04C 2443201166 731b98a8u0knf            1          2          0
22A4D04C 2443201166 731b98a8u0knf            0          0          1
22A4D04C 2443201166 731b98a8u0knf            0          1          1
22A4D04C 2443201166 731b98a8u0knf            0          2          0

The post is becoming long and may be annoying so I will stop here not without mentioning that I did played with the demo setting the optimizer_capture_sql_plan_baselines to TRUE/FALSE and observing the behavior of ACS through its corresponding views and it seems that adding an extra index generates a new sql plan baseline that is not into the SPM and influence a little bit the work of ACS without knowing the exact reason.

April 1, 2013

Sql Plan Mangement(SPM) and Adaptive Cursor Sharing(ACS) : My résumé

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

I read Dominic Brook’s interesting article about Adaptive Cursor Sharing and SQL Plan Baseline. I, then, have read the also interesting follow-up blog article written by one of those modest and smart Oracle guys Coskan Gundogar which he has entitled Adaptive Cursor Sharing with SQL Plan Baselines – Bind Sensitiveness. Finally, I have ended up my “SPM-ACS collaboration Giro” with the Optimizer blog article entitled How do adaptive cursor sharing and SQL Plan Management interact

Let me start by presenting the conclusions of these articles respectively

Dominic’s conclusion

Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.

Coskan’s conclusion

I personally think they work perfectly fine together but I also wish if Oracle gives option to hold this runtime monitoring info in SYSAUX for env where people can accommodate more data in SYSAUX. This will save a lot of time for the initial loads.

Optimizer group conclusion

If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the    query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS.

I like very much the optimizer group conclusion:

“SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS

Yes, that’s very correct.  Because ACS and SPM are playing different goals:

ACS is a feature that helps the CBO  adapt itself to the input bind variable in order to generate an execution plan that best fits that bind variable. ACS, given certain conditions, kicks of independently of the presence or not of a SQL Baseline

SPM is a feature which guarantees plan stability and allow plan evolution. It ensures only accepted plan will be executed whatever the technology used by the CBO to generate the best execution plan: ACS or Cardinality Feedback (even thought that Dominic and Kerry Osborne have already investigated the Interaction of SPM and Cardinality Feedback where they both demonstrated that this interaction is not as simple as it looks).

The logic of plan selection when SPM is used follows the following diagram:

SPM Selectin

In which we can see that when the best generated CBO plan is not already inside the SQL plan baseline (i.e. plan is ENABLED and ACCEPETD) then it will not be used. Instead, it will be inserted into the SQL plan history (i.e. ENABLED and not ACCEPTED) waiting to be evolved either manually using DBMS_SPM package or automatically when the Tuning Advisor consent to do so.

What does this means all in all?

In my opinion, in order to have a good collaboration between ACS and SPM, we need to load ACS plans (we have better to do that manually than automatically because they will be immediately ENABLED and ACCEPTED) and hope that all plans generated by the CBO via ACS will match the plans we have already loaded into the SPM baseline.  When the CBO comes up with a plan that is not into the SPM baseline it will not be used. Instead all ENABLED and ACCEPTED plans will compete against each other and the best plan from the Baseline will be selected for use.

The optimizer group example is largely sufficient to explain what I have stated above. The goal of this article is to start from the Coskan’s article end and present a curious observation.

A picture is worth a thousand words (in order to make this post as short as possible, select against dba_sql_plan_baseline will be referred to as pbaseline)

 SQL> > select
  2 to_char(signature) signature
  3 , sql_handle
  4 , plan_name
  5 , enabled
  6 , accepted
  7 from dba_sql_plan_baselines
  8 where signature = 1292784087274697613;

 SIGNATURE           SQL_HANDLE                PLAN_NAME                     ENA  ACC
 ---------------------------------------- ------------------------------ ---------------------------------
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES -> FULL SCAN
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES -> INDEX RANGE SCAN

 SQL> select sql_id
  2 , child_number
  3 , is_bind_aware
  4 , is_bind_sensitive
  5 , is_shareable
  6 , to_char(exact_matching_signature) sig
  7 , executions
  8 , plan_hash_value
  9 from v$sql
  10 where sql_id = '731b98a8u0knf';

 SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS  PLAN_HASH_VALUE
 ------------- ------------ - - - ------------------- ----------- -----------------
 731b98a8u0knf 0            N Y N 1292784087274697613  2           3625400295
 731b98a8u0knf 1            Y Y Y 1292784087274697613  2           3724264953  -> bind aware
 731b98a8u0knf 2            Y Y N 1292784087274697613  1           3625400295
 731b98a8u0knf 3            Y Y Y 1292784087274697613  1           3625400295  -> bind aware
 

Two plan baselines and two shareable sql child (1 and 3) that are bind sensitive and bind aware so that when FULL bind variable  (‘N1’) is used we get a FULL TABLE SCAN and when INDEX bind variable (‘Y1’) is used we get an INDEX RANGE SCAN.

  •  FULL scan: n=’N1’
 SQL > select count(*), max(col2) from t1 where flag = :n;

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

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

 Plan hash value: 3724264953

 ---------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes    | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |          | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30       |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 49872 | 1461K    | 275 (2)    | 00:00:04 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 2 - filter("FLAG"=:N)

 Note
 -----
  - SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
 
  •  INDEX scan : n=’Y1’
 SQL> select count(*), max(col2) from t1 where flag = :n;

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

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

 Plan hash value: 3625400295

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

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 3 - access("FLAG"=:N)

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

ACS produces the  plan which best fits the input bind variable and SPM used that plan because it found it into its  SPM baseline.

 SQL> @pbaseline

 SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS   PLAN_HASH_VALUE
 ------------- ------------ - - - -------------------- ----------- -----------------
 731b98a8u0knf 0            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 1            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 2            Y Y N 1292784087274697613   1          3625400295
 731b98a8u0knf 3            Y Y Y 1292784087274697613   6          3724264953   -> bind aware
 731b98a8u0knf 4            Y Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 5            Y Y Y 1292784087274697613   1          3625400295   -> bind aware
 

So far so good.

Let’s disturb a little bit this situation by creating an extra index on t1.

 SQL> create index I2 on t1(flag, col2) compress;

 Index created.

SQL> select count(*), max(col2) from t1 where flag = :n;
 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
  49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

 Plan hash value: 3724264953

 ------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |       | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30    |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 50110 | 1468K | 275 (2)    | 00:00:04 |
 ------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 2 - filter("FLAG"=:N)

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

That’s nice. The FULL TABLE SCAN baseline (cf47) kicks off appropriately. But let see what plan the CBO comes up with

 SQL > @pbaseline
 SIGNATURE           SQL_HANDLE               PLAN_NAME                     ENA  ACC
 ------------------- ------------------------ --------------------------- ------ ----
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd495f4ddb YES  NO
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES  YES
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES  YES
 

The CBO comes up with a new execution plan (SQL_PLAN_13w748wknkcwd495f4ddb) which has been constrained(discarded) by the SPM baseline. This new plan has been inserted into the SPM plan history (ACCEPTED =’NO’) for future evolution. The newly generated execution plan uses a INDEX FAST FULL SCAN of the new I2 index and it resembles to:

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

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd495f4ddb Plan id: 1230982619
 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 2348726875

 ---------------------------------------------------------------------------
 | Id | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 | 0  | SELECT STATEMENT    |      | 1     | 30    | 46 (5)     | 00:00:01 |
 | 1  | SORT AGGREGATE      |      | 1     | 30    |            |          |
 |* 2 | INDEX FAST FULL SCAN| I2   | 25000 | 732K  | 46 (5)     | 00:00:01 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

  2 - filter("FLAG"=:N)
 

But what looks strange it this

 SQL> @pbaseline

 SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS PLAN_HASH_VALUE
 ------------- ------------ - - - ------------------- ----------- ---------------

 731b98a8u0knf 0            N Y N 1292784087274697613  2          3625400295
 731b98a8u0knf 1            N Y N 1292784087274697613  2          3625400295
 731b98a8u0knf 2            Y Y N 1292784087274697613  1          3625400295
 731b98a8u0knf 3            N N Y 1292784087274697613  1          3724264953   -> Shareable but not bind aware
 731b98a8u0knf 4            Y Y N 1292784087274697613  2          3625400295
 

The child cursor number 5 has gone!!! I still have only one shareable child cursor (number 3 the one for FULL TABLE SCAN) which became no bind sensitive and no bind aware. Let’s execute the case of an INDEX RANGE SCAN

 SQL> exec :n := 'Y1';

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

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

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

 Plan hash value: 3724264953

 ------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |       | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30    |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 50110 | 1468K | 275 (2)    | 00:00:04 |
 ------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 2 - filter("FLAG"=:N)

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

 SQL> @pbaseline
SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS  PLAN_HASH_VALUE
 ------------- ------------ - - - ------------------- ----------- ---------------
 731b98a8u0knf 0            N Y N 1292784087274697613 8           3724264953
 731b98a8u0knf 1            Y Y N 1292784087274697613 2           3625400295
 731b98a8u0knf 2            N Y N 1292784087274697613 6           3625400295
 731b98a8u0knf 3            N N Y 1292784087274697613 15          3724264953 -> Shareable not bind aware
 731b98a8u0knf 4            Y Y N 1292784087274697613 2           3625400295

No way to make a shareable cursor bind sensitive and bind aware in order for the ACS to kick off and generate a plan that is in the SPM baseline.

Don’t tell me that this is due to the new index I2 I have created.

Will you?


SQL> drop index i2;

Index dropped.

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

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

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

Plan hash value: 3724264953

------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |       | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30    |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 49872 | 1461K | 275 (2)    | 00:00:04 |
 ------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("FLAG"=:N)

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

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

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

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

Plan hash value: 3625400295

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

Predicate Information (identified by operation id):
 ---------------------------------------------------

3 - access("FLAG"=:N)

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

SQL> @pbaseline

SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
 ------------- ------------ - - - -------------------------------  ------------------
 731b98a8u0knf 0            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 1            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 2            Y Y N 1292784087274697613   1          3625400295
 731b98a8u0knf 3            N Y Y 1292784087274697613   6          3724264953
 731b98a8u0knf 4            Y Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 5            Y Y Y 1292784087274697613   1          3625400295  -> bind aware

When I dropped the index, after a warm up execution my ACS is back.

How could a creation of an extra index disturb the ACS behavior? That’s the subject of my next investigation 

« Previous Page

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

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.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

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