This blog post is about another buggy/funny/misleading behaviour which you MUST know if you are using baselines ( especially in a non-controlled fashion by let oracle manage them
) It looks like a long post but it is actually an easy read since nothing rocket science in the post.
All story started when one of our batches was not using the plan stored in the sql plan baseline repository.
In these cases app support check if the baseline is used by checking v$sql.sql_plan_baseline value.
If it is not used then they check the enabled, accepted baselines by using the statement below as in documentation
dbms_xplan.display_sql_plan_baseline(sql_handle=>’&sql_handle’,plan_name=>’&sql_plan’, format=>’BASIC’));
Once they find the plan_hash_value they are looking for they try to fix it and expect Oracle use the baseline which it should use in theory.
In our case it didn’t. When I checked the plan baseline was created in september 2010 and never actually used since 14 January 2011.
Fun part is some occasions same plan hash value in the baseline is being generated but Oracle is not reporting that baseline is used. But that’s the whole point of baselines having a constant plan hash value. Why oracle is not using the plan baseline even it generates the same plan with same plan hash value
We raised the issue with Oracle and oracle come back with couple of request
1- 10053 trace
2- plan baseline trace( alter session set events ‘trace [SQL_Plan_Management.*]’;)
We provided the requests and they said if there was a change in the table structure at some point which is causing the plan can’t be reproduced. App team confirmed that there was a structural change to drop global index and recreate it as local on actually the date when the baseline was last used.
The problem is we changed a global index to local index on 14 January 2011 with an index name change, but display_sql_plan_baseline shows us a plan which uses a local index with the current name.This cannot be possible since the baseline is create on September 2010. So what was wrong?
Support could not managed to give me a decent answer or accept that it is a bug so it is time for me to create a test case and reveal the truth why this issue is happening.
HR@ORACOS> ---simple table creation
HR@ORACOS> create table baseline_test as select * from dba_objects;
Table created.
HR@ORACOS> ---index creation
create unique index baseline_test_idx_1
2 on baseline_test(object_id);
Index created.
HR@ORACOS> ---gather table stats
HR@ORACOS> @gts baseline_test
PL/SQL procedure successfully completed.
HR@ORACOS> HR@ORACOS>
HR@ORACOS>
HR@ORACOS> ---start session level baseline capturing process
HR@ORACOS> alter session set optimizer_capture_sql_plan_baselines=TRUE;
Session altered.
HR@ORACOS> ---run the query below several times for plan capturing happens
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> @e1
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 910wg4cc9fbwn, child number 0
-------------------------------------
select object_name from baseline_test where object_id=5 and
data_object_id=10
Plan hash value: 3566649846
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA_OBJECT_ID"=10)
2 - access("OBJECT_ID"=5)
Note
-----
- SQL plan baseline SQL_PLAN_g64u8gfpwnfr028052575 used for this statement
25 rows selected.
HR@ORACOS> @sql_plan_baselines2 910wg4cc9fbwn
##################
#active baselines#
##################
INST_ID UE SQL_ID PLAN_HASH_VALUE CHILD_NUMBER SQL_PLAN_BASELINE SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED CREATED LAST_EXECUTED
---------- ---- ------------- --------------- ------------ ------------------------------ ------------------------------ ------------------------------ ------- -------- ----- ------------------------- -------------------------
1 0 910wg4cc9fbwn 3566649846 0 SQL_PLAN_g64u8gfpwnfr028052575 SYS_SQL_f313487babca3ae0 SQL_PLAN_g64u8gfpwnfr028052575 YES YES NO 13-AUG-11 15.45.42.000000 13-AUG-11 15.45.42.000000
Now it is time to check the plan output
HR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'BASIC'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr028052575
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr028052575 Plan id: 671425909
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3566649846
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST |
| 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_1 |
-----------------------------------------------------------
21 rows selected.
Since we have a baseline now we can change the table index structure to replicate the issue
HR@ORACOS> ---now drop the index
HR@ORACOS> drop index baseline_test_idx_1
2 ;
Index dropped.
HR@ORACOS> create unique index baseline_test_idx_2 on baseline_test(data_object_id,object_id);
Index created.
HR@ORACOS> ---Run it couple of times for baseline generation
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> @e1
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 910wg4cc9fbwn, child number 0
-------------------------------------
select object_name from baseline_test where object_id=5 and
data_object_id=10
Plan hash value: 3304552195
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=10 AND "OBJECT_ID"=5)
20 rows selected.
As you can see baseline no baseline is used. Lets check if a baseline captured ?
HR@ORACOS> @sql_plan_baselines2 910wg4cc9fbwn
##################
#active baselines#
##################
INST_ID UE SQL_ID PLAN_HASH_VALUE CHILD_NUMBER SQL_PLAN_BASELINE SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED CREATED LAST_EXECUTED
---------- ---- ------------- --------------- ------------ ------------------------------ ------------------------------ ------------------------------ ------- -------- ----- ------------------------- -------------------------
1 0 910wg4cc9fbwn 3304552195 0 SYS_SQL_f313487babca3ae0 SQL_PLAN_g64u8gfpwnfr028052575 YES YES NO 13-AUG-11 15.45.42.000000 13-AUG-11 15.45.42.000000
1 0 910wg4cc9fbwn 3304552195 0 SYS_SQL_f313487babca3ae0 SQL_PLAN_g64u8gfpwnfr09db5a957 YES NO NO 13-AUG-11 15.49.54.000000
Two baselines are captured in the system, one is accepted which is the one initially created before the index recreation but the other one is not accepted ??
Now it is time to see what the plans of these baselines
HR@ORACOS> --ACCEPTED PLAN
HR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'BASIC'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr028052575
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr028052575 Plan id: 671425909
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3304552195
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST |
| 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_2 |
-----------------------------------------------------------
21 rows selected.
HR@ORACOS> ---NON ACCEPTED NEWLY GENERATED PLAN
HR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'BASIC'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr09db5a957
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr09db5a957 Plan id: 2645928279
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3304552195
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST |
| 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_2 |
-----------------------------------------------------------
21 rows selected.
They are same ???? How come the plan can be same with the one which has different index names ??
If you are a careful reader you might have spotted The plan_id is different with same plan hash value
Lets check with ADVANCED option instead BASIC
HR@ORACOS> ----ACCEPTED BASELINE
HR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'ADVANCED'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr028052575
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr028052575 Plan id: 671425909
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "BASELINE_TEST"@"SEL$1" ("BASELINE_TEST"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
--------------------------------------------------------------------------------
Plan hash value: 3304552195
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / BASELINE_TEST@SEL$1
2 - SEL$1 / BASELINE_TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "BASELINE_TEST"@"SEL$1" ("BASELINE_TEST"."DATA_OBJECT_ID"
"BASELINE_TEST"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=10 AND "OBJECT_ID"=5)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - "BASELINE_TEST".ROWID[ROWID,10]
68 rows selected.
Did you spot something ? Look carefully because I did not look carefully and wasted 1 day. Once you know how to get the higher detail of information better you should look carefully
HR@ORACOS>---NON ACCEPTED NEWLY GENERATED PLAN
hR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'ADVANCED'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr028052575
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr028052575 Plan id: 671425909
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "BASELINE_TEST"@"SEL$1" ("BASELINE_TEST"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
--------------------------------------------------------------------------------
Plan hash value: 3304552195
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BASELINE_TEST | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BASELINE_TEST_IDX_2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / BASELINE_TEST@SEL$1
2 - SEL$1 / BASELINE_TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "BASELINE_TEST"@"SEL$1" ("BASELINE_TEST"."DATA_OBJECT_ID"
"BASELINE_TEST"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=10 AND "OBJECT_ID"=5)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - "BASELINE_TEST".ROWID[ROWID,10]
68 rows selected.
If you haven’t spotted already, the problem with the accepted plan output is the difference between OUTLINE DATA FROM SMB and OUTLINE DATA sections.
There are two outline sections per baseline output. one is OUTLINE DATA FROM SMB and other is OUTLINE DATA
Now you can do line by line comparison and see the difference.
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@”SEL$1″ “BASELINE_TEST”@”SEL$1” (“BASELINE_TEST”.”OBJECT_ID”))
OUTLINE_LEAF(@”SEL$1″)
ALL_ROWS
DB_VERSION(‘11.2.0.1’)
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.1’)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@”SEL$1″ “BASELINE_TEST”@”SEL$1” (“BASELINE_TEST”.”DATA_OBJECT_ID”
“BASELINE_TEST”.”OBJECT_ID”))
OUTLINE_LEAF(@”SEL$1″)
ALL_ROWS
DB_VERSION(‘11.2.0.1’)
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.1’)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
What happens here is that Oracle only stores the outline information and when you run the display_sql_plan_baseline
Oracle tries to generate the plan output from the outline but problem starts there because it tries to use the index which is not existent on the system anymore so it can’t reproduce the plan and instead of giving any warning or error it generates a plan for you by lying you.
To confirm what we said lets drop the secondary index and see what dbms_xplan.display_sql_plan_baseline will generate for us for the same baseline
HR@ORACOS> ---dropping index
HR@ORACOS>
HR@ORACOS> drop index baseline_test_idx_2;
Index dropped.
------FIRST BASELINE CREATED
HR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'BASIC'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr09db5a957
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr09db5a957 Plan id: 2645928279
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2027741861
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| BASELINE_TEST |
-------------------------------------------
20 rows selected.
As you can see now it generates a new plan for us for the same baseline 🙂 What a liar
What is the output from the valid but not accepted baseline, once we dropped the index it was using
HR@ORACOS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'BASIC'));
Enter value for sql_handle: SYS_SQL_f313487babca3ae0
Enter value for sql_plan: SQL_PLAN_g64u8gfpwnfr028052575
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_f313487babca3ae0
SQL text: select object_name from baseline_test where object_id=5 and
data_object_id=10
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g64u8gfpwnfr028052575 Plan id: 671425909
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2027741861
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| BASELINE_TEST |
-------------------------------------------
20 rows selected.
Same issue happened again.
Lets run the same sql to see if it generates and accepts a baseline with full table scan
HR@ORACOS> ---Lets run the sql again to force a plan
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> select object_name from baseline_test where object_id=5 and data_object_id=10;
no rows selected
HR@ORACOS> @e1
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 910wg4cc9fbwn, child number 0
-------------------------------------
select object_name from baseline_test where object_id=5 and
data_object_id=10
Plan hash value: 2027741861
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 217 (100)| |
|* 1 | TABLE ACCESS FULL| BASELINE_TEST | 1 | 32 | 217 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_ID"=5 AND "DATA_OBJECT_ID"=10))
19 rows selected.
The baseline is generated as below but it is still not accepted.
HR@ORACOS> @sql_plan_baselines2 910wg4cc9fbwn
##################
#active baselines#
##################
INST_ID UE SQL_ID PLAN_HASH_VALUE CHILD_NUMBER SQL_PLAN_BASELINE SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED CREATED LAST_EXECUTED
---------- ---- ------------- --------------- ------------ ------------------------------ ------------------------------ ------------------------------ ------- -------- ----- ------------------------- -------------------------
1 0 910wg4cc9fbwn 2027741861 0 SYS_SQL_f313487babca3ae0 SQL_PLAN_g64u8gfpwnfr028052575 YES YES NO 13-AUG-11 15.45.42.000000 13-AUG-11 15.45.42.000000
1 0 910wg4cc9fbwn 2027741861 0 SYS_SQL_f313487babca3ae0 SQL_PLAN_g64u8gfpwnfr09db5a957 YES NO NO 13-AUG-11 15.49.54.000000
1 0 910wg4cc9fbwn 2027741861 0 SYS_SQL_f313487babca3ae0 SQL_PLAN_g64u8gfpwnfr018837053 YES NO NO 13-AUG-11 15.59.15.000000
I assume You already understood the issue. This is a proper buggy/dodgy behaviour for me.
Now lets try some workaround to see how we can get over from this situation.
There are 2 ways to get around from this problem and all of them needs DBA input. I have tested all and they are working but you can go ahead and test them. I don’t want to make this blog post longer than it is so I’m only listing the options.
1- Evolve the baseline for the sql_handle so Oracle accepts the plans (will keep the invalid ones in the system)
2- Drop the invalid baselines and evolve the handle (this way you will get rid of the invalid ones)
3- Drop all baselines related with the objects altered and allow capturing to the trick for you. (Less headache than second but you may lose the good plans- still would be my approach for releases )
Even Oracle warns in documentation “SQL plan baselines cannot help in cases where an event has caused irreversible execution plan changes, such as dropping an index.” and also ” if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, then the optimizer selects the best-cost plan. ”
The did not give much information about the behaviour.
From my point of view there are two major problems which needs to be addressed for this specific table structure change case
1- Instead of showing a plan which is not same as baseline, display_sql_plan_baseline must report an error that stored baseline is not reproduce-able
2- Baselines are not invalidated there must be an invalidation flag which makes the baseline invalidated at very first time CBO can’t reproduce the plan via baseline and set enabled and accepted flag to no. Since the purging of the plans is 53 weeks by
default for non used baselines this is a needed enhancement. If a plan is accepted and not produceable with smaller cost it avoids other plans accepted.
Another problem with baselines is also covered in this clear post by Maxym Kharchenko which can be related with my first point (that very same post series from Maxym is one of the reasons I did not continue my series since author nearly covered all -but I still have something to write) What happens if both objects are under two schemas like some development groups uses for different testing phases. One drops the index one did not and you have system level capturing on. ?
I think Oracle must address these issues by introducing another complexity for sql signatures which involves schema owner or something similar.
Long story short you might be easily fooled by baselines with the current structure. If I were you I will always check the plans with at least OUTLINE option or go for ADVANCED.
Confession:n I am always using advanced option but as usual I did not pay that much attention to the output at first point 😦 That was my biggest mistake there which cost 1 day for analysis. knowing and using an option but not paying attention to the output of the option.)
All tests are done on 11.2.0.1
Bonus material
For the ones who likes tracefiles this issue is shown as this
=======TRACEFILE ======
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
parse_schema name : HR
plan_baseline signature : 17515423071774325472
plan_baseline plan_id : 671425909
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 | | | | 217 | |
| 1 | TABLE ACCESS FULL | BASELINE_TEST| 1 | 32 | 217 | 00:00:02 |
------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(("OBJECT_ID"=5 AND "DATA_OBJECT_ID"=10))
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : HR
plan_hash : 2027741861
plan_hash_2 : 411267155
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" "BASELINE_TEST"@"SEL$1")
END_OUTLINE_DATA
*/
------- END SPM Plan Dump -------
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 411267155
Coskan's Approach to Oracle 
I’m wondering if this behavior stems from handling the possibility of indices being deferred, rebuilt online or otherwise temporarily unavailable. You wouldn’t want to totally invalidate a baseline due to a temporary situation. You would want to be explicit about it though, I’d think a view that included info from when these things happen should be available.
Do you see the same effect when you, say, change an index to novalidate?
Comment by barfo rama — August 15, 2011 @ 11:13 pm
I am curious. Is the signature from dba_sql_plan_baselines the same for both?
Comment by mdinh — August 16, 2011 @ 1:04 pm
Thanks for the comment. They are all same. sql_plan_baselines2.sql is actually query v$sql and dba_sql_plan_baseline via force_matching_signature and signature columns
Comment by coskan — August 16, 2011 @ 6:20 pm
I guess it’s exact_matching_signature=signature.
I have also noticed that the sql plan baselines aren’t being used after a table was altered in PeopleSoft applications(I am sure you must be aware of how PeopleSoft application designer generates alter scripts).In my case,I did a test w/o altering the table/index structure by choosing “alter even if no changes” option from application designer’s build options.
Thanks
Antony
Comment by anthonydba — May 14, 2012 @ 8:19 pm
[…] After I wrote this up I realized that Coskan Gundogar had already blogged about it here (pretty thoroughly I might add). So please see his post as well. By the way, I do agree with his […]
Pingback by Kerry Osborne’s Oracle Blog » Blog Archive » Displaying SQL Baseline Plans – Kerry Osborne’s Oracle Blog — March 15, 2012 @ 8:33 pm
Thanks! Great Kerry’s scripts usage examples.
Comment by Sergey — July 26, 2012 @ 1:12 pm
Thanks. Very useful info.
Comment by Sergey — July 26, 2012 @ 1:13 pm
Since 11.2.0.2, REPRODUCED column from DBA_SQL_PLAN_BASELINE can help
-> REPRODUCED : Indicates whether the optimizer was able to reproduce the plan (YES) or not (NO).
Comment by Marie Raillard — August 8, 2012 @ 3:07 pm
more about 918
dbms_xplan.display_sql_plan_baseline can also lie ! | Coskan’s Approach to Oracle
Trackback by more about 918 — November 17, 2020 @ 7:41 pm
Hello Coskan,
It is a detailed article. Would you mind showing sql_plan_baselines2.sql’s text.
Comment by ruparelkaushalgmailcom — June 25, 2021 @ 11:01 am