Displaying SQL Baseline Plans
Since I’m on vacation and not “really” working, I thought I might have time to write up a quick blog post. The idea for this one was triggered by one of Maria Colgan’s presentations at Hotsos last week. Maria was talking about SQL Plan Management and Baselines and somehow got me thinking about the DBMS_XPLAN option to display plans for Baselines. This is a pretty neat feature that allows you to the see the plan associated with a Baseline (well sort of).
The 11.2 documentation (Oracle® Database PL/SQL Packages and Types Reference) says this about the DISPLAY_SQL_PLAN_BASELINE function:
This procedure uses plan information stored in the plan baseline to explain and display the plans.It is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.
But what does that mean? Well in short it means that Baselines don’t store plans, they store hints that when fed to the optimizer will hopefully cause it to come up with the desired plan. Baselines also store a plan_hash_value so it’s possible to tell whether the hints worked or not. Baselines do not actually store all the steps of a plan. So if that’s the case, then it’s obviously not possible for the display_sql_plan_baseline function to show the plan if the optimizer can’t reproduce it for some reason. When the doc’s say “it is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan”, that’s what they are talking about. I decided to create a test case to see what happens when the generated plan can’t match the original. Here’s the basic idea:
- run a statement that uses an index and check the plan
- create a Baseline on the statement using the index (using my create_baseline.sql script)
- check the hints stored with the baseline (using my baselines_hints.sql script)
- run the statement again and check the real plan to see that the Baseline was used
- use the display_sql_plan_baseline function to show the Baseline plan
- make the index invisible (thus rendering the Baseline plan non-reproducible)
- execute the statement again and check the real plan
- use the display_sql_plan_baseline function to show the Baseline plan
So here’s the test:
SYS@dbm1> @avgskewi
AVG(PK_COL)
-----------
Elapsed: 00:00:00.02
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
Plan hash value: 3723858078
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 33 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 32 | 352 | 33 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SKEW_COL1 | 33 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=136133)
20 rows selected.
Elapsed: 00:00:00.03
SYS@dbm1>
SYS@dbm1> -- Create the Baseline
SYS@dbm1>
SYS@dbm1> @create_baseline
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for plan_hash_value: 3723858078
Enter value for fixed (NO):
Enter value for enabled (YES):
Enter value for plan_name (ID_sqlid_planhashvalue):
Baseline SQLID_7S0B9YGCRJ77U_3723858078 created.
Elapsed: 00:00:00.08
SYS@dbm1>
SYS@dbm1> -- Check Baseline Hints
SYS@dbm1>
SYS@dbm1> @baseline_hints
Enter value for baseline_plan_name: SQLID_7S0B9YGCRJ77U_3723858078
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))
6 rows selected.
Elapsed: 00:00:00.04
SYS@dbm1> @avgskewi
AVG(PK_COL)
-----------
1 row selected.
Elapsed: 00:00:00.01
SYS@dbm1> /
AVG(PK_COL)
-----------
1 row selected.
Elapsed: 00:00:00.01
SYS@dbm1>
SYS@dbm1> -- Check to make sure statement is using the Baseline
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
Plan hash value: 3723858078
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 35 |
|* 3 | INDEX RANGE SCAN | SKEW_COL1 | 37 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=23489)
Note
-----
- SQL plan baseline SQLID_7S0B9YGCRJ77U_3723858078 used for this statement
27 rows selected.
Elapsed: 00:00:00.05
SYS@dbm1>
SYS@dbm1> -- Use display_sql_plan_baseline to see Baseline Plan
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
Enter value for sql_handle: SQL_1e2d7159fc8f7496
Enter value for plan_name: SQLID_7S0B9YGCRJ77U_3723858078
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_1e2d7159fc8f7496
SQL text: select avg(pk_col) from kso.skew where col1 = 23489
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQLID_7S0B9YGCRJ77U_3723858078 Plan id: 1416105523
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3723858078
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 36 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 35 | 385 | 36 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SKEW_COL1 | 37 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=23489)
26 rows selected.
Elapsed: 00:00:00.05
SYS@dbm1>
SYS@dbm1> -- Hide the index and rerun the statement
SYS@dbm1>
SYS@dbm1> alter index kso.skew_col1 invisible;
Index altered.
Elapsed: 00:00:00.01
SYS@dbm1> @avgskewi
AVG(PK_COL)
-----------
1 row selected.
Elapsed: 00:00:01.14
SYS@dbm1> /
AVG(PK_COL)
-----------
1 row selected.
Elapsed: 00:00:01.12
SYS@dbm1>
SYS@dbm1> -- Check the actual plan and see that the Baseline is no longer used
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
Plan hash value: 568322376
----------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 35 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1"=23489)
filter("COL1"=23489)
20 rows selected.
Elapsed: 00:00:00.02
SYS@dbm1>
SYS@dbm1> -- Use display_sql_plan_baseline to see Baseline Plan now
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
Enter value for sql_handle: SQL_1e2d7159fc8f7496
Enter value for plan_name: SQLID_7S0B9YGCRJ77U_3723858078
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_1e2d7159fc8f7496
SQL text: select avg(pk_col) from kso.skew where col1 = 23489
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQLID_7S0B9YGCRJ77U_3723858078 Plan id: 1416105523
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 568322376
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 44513 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 35 | 385 | 44513 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1"=23489)
filter("COL1"=23489)
26 rows selected.
Elapsed: 00:00:00.04
|
So in the last step you can see that the display_sql_plan_baseline function does not actually show the plan that was associated with the baseline. It can’t because the plan is not stored. So the optimizer must attempt to reproduce the plan with the hints and if the plan is not reproducible, it spits out some other plan. Note that the plan_id in the output still matches the desired plan even though the optimizer was unable to reproduce this plan.
Note: 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 point that when a plan is not reproducible the display_sql_plan_baseline function should probably just throw an error.
Kerry,
How do I create a Baseline from a sqlid in AWR and then modify with the new hints.
Thank you
Hi Rajeev,
There is no way (that I’m aware of) to just add arbitrary hints to an existing Baseline. That’s one of the advantages of SQL Profiles by the way. With SQL Profiles you can add whatever hints you want via the dbms_sqltune.import_sql_profile procedure. But you can create a Baseline on a statement that has a Profile, so if you prefer to use Baselines, you could do the following:
1. create a SQL Profile with the desired hints
2. execute the statement and verify that you got the plan you wanted
3. create a Baseline for the statement
4. drop the SQL Profile
Keep in mind that SQL Profiles require tuning and diagnostics packs. I’ve written many posts about creating SQL Profiles here so just use the search function to find them.
Another option would be to use the built-in ability of Baselines to be moved from one statement to another. The steps would be something like this:
1. Create a statement that matches the one you want to control (have a look at my bbv.sql)
2. Add the necessary hints to get the plan you want
3. Create a baseline on the modified (hinted) statement
4. Move the Baseline to original (unhinted) statement
Yet another option would be to use a SQL Patch (which don’t require any additional licenses) to add the desired hints (if there only a few) in a similar manner to approach number one with SQL Profiles and then create a Baseline on the statement.
Off the top of my head I think options 1 and 2 (SQL Profiles and SQL Patches) are a little more straight forward than option 2.
Hmmm – sounds like a good idea for a blog post and script to automate the procedure. Thanks for the idea!
Kerry
Kerry,
Could you explain the next situation (11gR2):
there are two plans in the SQL plan baselines for one sql statement.
(not different owner, not different sql, the sql statement is the same one exactly, the
same sql_id, from one oracle user and the same client application)
one of the plan is marked as enabled, the other(slow) is marked as not enabled.
(I used DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE)
But sometimes the optimizer randomly picks up for using the “not enabled” plan – why ?
At these moments I see at plan_hash value and the cursor plan display – the used
plan is identical to one in the SPB(which “not enabled”). But at that the output doesn’t
desplay the note about sql plan “SQL plan baseline … used for this statement”
The table structure, the indexes are not changed.
And no new another plan for this sql is added to SQL plan baselines during such execution.
The behaviour looks as random.
After some time(for example, after killing long runnig session whith bad plan and
starting it anew) oracle uses good plan (“enabled”) again – correct behaviour.
(I posted this question into some known oracle blogs,but it’s no answer)
Hi Andr,
Keep in mind that in 11g, baselines are strictly a set of hints. That is to say that the optimizer still has to do it’s calculations. The processing (for the situation you have described with only 1 accepted/enabled baseline) goes something like this:
Parse the statement without any hints.
If the plan the optimizer comes up with matches the existing baseline, use it.
If not, re-optimize with the hints from the accepted/enabled baseline.
If the plan the optimizer comes up with matches the baseline, use it.
Otherwise use the one the optimizer came up to begin with.
Sounds like your situation is that for some reason, even with the hints, the plan is not reproducible in some situations and therefore it falls back to using it’s normal optimization (without hints). The fact that the plan matches a plan in a non-enabled baseline has nothing to do with the non-enabled baseline (if it was being used you’d see the note about a baseline being used). The fact that the plan can not always be reproduced should be reported to Oracle with an SR.
12c stores the actual plan (instead of just hints) by the way, which should clean up some of these types of issues. So in 12c this kind of thing should only happen if a plan can no longer be re-produced such as when an index has been dropped.
Kerry
Kerry, thanks for the explanation.
According the documentation I thought that if the plan is disabled optimizer will never use this plan.
Hi Kerry,
It’s additional information about that situation (one plan is enabled, but sometimes oracle uses a non-enabled plan):
this sql statement uses rls (policies, predicates). Can it result to that behaviour ?
Unfortunetly I could’t make test with such result.
Yes any statement that cannot reproduce a plan stored in a baseline using the hints (for any reason) can fall back to normal optimizer behavior – meaning the plan stability feature is basically turned off.
Kerry
Fantastic blog post. Really Cool.
I ran into an issue where a particular SQL statement would generate a poor execution plan in only one of our data warehouse databases. It ran in 20 secs in dev, sit and prod; but took over 8 hrs in uat. I copied the good SQL baseline from prod to uat; but it would still fall back to the bad plan. The root cause ended up being someone had changed the DOP for a few indexes used in the SQL; but only changed them in uat. Once I re-synced the DOP for the indexes to match the other environments it started generating the correct plan again.