Search
Navigation
OakieTags
Who's online
Affiliations
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.
.png){kind=small}
TweetsAbout Kerry Osborne
Popular content
Today's:
Monthly archive
- May 2008 (4)
- April 2008 (3)
- March 2008 (8)
- February 2008 (2)
- January 2008 (7)
- December 2007 (4)
- November 2007 (6)
- October 2007 (2)
- September 2007 (2)
- April 2007 (6)
- March 2007 (5)
- February 2007 (4)
- January 2007 (7)
- October 2006 (1)
- « first
- ‹ previous
- 1
- 2
- 3
Recent comments
17 weeks 2 days ago
27 weeks 23 hours ago
28 weeks 5 days ago
32 weeks 9 hours ago
34 weeks 2 days ago
43 weeks 5 days ago
45 weeks 2 days ago
46 weeks 2 days ago
46 weeks 3 days ago
49 weeks 1 day ago