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.
Kyle Hailey:
awesome write-ups.
November 12, 2009, 9:51 pmI 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.
osborne:
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
November 13, 2009, 9:31 am