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
> The baseline stored execution plan has been automatically updated to reflect the disappeared index
Perhaps I’m misunderstanding what you’re suggesting but this doesn’t make sense.
Without further investigation, I’m guessing that you’re showing me a bug/oversight in DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.
I presume that you had two plans in the baseline already? The index scan and the full?
How can the baseline plan be automatically updated?
The baseline is for a specific plan based on the plan id which maps to plan_hash_2 in v$sql_plan.other_xml.
In 11g, nothing else about the plan other than the hints and this plan_hash_2 is stored.
SQL_PLAN_13w748wknkcwd8576eb1f should not have the same plan id for both the index range scan and the full scan.
I believe, but I’ve yet to investigate, that 12c has the actual plan steps as well so I’m guessing it is not affected by the bug.
So… I wonder what DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE in 11g does behind the scenes for the execution plan?
Perhaps something nasty like an explain plan using the hints in the baseline which would explain the misinformation?
Comment by Dom Brooks — January 15, 2014 @ 11:20 am |
Dom,
Thanks for reading this post and the other one.
No, I have only one plan in the sql_plan_baseline; it is the plan with the index range scan. Let me present the example
create table t2 (col1 number ,col2 varchar2(50) ,flag varchar2(2)); insert into t2 select rownum , lpad('X',50,'X') , case when rownum = 1 then 'Y1' when rownum = 2 then 'Y2' when mod(rownum,2) = 0 then 'N1' else 'N2' end from dual connect by rownum <= 100000; create index i1 on t2 (flag); exec dbms_stats.gather_table_stats(user, 't2'); select count(1) from dba_sql_plan_baselines; COUNT(1) ---------- 0 ---> no baseline var n varchar2(2) exec :n := 'Y1'; alter session set optimizer_capture_sql_plan_baselines = TRUE; select count(1) from t2 where flag = :n; COUNT(1) ---------- 1 select count(1) from t2 where flag = :n; COUNT(1) ---------- 1 alter session set optimizer_capture_sql_plan_baselines = FALSE; select plan_name from dba_sql_plan_baselines; PLAN_NAME ------------------------------ SQL_PLAN_3jjzm5fb07xfs3f0011ba ---> one plan in SPM baseline SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format => 'ADVANCED')); -------------------------------------------------------------------------------- SQL handle: SQL_38c7f32b9603f5d8 SQL text: select count(1) from t2 where flag = :n -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba Plan id: 1056969146 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Outline Data from SMB: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ -------------------------------------------------------------------------------- Plan hash value: 3900446664 --> note this -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 13 (8)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| I1 | 25000 | 75000 | 13 (8)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this. Outline Data and Outline from SMB are identical OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FLAG"=:N) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] select hash_value, plan_hash_value from v$sql_plan where plan_hash_value = 3900446664; --> the above plan hash value HASH_VALUE PLAN_HASH_VALUE ---------- --------------- 3335798963 3900446664 3335798963 3900446664 3335798963 3900446664No, I will just drop the index and get the execution plan of the already existing sql_plan_baseline
drop index i1; SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format => 'ADVANCED')); -------------------------------------------------------------------------------- SQL handle: SQL_38c7f32b9603f5d8 SQL text: select count(1) from t2 where flag = :n -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba Plan id: 1056969146 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Outline Data from SMB: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) ---> spot this OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ -------------------------------------------------------------------------------- Plan hash value: 3321871023 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 241 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | TABLE ACCESS FULL| T2 | 25000 | 75000 | 241 (3)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "T2"@"SEL$1") --> spot this change from INDEX to FULL OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("FLAG"=:N) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 65 rows selected. SQL> select hash_value, plan_hash_value from v$sql_plan where plan_hash_value = 3321871023; no rows selectedAnd now I repeat exactly the same thing in 12c and here below what I have
In Presence of the index
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format=>'ADVANCED')); -------------------------------------------------------------------------------- SQL handle: SQL_38c7f32b9603f5d8 SQL text: select count(1) from t2 where flag = :n -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba Plan id: 1056969146 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE Plan rows: From dictionary -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Outline Data from SMB: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('12.1.0.1') OPTIMIZER_FEATURES_ENABLE('12.1.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ -------------------------------------------------------------------------------- Plan hash value: 3900446664 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 49 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| I1 | 25000 | 75000 | 49 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('12.1.0.1') OPTIMIZER_FEATURES_ENABLE('12.1.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :N (VARCHAR2(30), CSID=178): 'Y1' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FLAG"=:N) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22]In the absence of the index
SQL> drop index i1; Index dropped. select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_3jjzm5fb07xfs3f0011ba', format=>'ADVANCED')); -------------------------------------------------------------------------------- SQL handle: SQL_38c7f32b9603f5d8 SQL text: select count(1) from t2 where flag = :n -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_3jjzm5fb07xfs3f0011ba Plan id: 1056969146 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE Plan rows: From dictionary -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Outline Data from SMB: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('12.1.0.1') OPTIMIZER_FEATURES_ENABLE('12.1.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ -------------------------------------------------------------------------------- Plan hash value: 3900446664 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 49 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| I1 | 25000 | 75000 | 49 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."FLAG")) --> spot this: They are the same in contrast to 11g OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('12.1.0.1') OPTIMIZER_FEATURES_ENABLE('12.1.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :N (VARCHAR2(30), CSID=178): 'Y1' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FLAG"=:N) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 71 rows selected. select plan_name, enabled, accepted from dba_sql_plan_baselines; PLAN_NAME ENA ACC ------------------------------- ---- --- SQL_PLAN_3jjzm5fb07xfs3f0011ba YES YES ---> baseline is still acceptedAnd if I execute again my initial query here below what I obtain
SQL> select count(1) from t2 where flag = :n; COUNT(1) ---------- 1 SQL> select * from table(dbms_xplan.display_cursor); SQL_ID 9zr7y0b3d8h5m, child number 0 An uncaught error happened in prepare_sql_statement : ORA-01403: no data found NOTE: cannot fetch plan for SQL_ID: 9zr7y0b3d8h5m, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) SQL> select count(1) from t2 where flag = :n; COUNT(1) ---------- 1 SQL> select * from table(dbms_xplan.display_cursor); SQL_ID 9zr7y0b3d8h5m, child number 0 ------------------------------------- select count(1) from t2 where flag = :n Plan hash value: 3321871023 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 273 (100)| | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | TABLE ACCESS FULL| T2 | 25000 | 75000 | 273 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("FLAG"=:N)This is what I wanted to show in this blog. No more no less. Starting from 12c when you want to see the plan of a baseline dbms_xplan.display_plan_baseline will show you the plan as it was at the baseline creation time. While in 11g it will show you the plan as it is (or might be) at the moment you asked to see it.
Best regards
Comment by Mohamed — January 16, 2014 @ 2:51 pm |
Ignore the conjecture about you having two plans, I’ve since tested it.
But my point remains.
Think about it.
If the baseline was actually changed that defeats the whole purpose of baselines!
The point is that in 11g, DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE is unable to tell you for definite what the execution plan is in the baseline.
It only has
a) The outline hints
b) The plan id
If you look at the underlying information in SQLOBJ$, etc you will see this.
So, all DBMS_XPLAN can do – I’m guessing without a sql trace – is something like apply the hints to the SQL and see what happens.
It would be great if it then had a little note which said “I couldn’t reproduce the right plan id”.
The significant change in 12c is that the actual execution plan steps are stored as well.
So DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can show the plan which it’s meant to reproduce.
Comment by Dom Brooks — January 16, 2014 @ 3:07 pm |
Thanks Dom. This is clear. And this ” So DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can show the plan which it’s meant to reproduce” is cristal clear.
Comment by Mohamed — January 16, 2014 @ 3:29 pm
I see also that coskan has already blogged about something similar
http://coskan.wordpress.com/2011/08/13/dbms_xplan-display_sql_plan_baseline-can-also-lie/
Best regards
Comment by Mohamed — January 16, 2014 @ 2:56 pm |