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
No, I will just drop the index and get the execution plan of the already existing sql_plan_baseline
And now I repeat exactly the same thing in 12c and here below what I have
In Presence of the index
In the absence of the index
And if I execute again my initial query here below what I obtain
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 |