Oracle 11g SQL Plan Management – SQL Plan Baselines
Note, this post applies to 11gR1 (specifically 11.1.0.7).
11g has a new method of dealing with plan instability (i.e. the tendency of a statement to flip flop between plans). The goal has always been to provide the best performance, but until 11g there has not been a mechanism that had as a design goal, the idea of eliminating backwards movement (“performance regressions” as the Oracle documentation calls them). That is, not allowing a statement to switch to a plan that is significantly slower than the one it has already been executing. This new mechanism depends on a construct called a Baseline. So for this post I am not going to focus on the process of evolving plans to prevent “performance regressions”, but rather on the Baselines themselves. (hopefully I’ll get to the plan management part soon)
Here’s a little excerpt from the 11gR1 documentation:
Each time a SQL statement is compiled, the optimizer first uses a cost-based search method to build a best-cost plan, then tries to find a matching plan in the SQL plan baseline. If a match is found, the optimizer will proceed using this plan. Otherwise, it evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost. The best-cost plan found by the optimizer that does not match any plans in the plan history for the SQL statement represents a new plan, and is added as a non-accepted plan to the plan history. The new plan is not used until it is verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, the optimizer will select the best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL statement.
Clear as mud?
Now my description:
Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. The previous efforts were Outlines and SQL Profiles. Both of those efforts were based on the idea that hints could be applied to limit the optimizers choices down to one. That approach seems a little flawed. Why not just save the plan and be done with it? I believe that’s what Baselines are intended to do. Unfortunately, they don’t appear to do it yet. But they do have an advantage over Outlines and SQL Profiles in that they at least save the plan_hash_value, so they know if they are reproducing the correct plan or not. (see my previous post, Do Baselines Use Hints? – Take 2) And hopefully, they will evolve to the point where the actual plan is saved along with the hash value – we’ll see.
So anyway, here’s some basic housekeeping stuff on Baselines:
- Baselines will be used by default in 11gR1, if they exist. There is a parameter to control whether they are used or not (OPTIMIZER_USE_SQL_PLAN_BASELINE). It is set to TRUE by default.
- Baselines will not be created by default in 11gR1. So, much like with the older Outlines or SQL Profiles, you must do something to create them.
- There is a view called DBA_SQL_PLAN_BASELINES that exposes the Baselines that have been created.
- Just like Outlines and SQL Profiles, Baselines apply to all instances in a RAC environment (they are not localized to a specific instance).
Baselines can be created a couple of different ways. They can be created automatically by the database as it’s running by setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter. They can also be created for statements in a SQL Tuning Set. But I’m not going to talk about either one of those approaches in this post, as I don’t think they really make much sense in the case where you want to use a Baseline to lock a plan for a specific statement. Instead, I am going to discuss creating a baseline for a statement that is in the shared pool via the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function.
All the function needs is a SQL_ID and a PLAN_HASH_VALUE. Optionally a parameter can be used to define the baseline as FIXED. If it’s FIXED then it gets priority over any other Baselines for that statement, except other FIXED Baselines. Confused? Well it’s not exactly the most straight forward setup. I’m kind of a simple guy, so at this point I’m thinking one FIXED Baseline is enough.
Before you get too bored, here’s a quick example:
SQL> select avg(pk_col) from kso.little_skew where col1 = 99320;
AVG(PK_COL)
-----------
628922
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
4dd2qjxbjav6u 0 2709260180 1 .02 89 select avg(pk_col) from kso.little_skew where col1 = 99320
SQL> @dplan
Enter value for sql_id: 4dd2qjxbjav6u
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4dd2qjxbjav6u, child number 0
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 = 99320
Plan hash value: 2709260180
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LITTLE_SKEW | 1 | 9 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | LITTLE_SKEW_COL1 | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=99320)
20 rows selected.
SQL> !cat create_baseline.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
sql_id=>'&sql_id', -
plan_hashvalue=>&plan_hash_value,-
fixed=>'&fixed');
SQL> @create_baseline
Enter value for sql_id: 4dd2qjxbjav6u
Enter value for plan_hash_value: 2709260180
Enter value for fixed: NO
PL/SQL procedure successfully completed.
SQL> !cat baselines.sql
set lines 155
col sql_text for a50 trunc
col last_executed for a28
col enabled for a7
col plan_hash_value for a16
col last_executed for a16
select spb.sql_handle, spb.plan_name, spb.sql_text,
spb.enabled, spb.accepted, spb.fixed,
to_char(spb.last_executed,'dd-mon-yy HH24:MI') last_executed
from
dba_sql_plan_baselines spb
where spb.sql_text like nvl('%'||'&sql_text'||'%',spb.sql_text)
and spb.sql_handle like nvl('&name',spb.sql_handle)
and spb.plan_name like nvl('&plan_name',spb.plan_name)
/
SQL> @baselines
Enter value for sql_text: %little%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_b77256b604589dec SYS_SQL_PLAN_04589dec2fdfd157 select avg(pk_col) from kso.little_skew where col1 YES YES NO
SQL> select avg(pk_col) from kso.little_skew where col1 = 99320;
AVG(PK_COL)
-----------
628922
SQL> select avg(pk_col) from kso.little_skew where col1 = 99320;
AVG(PK_COL)
-----------
628922
SQL> @find_sql
Enter value for sql_text:
Enter value for sql_id: 4dd2qjxbjav6u
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
4dd2qjxbjav6u 0 2709260180 1 .02 89 select avg(pk_col) from kso.little_skew where col1 = 99320
4dd2qjxbjav6u 1 2709260180 1 .00 5 select avg(pk_col) from kso.little_skew where col1 = 99320
SQL> @dplan
Enter value for sql_id: 4dd2qjxbjav6u
Enter value for child_no: 1
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4dd2qjxbjav6u, child number 1
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 = 99320
Plan hash value: 2709260180
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LITTLE_SKEW | 1 | 9 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | LITTLE_SKEW_COL1 | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=99320)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_04589dec2fdfd157 used for this statement
24 rows selected.
So that’s a quick example of how easy it is to create a Baseline. Once a Baseline is created it behaves much like an Outline or SQL Profile, in that it will be applied to any SQL statement where the normalized text matches. It’s interesting to note that even though Outlines are way outdated in 11g, they still take precedence over Baselines. So if you have an Outline and a Baseline on the same statement, the Outline will be used. Here’s an example of that behavior:
SQL> @outlines
Enter value for category:
Enter value for name:
CATEGORY NAME USED ENABLED HINTS SQL_TEXT
--------------- ------------------------------ ------------ ------------ ------ ----------------------------------------------------------------------
DEFAULT KSOTEST1 USED ENABLED 6 select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
SQL> select /* test1 */ avg(pk_col) from kso.skew where col1 > 0;
AVG(PK_COL)
-----------
16093749.3
SQL> @find_sql
Enter value for sql_text: select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
0r09xac72gvj1 0 568322376 3 9.97 162,297 select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
SQL> @dplan
Enter value for sql_id: 0r09xac72gvj1
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0r09xac72gvj1, child number 0
-------------------------------------
select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44497 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 32M| 335M| 44497 (2)| 00:08:54 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
Note
-----
- outline "KSOTEST1" used for this statement
23 rows selected.
SQL> @baselines
Enter value for sql_text: %test1%
Enter value for name:
Enter value for plan_name:
no rows selected
SQL> @create_baseline
Enter value for sql_id: 0r09xac72gvj1
Enter value for plan_hash_value: 568322376
Enter value for fixed: YES
PL/SQL procedure successfully completed.
SQL> @baselines
Enter value for sql_text: %test1%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_661b59bf2029dc56 SYS_SQL_PLAN_2029dc5655381d08 select /* test1 */ avg(pk_col) from kso.skew where YES YES YES
SQL> select /* test1 */ avg(pk_col) from kso.skew where col1 > 0;
AVG(PK_COL)
-----------
16093749.3
SQL> @baselines
Enter value for sql_text: %test1%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_661b59bf2029dc56 SYS_SQL_PLAN_2029dc5655381d08 select /* test1 */ avg(pk_col) from kso.skew where YES YES YES
SQL> -- note that the last_executed column is still blank, so it wasn't used
SQL>
SQL> @find_sql
Enter value for sql_text: %test1%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
0r09xac72gvj1 0 568322376 4 10.00 162,297 select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
SQL> - -and the existing cursor is still there with one more execution
SQL> -- just to verify let's check the plan
SQL>
SQL> @dplan
Enter value for sql_id: 0r09xac72gvj1
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0r09xac72gvj1, child number 0
-------------------------------------
select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44497 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 32M| 335M| 44497 (2)| 00:08:54 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
Note
-----
- outline "KSOTEST1" used for this statement
23 rows selected.
SQL> -- so Outlines do take precedence over Baselines
SQL> -- let's try disabling the Outline
SQL>
SQL> @outlines
Enter value for category:
Enter value for name:
CATEGORY NAME USED ENABLED HINTS SQL_TEXT
--------------- ------------------------------ ------------ ------------ ------ ----------------------------------------------------------------------
DEFAULT KSO1 USED ENABLED 6 select /*+ full(a) */ avg(pk_col) from kso.little_skew a where col1 =
DEFAULT KSOTEST1 USED ENABLED 6 select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
SQL> alter outline &outline_name disable;
Enter value for outline_name: KSOTEST1
Outline altered.
SQL> select /* test1 */ avg(pk_col) from kso.skew where col1 > 0;
AVG(PK_COL)
-----------
16093749.3
SQL> @find_sql
Enter value for sql_text: %test1%
Enter value for sql_id:
no rows selected
SQL> -- quirky behavior - no record of the first execution with the baseline ???
SQL> -- let's try it again
SQL>
SQL> select /* test1 */ avg(pk_col) from kso.skew where col1 > 0;
AVG(PK_COL)
-----------
16093749.3
SQL> @find_sql
Enter value for sql_text: %test1%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
0r09xac72gvj1 0 568322376 1 9.57 162,297 select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
SQL> -- Now it's here but shows only one execution (not sure what happened on the first execution)
SQL> -- Let's check the plan
SQL>
SQL> @dplan
Enter value for sql_id: 0r09xac72gvj1
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0r09xac72gvj1, child number 0
-------------------------------------
select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44497 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 32M| 335M| 44497 (2)| 00:08:54 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_2029dc5655381d08 used for this statement
23 rows selected.
SQL> -- using the Baseline now
SQL> @baselines
Enter value for sql_text: %test1%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_661b59bf2029dc56 SYS_SQL_PLAN_2029dc5655381d08 select /* test1 */ avg(pk_col) from kso.skew where YES YES YES 13-apr-09 10:31
SQL> -- and the Baseline table now shows the last execution time as expected
So being able to create a Baseline on an existing statement is useful, but being able to create a Baseline on one statement, and then attach it to a different statement is awesome. That gives us the ability to manipulate the text of the SQL statement in order to get the plan we want. Outlines can be tricked into doing something similar by swapping hints between statements. SQL Profiles also can do something similar by using a trick to load the hints from one statement into another (see my previous post for an example). But Baselines have the built-in ability to do this. No tricks, no sneaky stuff, just call the procedure directly and it’s done. Of course the optimizer will have to verify that the plan will work for the statement you attach it to. If it fails this validation step, then the optimizer will ignore it and go on about it’s normal business. Here’s an example:
SQL> @flush_pool
System altered.
SQL> select avg(pk_col) from kso.little_skew where col1 > 0;
AVG(PK_COL)
-----------
320093.5
SQL> @find_sql
Enter value for sql_text: %avg%little%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
a2h75xrkn1xh3 0 3498336203 1 .26 3,366 select avg(pk_col) from kso.little_skew where col1 > 0
SQL> @dplan
Enter value for sql_id: a2h75xrkn1xh3
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a2h75xrkn1xh3, child number 0
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 > 0
Plan hash value: 3498336203
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 907 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| LITTLE_SKEW | 640K| 6251K| 907 (2)| 00:00:11 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
19 rows selected.
SQL> select /*+ index (a LITTLE_SKEW_COL1) */ avg(pk_col) from kso.little_skew a where col1 > 0;
AVG(PK_COL)
-----------
320093.5
SQL> @find_sql
Enter value for sql_text: %avg%little%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
3trqfzj8yhu6j 0 2709260180 1 2.53 580,280 select /*+ index (a LITTLE_SKEW_COL1) */ avg(pk_col) from ks
o.little_skew a where col1 > 0
a2h75xrkn1xh3 0 3498336203 1 .26 3,366 select avg(pk_col) from kso.little_skew where col1 > 0
SQL> @create_baseline
Enter value for sql_id: a2h75xrkn1xh3
Enter value for plan_hash_value: 3498336203
Enter value for fixed: NO
PL/SQL procedure successfully completed.
SQL> @baselines
Enter value for sql_text: select avg(pk_col) from kso.little_skew where col1 > 0
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_6560e8852671e3e3 SYS_SQL_PLAN_2671e3e38b1420d2 select avg(pk_col) from kso.little_skew where col1 YES YES NO
SQL> !cat create_baseline2.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
sql_id=>'&sql_id', -
plan_hashvalue=>&plan_hash_value,-
sql_handle=>'&sql_handle',-
fixed=>'&fixed');
SQL> @create_baseline2
Enter value for sql_id: 3trqfzj8yhu6j
Enter value for plan_hash_value: 2709260180
Enter value for sql_handle: SYS_SQL_6560e8852671e3e3
Enter value for fixed: YES
PL/SQL procedure successfully completed.
SQL> @baselines
Enter value for sql_text: select avg(pk_col) from kso.little%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_6560e8852671e3e3 SYS_SQL_PLAN_2671e3e32fdfd157 select avg(pk_col) from kso.little_skew where col1 YES YES YES
SYS_SQL_6560e8852671e3e3 SYS_SQL_PLAN_2671e3e38b1420d2 select avg(pk_col) from kso.little_skew where col1 YES YES NO
SQL> -- since we made the one we added a fixed plan, it will take precedence
SQL>
SQL>
Baselines Do Have a Few Quirks:
- The first execution of a statement using a Baseline appears to be immediately discarded from the shared pool (see the examples above). Not that big of deal since it’s only the 1st execution after the Baseline is created, but it’s a little weird.
- If a FIXED Baseline cannot be validated, NON-FIXED Baselines will NOT be used. That is to say, if a FIXED Baseline exists, and it cannot be validated, any NON-FIXED Baselines will be ignored. Instead, the normal optimizer costing calculations will kick in to create a new plan. (By the way, validation includes checking to make sure that the plan_hash_value arrived at by applying the hints associated with the Baseline matches the original plan_hash_value. Therefore, a different plan than the original cannot be used – unlike with Outlines and SQL Profiles).
- The LAST_EXECUTED field in DBA_SQL_PLAN_BASLINES appears to be updated somewhat inconsistently, so I wouldn’t rely on it.
- DBA_SQL_PLAN_BASELINES has a PLAN_ID field, but is not the same as the one in V$SQL. It would be handy to be able to relate the 2 plan hash values, but I haven’t figured out a way to do that yet (any ideas?).
- You can rename a Baseline. But then it won’t work! ???? Here’s an example:
SQL> @baselines
Enter value for sql_text: %test1%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_661b59bf2029dc56 SYS_SQL_PLAN_2029dc5655381d08 select /* test1 */ avg(pk_col) from kso.skew where YES YES YES
SQL> !cat alter_baseline.sql
-- Used to set attributes
--
-- FIXED - YES/NO
-- ENABLED - YES/NO
-- PLAN_NAME - doesn't work in 11.1.0.7
var ret number
exec :ret := dbms_spm.alter_sql_plan_baseline(-
sql_handle=>'&sql_handle',-
plan_name=>'&plan_name',-
attribute_name=>'&attribute_name',-
attributevalue=>'&attribute_value');
SQL> @alter_baseline
Enter value for sql_handle: SYS_SQL_661b59bf2029dc56
Enter value for plan_name: SYS_SQL_PLAN_2029dc5655381d08
Enter value for attribute_name: PLAN_NAME
Enter value for attribute_value: TEST1
PL/SQL procedure successfully completed.
SQL> @baselines
Enter value for sql_text: %test1%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_661b59bf2029dc56 TEST1 select /* test1 */ avg(pk_col) from kso.skew where YES YES YES
SQL> select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
2 /
select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
*
ERROR at line 1:
ORA-38141: SQL plan baseline SYS_SQL_PLAN_2029dc5655381d08 does not exist
SQL> -- error caused by renaming a baseline
SQL> -- so it lets you rename them, but then the statements fail
So Baselines seem to be a definite step in the right direction. Although I must say that at this point they are not very clearly documented. It does appear from looking at 10053 trace data that the complete optimization path is carried out, even though the optimizer recognizes that a Baseline exists for the statement. It seems like a Fixed Baseline would short circuit this process and just say, “I have a plan, let’s use it”. But it appears that the optimizer wants to evaluate other plans, probably in an attempt to add other opportunities to evolve a better plan.
I haven’t found a lot of information on Baselines on the web, but there is a pretty good post on them on the OptimizerMagic blog.
awesome write-ups.
I much enjoyed them because it was on my list of tasks to look at using the old outline swap to use hints on a statement for profiles and baselines. Baselines seem like a solid solution for most statements when hints are beneficial because of the evolve option.
Curious if there is any reason you see to use profiles over outlines.
Kyle,
Thanks for the kind words. Interesting question. I had an online dialog with a guy name Randolf Geist a year ago or so where he convinced me to look closer at SQL Profiles. Up until that point I had only seen SQL Profiles that were created by the SQL Tuning Advisor and I was not very impressed. They tended to go sour after a little time because of the OPT_ESTIMATE hint, but I digress. The answer is yes, I think SQL Profiles have a couple of advantages over Outlines at this point.
1. They have the ability to be used on multiple statements (ones that are the same except for literals) using the force matching signature bit.
2. They are newer. I’m starting to worry about Outlines getting buggy due to lack of attention. Documentation says they are deprecated and although they continue to work in 11g, I am a bit leery of continuing to use them at this point. (the whole hint based mechanism is tricky enough as it is).
11gR2 includes a procedure to migrate Outlines to Baselines by the way (DBMS_SPM.MIGRATE_STORED_OUTLINE). At any rate, I am not implementing Outlines at this point.
Kerry
Hi,
Thank you for the post. I’ve been looking for a way to pin a one baseline to one SQL and finally found it today with your post!
I do have a question though.
regarding your comment about SQL Profiles: “They have the ability to be used on multiple statements (ones that are the same except for literals) using the force matching signature bit.”
Do baselines have this same nice feature that SQL Profiles have (the forced matching thing)? We are using PeopleSoft so there are way more literals than bind variables, unfortunately, and the “force match” feature of SQL Profiles has been nice. However, SQL Profiles do sour over time, as you said, and that has been very problematic for us, which is why I’m looking into baselines.
Thanks,
Laurie
Hi Laurie,
Baselines do not have the ability to be applied to multiple statements. That is a feature of SQL Profiles only. The Profiles that tend to sour over time are the ones created by the SQL Tuning Advisor which use the opt_estimate hint under the covers. You can also generate a SQL Profile with your own hints (like a Baseline). I have written and spoken about this quite a bit. Please have a look at my Controlling Execution Plans (Without Touching the Code) 2014 slides.
Also this post might be of use:
How to Lock SQL Profiles Generated by SQL Tuning Advisor
Kerry
I checked all Oracle documentation for the following error:
ORA-38141: SQL plan baseline SYS_SQL_PLAN_2029dc5655381d08 does not exist
I have verified that this baseline plan does not exist.
Could you please let me know how to resolve this error?
That error occurred when I altered the baseline (changing it’s name) and then tried to execute a statement which was trying to use the baseline. So, if I understand your question, why might you see this in the wild, I would guess that someone altered the baseline changing it’s name. To find which baseline has been changed you might look for baselines with unusual names, or ones that have been modified recently (see last_modified column in dba_sql_plan_baselines).
Kerry
Hello Osborne, I am using Oracle Database 11.1.0.7 on AIX
We have a congnos application installed and this morning they got the following error in their application log. This is turning out to be a show stopper.
Error Messages from Cognos:
Unable to execute the statement.
The SQL was:
SELECT object_name FROM sys.all_objects WHERE (object_type = ‘TABLE’) AND (owner = ‘MYSCHEMA’)
ORA-38141: SQL plan baseline SYS_SQL_PLAN_95dde1aac49074c9 does not exist
When I cehck the DBA_SQL_PLAN_BASELINE, there is no SQL Handel like SYS_SQL_PLAN_95dde1aac49074c9 and this confirms the message is right.
But when I run the same query –
SELECT object_name FROM sys.all_objects WHERE (object_type = ‘TABLE’) AND (owner = ‘MYSCHEMA’);
it works fine but when it runs through cognos, it throws the above mentioned error message. Not sure why it is looking for this baseline when it’s not there in the table itself.
I don’t think anybody changed the PLAN_NAME as all the plan name looks like as if it was generated by Oracle (SYS_SQL_PLAN_xxxxxxxxxxxxxxxx).
Thanks in advance for giving your valuable time to this mystery.
Sorry – I don’t know the answer to that one – better open an SR
Kerry
I have the same issue as RK except with Toad and I upgraded the database itself from 11.1.0.7 to 11.2.0.2. ORA-38141 seemed to pop-up after that.
Also I noticed all plan names began with “SYS_SQL_PLAN” but a few of them started with “SQL_PLAN_”. When and why would the names be different? I deleted the few plans that started with “SQL_PLAN_” and the ORA-38141 errors disappeared.
[…] Osborne 1. Do SQL Plan Baselines use hints (pt.1) 2. Do SQL Plan Baselines use hints (pt. 2) 3. SQL Plan Management – SQL Plan Baselines 4. Why isn’t Oracle using my outline/ SQL profile / SQL Plan Baseline 5. Dynamic Sampling and […]
Thanks for what I’m sure will be a useful post. Your other posts re sql profiles and about unstable plans have been very useful to me.
DBA_ADVISOR_SQLSTATS has both plan_id and plan_hash_value columns. I haven’t tried it, so this is a wild guess, but maybe if you created a plan baseline for your query that would give it a plan_id, and then if you ran sql tuning advisor on your query you’d end up seeing both columns populated in dba_advisor_sqlstats. Just maybe.
Thanks Paul,
I’ll give that a look when I have a few minutes.
Kerry
Kerry,
Above you say “I’m starting to worry about Outlines getting buggy due to lack of attention.”. It seems though that SPM is using outlines to (re)generate the plans. The data in sys.sqlobj$data.comp_data is basically an outline. It has the same info you would get if you run “select * from table(dbms_xplan.display_cursor(‘&sqlid’,&child_number,’ADVANCED’))” and look at the outline data.
Thought that was interesting… Maybe you don’t need to worry 🙂
Jan
Jan,
Yeah, it’s very closely related. The Outline object consists of a set of hints that is applied to a statement and is similar to a SQL Profile and a Baseline. But they are stored in different ways and presumably use different code paths (although some of the code may be the same). At any rate, Outlines have been documented as deprecated since 11.1 (from the 11gR2 Database Upgrade Guide – “The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1).”) I’d definitely prefer to use SQL Profiles or Baselines at this point.
The ADVANCED option on dbms_xplan.display_cursor spits out the content of the OTHER_XML field in V$SQL_PLAN. It is the set of hints that Oracle thinks are necessary to recreate the plan. I’ve written about that somewhere on this blog.
Kerry
[…] to Kerry Osborne again, and his article about SQL Plan Baselines. He (and others) write in detail about how and what SQL Plan Baselines are, but in essence it lets […]
[…] the stored SPB http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints-take-2/ http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/ GA_googleAddAttr("AdOpt", "1"); GA_googleAddAttr("Origin", "other"); GA_googleAddAttr("theme_bg", […]
Hi Kerry,
here’s the connex between dba_sql_plan_baselines and v$sql:
select s.*, b.*
from dba_sql_plan_baselines b,
v$sql s
where s.exact_matching_signature = b.signature
and s.sql_plan_baseline = b.plan_name;
Thx 4 your great Blog.
Hi Kerry, does a stored outline still take precedence over baselines in Oracle 11gR2? Thanks.
Yes,
Outlines override Baselines even in 11gR2 (at least as of 11.2.0.2).
Thanks Kerry. I did a similar test in 11.2.0.2. It is true that outlines still overrides baselines as of this version.
[…] Oracle 11g SQL Plan Management – SQL Plan Baselines […]
hi kerry
we recently implemented a sql plan plan to fix a particular sql plan – first time i have used the package.
the method used to identify the statement was the usual scan of v$sqlarea ordered by buffer_gets/disk_reads/elapsed-time etc
got the sql_id, load it into sql-base-plan using dbms_spm.load_plan_from_cursor_cache(sql_id=>’….’)
the change seems to have been effective, but the activity is now in stealth mode – i can no longer find the original sql statement executions recorded in v$sqlarea.
i can also see that the dba_sql_base_lines view records usage of the plan.
so, basically – what happened to the v$sql trail – is it recorded somewhere else, or simply not recorded?
mr
Perhaps it’s not run very often and since it performs well now it ages out of the shared pool fairly quickly. You could try checking AWR to see if it’s been captured recently. I use a script called awr_plan_change.sql for this, which I talked about here: Unstable Plans But if it performs really well it may not show up there even if it’s run quite a bit because it just doesn’t make it to the top of the list in any of the categories AWR monitors. AWR has the ability to designate specific statements for capture, so if you don’t find any recent snaps that have captured it, you could try “coloring” the SQL_ID for AWR.(See Dion Cho’s post on this feature for more info). If that doesn’t work then I’m not sure what to tell you.
Kerry
Hi Kerry
This is a very useful site. I have used it to change execution plan for few SQL statements that were taking long to run.
I have a typical scenario though. I have a SQL that has “bad” execution plan. It has a literal that changes from one run to another (RUN_ID), everything else being same. This poses a challenge as the SQL_ID is different every time. So even if I try to baseline good plan, next time when the application runs, it would have a different SQL_ID and will completely ignore the baseline. Please advise how to tackle this issue.
Thanks
The capability you asked about is a feature that Baselines don’t have. But have a look at SQL Profiles. One of the options available to manually created SQL Profiles is to match all statements that differ only by the use of literals. This is accomplished by setting the force_match parameter of dbms_sqltune.import_sql_profile to true (and it uses the force_matching_signature in v$sql under the covers). Note that it won’t work for statements that have both literals and bind variables. I have written quite a bit about SQL Profiles on this blog. Use the search box to find them. Kaen Morton also wrote about force_matching_signature here: http://karenmorton.blogspot.com/2012/05/force-matching-signature.html
In case you don’t want to do any more reading but just want to try it, here is a link to a little script to create a manual SQL Profile on an statement that resides in the shared pool: (create_sql_profile.sql).
Hope that helps.
Kerry
Many thanks for such a swift response. I will look into this.
Thank you for such an insightful post , However I had a query regarding Baselines … What about a query that runs for both a very small amount of data and a very large amount of data on different days of a month.If the baseline is set for large amount of data then when we run for a small amount of data it will take minutes instead of seconds. The other way around (set the baseline for small and then run large) could make the large query go from hours to days. Is this possible and if it is so , is there anyway that this can be avoided ?
Hi Azhar,
Yes – baselines can contain more than one plan. So in a case where the optimizer wants to pick different plans for large data set vs for small data set, both plans can be captured and added to the baseline and enabled. Then, hopefully the optimizer will pick the correct plan, which will match one of the enabled plans in the baseline. This works well in many/most cases. Key is to enable more than one plan.
Kerry
Hello,
thank you very much for this and other articles.
Could you please describe more precisely the following point:
* If a FIXED Baseline cannot be validated, NON-FIXED Baselines will NOT be used. That is to say, if a FIXED Baseline exists, and it cannot be validated, any NON-FIXED Baselines will be ignored. Instead, the normal optimizer costing calculations will kick in to create a new plan. (By the way, validation includes checking to make sure that the plan_hash_value arrived at by applying the hints associated with the Baseline matches the original plan_hash_value. Therefore, a different plan than the original cannot be used – unlike with Outlines and SQL Profiles).
e.g. more on process of validation ( evaluation from unaccepted to accepted ? ),
NON-FIXED Baselines’ plan_hash_value, original and new plan_hash_value ?
Best regards
Srki
I haven’t tested this in a long time but at one point, declaring a baseline plan as “fixed” had the affect of disabling other “non-fixed” baseline plans from consideration. I covered the algorithm here:
SQL Plan Management (SPM) for 12c
(it included enhancements in 12c baselines, but 95% is all the same as 11g).
Kerry
Hi Kerry,
We are in the process of upgrading our database from 11g R1 (11.1.0.7) to 11g R2 (11.2.0.4). It will be out of place upgrade and data will be copied using data pump. I have copied all the sql plan baselines from 11g R1 to 11g R2. However, this particular SQL is not using the plan baseline. It runs fine in 11g R1 and has been using plan baseline, but 11g R2 database is not using it. The only difference I found is SQL_handle has a prefix SYS in 11g R1 but not in 11g R2. I cannot understand why is it not using the plan from baseline? The force_matching_signature of the sql (from v$sql) is same as that of signature in dba_sql_plan_baselines. Could you explain what could be the reason for it to not use the plan from baseline? I have even fixed the plan.
Thanks
Wolfgang it! (10053 trace) Search in the trace file for SPM and you’ll find out if it is kicking in at all (probably it is). Remember that Baselines are a bit weird in that they know what plan_hash_value they are trying to recreate. If the plan_hash_value doesn’t match, all the hints are thrown away and the optimizer goes with standard optimization. So most likely, the baseline is kicking in, but the plan is not reproducible for some reason, so the xplan output says nothing about the fact that the baseline was even attempted. Again, look for more info in the 10053 trace file. SQL Profiles don’t have that characteristic by the way, they apply the hints and use whatever plan they come up with. Let me know what you find out.
Kerry
10053 trace file shows following, I guess it saved the reference to DB version (source database where the plan is copied from). So looks like it does attempt but failed to use it. Any advise?
SPM: failed to reproduce the plan using the following info:
parse_schema name : SYSADM
plan_baseline signature : 11472508426680117950
plan_baseline plan_id : 2004844254
plan_baseline hintset :
hint num 1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num 2 len 37 text: OPTIMIZER_FEATURES_ENABLE(‘11.1.0.7’)
hint num 3 len 22 text: DB_VERSION(‘11.1.0.7’)
hint num 4 len 42 text: OPT_PARAM(‘_bloom_filter_enabled’ ‘false’)
hint num 5 len 53 text: OPT_PARAM(‘_optimizer_enable_extended_stats’ ‘false’)
hint num 6 len 8 text: ALL_ROWS
hint num 7 len 22 text: OUTLINE_LEAF(@”SEL$2″)
hint num 8 len 22 text: OUTLINE_LEAF(@”SEL$3″)
hint num 9 len 22 text: OUTLINE_LEAF(@”SEL$4″)
hint num 10 len 22 text: OUTLINE_LEAF(@”SEL$1″)
hint num 11 len 23 text: OUTLINE_LEAF(@”SEL$10″)
hint num 12 len 29 text: OUTLINE_LEAF(@”SEL$A9DBB1CB”)
hint num 13 len 29 text: OUTLINE_LEAF(@”SEL$3512B053″)
hint num 14 len 29 text: OUTLINE_LEAF(@”SEL$3C459230″)
hint num 15 len 29 text: OUTLINE_LEAF(@”SEL$D67CB2D2″)
hint num 16 len 29 text: OUTLINE_LEAF(@”SEL$D67CB2D3″)
hint num 17 len 22 text: OUTLINE_LEAF(@”SEL$5″)
hint num 18 len 29 text: OUTLINE_LEAF(@”SEL$4DFE2BB3″)
Thanks
Doesn’t look like that’s all the hints. The DB_VERSION hint is probably not the problem. Compare the plans and see if you can tell what changed. Most common reasons for non-reproducible plans would be missing indexes or perhaps a feature that was enabled by default in 11.1 that is not enabled by default in 11.2. Creating a SQL Profile may give you more stability as they don’t throw the whole set of hints out if the plan_hash_value doesn’t match. So it may get you very close to same plan (close enough that performance doesn’t suffer) even though the plan is not exactly the same.
Kerry
Hi Kerry
Sorry my mistake, I thought the section I sent might help you. I can send you the good and the bad plan, 10053 trace file along with the query? I have checked as much as I could. The bad plan is consistently blowing temp tablespace (128 GB). I am under pressure to get to the bottom of this issue, so any help would greatly help.
Many thanks
I’d try using a SQL Profile first. Here’s a link to a post to look at on how to do that. Tuning Paramon. That post has a script called coe.sql with an example of how to use it. The script will a create text file with a SQL Profile definition which can be moved from one system to another. So I’d drop the baseline, create the script on the old system, move it over to the new system, and run it to create the Profile. Hopefully that will work for you.
Kerry
Bingo. That worked like a treat. I would still like to understand as to why it didn’t use the plan from baseline. In the past I have copied plans numerous times and it has always worked, only difference being they were all 11.1.0.7.
Thanks for your help.
Cool.
Baselines are just a set of hints and a plan_hash_value. If the hints don’t cause the optimizer to pick the same plan (plan_hash_value) then all the hints are thrown out and the optimizer goes with it’s default optimization. Profile’s don’t have a plan_hash_value, they just apply the hints and use whatever the optimizer comes up with using the hints. Using hints is not fool proof. Sometimes they don’t result in the same plan. Profile’s can work better in those corner cases because they may result in a plan that’s at least close.
Feel free to send me plans (11.1, 11.2 and what you got with the Profile). I’ll see if I can shed any more light on the situation.
Kerry
kerry.osborne@enkitec.com
Hi Kerry,
We have a similar situation.
We have a SQL statement that has different executition plans historically. We did create an SQL profile for that statement and loaded one “good” execution plan from cache into SPM and even marked that baseline as FIXED. That “good” execution plan used the SQL profile. Later the fixed baseline was used only once and then it was no longer used. The SQL statement starts to use new different execution plans.
We also notice that the SQL tuning advisor is also automatically generating new, unaccepted baselines for this SQL statement.
In our case, what should we do? Should we consider using only SQL profile for this SQL statement? How can we ensure plan stability for this SQL statement?
Thanks!
Same story as my previous comment. Profiles just apply hints, baselines apply hints and if the PHV is not reproduced it throws the hints out and optimizes without them. The set of hints may not be sufficient to guarantee the same plan is reproduced, so in that case, Profile may be better because it may at least get close enough to the plan you want to get reasonable performance.
Kerry
[…] Back to Kerry Osborne again, and his article about SQL Plan Baselines. He (and others) write in detail about how and what SQL Plan Baselines are, but in essence it lets […]
Where is the source code for baselines.sql and create_baseline.sql?
I always just use the search box at the top right – you can just type in the name of the script. But for ease here are direct links:
http://kerryosborne.oracle-guy.com/scripts/baselines.sql
http://kerryosborne.oracle-guy.com/scripts/create_baseline.sql
Kerry
Hi Kerry
I read this article with great interest and had hoped it would solve a performance problem I’m seeing in one of my Oracle databases. Basically the application is running a piece of SQL that queries a couple of views based on a ‘COMPONENT_ID’ that uses a bind variable. Most of the time the query runs OK and returns the expected results, however, occasionally the optimizer uses a horrendous plan than causes the application to freeze and nothing gets returned.
I’ve checked the SQL ID’s for both the ‘good’ and the ‘bad’ queries and they are identical however the PLAN HASH values differ. Based on your article I created a SQL Plan Baseline for the ‘good’ sql and set it to FIXED.
I cleared the cursor cache and then ran a test where I knew the result should return OK, sure enough it did. I ran another test where I knew that previously the ‘bad’ plan was executed. I was fully expecting this to work and for the optimizer to use the stored baseline, however the baseline was ignored and the same ‘bad’ execution plan was used causing the application to freeze.
I’ve spent hours looking for the reason why the optimizer would choose to ignore the stored baseline for this particular SQL statement but have not been able to find anything.
Have you ever witnessed similar behaviour and give me some idea how I can force the optimizer to use the baseline over a suboptimal plan.
Regards
Neil
Hi Neil,
I’d try a SQL Profile if you have tuning pack license. Remember Baselines and Profiles are just set of hints that may or may not be sufficient to generate an exact plan match. Baselines throw all hints out if the plan_hash_value doesn’t match and goes with default optimization with no hints applied. Profiles apply all hints and use whatever plan is arrived at with the hints. So in some cases a Profile will get very close to the plan you want (and it preforms well) even if the plan is not an exact match to the original, while a Baseline will appear to not work at all. I have a script on the blog for creating a SQL Profile from an existing cursor. Just search for create_sql_profile.sql in the search box. Hope that helps.
Kerry
[…] Osborne – Oracle 11g SQL Plan Management, SQL Plan […]
Hi Kerry,
I am a beginner to the Performance tuning field and while searching for articles about baseline, found your blog. It was extremely useful for me to understand the concepts of baseline. But I have a small confusion about the following statement:
“Baselines will apply the hints and if the optimizer gets the plan it was expecting, it uses the plan. If it doesn’t come up with the expected plan, the hints are thrown away and the optimizer tries again (possibly with the hints from another accepted Baseline).”
What will happen if a baseline has ONLY one accepted plan associated with it and Optimizer is not able to reproduce that same plan_hash_value for the given statement?
As per my understanding from your article, It will generate a new execution plan, with no hints applied, and will store this plan as non-accepted plan within the same Baseline. Now baseline will have 2 SQL plans, one is accepted (with hints info) and another is non-accepted (with no hints applied). Please make me correct if I miss-understood this concept.
Also, how that statement will be executed in case when optimizer is not able to reproduce same plan_hash_value ? Using the already existing accepted plan OR using the new plan with no hints applied.
Hi Pankaj,
Yes your understanding is correct about the baselines with a single accepted plan. Failure to reproduce the plan (or any of multiple accepted plans) will result in the optimizer using a plan created without any hints (i.e. the default plan). It will not throw an error, but just execute with default optimization. This is one reason that baselines can be a little confusing. If an accepted plan is not reproduced, all the hints are thrown out.
Kerry
[…] to Kerry Osborne again, and his article about SQL Plan Baselines. He (and others) write in detail about how and what SQL Plan Baselines are, but in essence it lets […]
Thanks so much for this information. It is a great help!
Just one correction I had to do to get the “create baseline” working was …
In
—-
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
sql_id=>’&sql_id’, –
plan_hashvalue=>&plan_hash_value,-
fixed=>’&fixed’);
—-
I had to correct the typo in the line
plan_hashvalue=>&plan_hash_value,-
To
plan_hash_value=>&plan_hash_value,-
Just wanted to mention it here for benefit of any future readers.
Regards.
Hi Kerry,
Thanks for sharing your experience with us. I’ve been reading your posts and I have a question.
If the optimizer will choose the plan with the lowest cost inside a plan baseline, why would I want to have multiple “accepted” plans in SQL Plan baseline? Is there a case where the optimizer will favor a less efficient “accepted” plan?
The main reason for having multiple plans is to handle situations where bind variable peeking (Adaptive Cursor Sharing now) would result in more than one “good” plan, depending on value of the bind variables.
Kerry
Thank you 🙂