SQL Plan Management – Oracle Database 11g
SQL Plan Management or SPM is a new feature introduced in 11g. We will take a detailed look at SPM in the following sessions.
Before starting with practical example lets see some theory.
What is SQL Plan Management?
SPM is a new feature introduced in Oracle database 11g which stores the baseline (Plans + Hints) inside database.
SPM allows an Oracle DBA to capture and preserve the most efficient execution plans for any SQL statement, thus limiting the impact of refreshed optimizer statistics, changes to existing applications, and even upgraded database versions
Why do we need SPM?
SPM is basically used for plan stability. If you have a database environment with several queries running, you always want your queries to run the way they are running irrespective of the changes that you make in your environment.
Example, If you upgrade your database, your queries might behave differently because of the change in optimizer or change in environment and at initial run you have to fix many queries in your production DB because of the upgrade or change in the env.
To avoid this, SPM is introduce to give you plan stability. So even after change in the environment, your queries will use same plan as it was before. More plans (Better or worse) could exists in the new environment, but optimizer is not allowed to use those plans without you confirming the plan change.
What was wrong with outlines?
Well, outlines are more about fixing the plan for a query that optimizer will use. So if there is change in environment, the plan would still be the same.
There are certain situations where you want the plan to change with change in lets say data. What if your data gets skewed over time.
Intially you had 100 of entries for month “DEC” out of total 150 entries. So it was using FTS. Now with increase in the data, there are 1 million records and entries for month “DEC” are around 1000. In that case it makes sense for optimizer to change plan and start using Index scan instead of FTS.
But since you used outline, you are forcing optimizer to go for a FTS. This is just an example situation and things can change in your environment and so outline is not a very good approach for such situations.
What was wrong with profile?
Profile is another feature introduced in 10g and can be implemented at SQL level. Profiles are better than outlines in that they are not fixing a plan through out the life of SQL. With change in the environment the plans can change even if the profile is used.
So whats the difference between profile and SQL plan baselines?
Profile is more of a advisors. They give advice to optimizer while executing the query. They provide optimizer with all the precise estimates. Profiles are more abount correcting optimizer to use correct plan when the underlying data is skewed and changed drastically.
The goal is to create best execution plan for the SQL by giving the very precise data to the optimizer. Its the optimizer who will decide what should be the explain plan based on the information/hints it has received from profile.
SQL Plan baselines works differently. It enforces the plan to be used for a SQL. Baseline does not provide and estimates or it does not help optimizer in anyway. It just tells optimizer to ignore everything and use the plan that we are giving you.
Optimizer just followes the plan provided by baselines. So here baseline is the driving mechanism for getting the correct plan.
Finally how baselines are different then outline then?
Well, baseline always keeps the optimized plans for your SQL. If there is a change in the environment you will have a new baseline created for the same SQL. Oracle will stop that baseline in the baseline history.
Depending on the baseline parameters setting in your environment, new plan will be automatically used or you have to evolve the new plan for optimizer to use it.
So in a way SQL baselines are combination of outlines and profiles. It gives the stability of plan similar to outlines and it also allows capturing better plans in case the environment changes.
Not only that, SQL Baselines give the complete control to the DBA on
- Whether to capture the new and better plans?
- Whether to use the new plans automaticallly without DBA’s intervention.
So DBAs have complete control of the environment now. Far better than profiles and outlines.
SQL Plan Baseline Parameters:
Before we check how to use SQL Plan baselines, lets consider the significance of 2 important baseline parameters
1) optimizer_capture_sql_plan_baselines – Default “FALSE”
This parameter is responsible for automatic capturing the baselines for SQLs. If we set this parameter to TRUE we are asking oracle to automatically gather the baselines for the SQL.
When you run the query for the first time (and parsed version is not present in shared_pool), oracle consider that as a fresh new query and does not create a baseline.
When you run the query for second time, oracle will consider the query as repetative and will automatically create the baseline.
These baseline are stored in DBA_SQL_PLAN_BASELINES table.
If this parameter is set to FALSE, then we (DBA) has to create baselines for the SQL manually. There are 2 procedure available for creating baselines manually.
1) Using DBMS_SPM.LOAD_PLANS_FROM_SQLSET
This procedure is usually used when we create SQL tuning set and store our SQLs into the tuning sets. Normally done before we upgrade the database to 11g. In our 10g database we create SQL tuning set and store all our SQL. Once we upgrade to 11g, we can create baselines for all our plans in SQL tuning set. That why what ever plans were effective in 10g, same will be used in 11g and there wont be any plan flips.
2) Using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
This is used when we want to fix one of the SQL currently running in our 11g database. We just load the plans from cursor cache (shared_pool) and create baseline out of that. We need to give SQL ID as input to this procedure.
We will see how to create baseline using manual method at later point of time.
2) optimizer_use_sql_plan_baselines – Default “TRUE”
This parameter will allow optimizer to use the baselines present in DBA_SQL_PLAN_BASELINES table. If you set this parameter to FALSE, then your 11g DB will start behaving same as 10g DB.
If there is any change in environment then it might flip the plan. Keeping this parameter TRUE is important in 11g.
How to use SQL Plan Management baselines ?
Lets take an example in a test database.
Table T with 1540 records.
SQL> select count(1) from t;
COUNT(1)
----------
1540
Data is skewed and distribution is as given below.
SQL> select col1, count(1) from t group by col1;
COL1 COUNT(1)
---------- ----------
1 1
2 3
3 1536
Gather the stats on the table
SQL> exec dbms_stats.gather_table_stats(OWNNAME=> 'ADVAITD_DBA',TABNAME => 'T', DEGREE => 6, GRANULARITY => 'ALL' ,CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');
SQL>
Currently I dont have any baseline.
SQL> select count(1) from dba_sql_plan_baselines;
COUNT(1)
----------
0
SQL>
My baseline parameters setting is as below.
SQL> show parameters baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
Let us first consider the auto capture utility for baselines.
AUTO Capture of baseline
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;
Session altered.
SQL>
As mention earlier, we need to run the query 2 times in order to automatically create the baseline.
SQL> select * from t where col1 = 1;
COL1 COL2 COL3
---------- -------------------------------------------------- -----------
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011
SQL> select * from t where col1 = 1;
COL1 COL2 COL3
---------- -------------------------------------------------- -----------
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011
If we check DBA_SQL_PLAN_BASELINES we will see a sql baseline created.
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
2 from dba_sql_plan_baselines
3 WHERE sql_text like 'select * from t%';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c YES YES NO
Turning off auto SQL plan baseline capture
SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;
Session altered.
SQL>
Following statement gives the plan stored in the baseline. DBMS_XPLAN has a new procedure DISPLAY_SQL_PLAN_BASELINE which will display the baseline.
SQL> set line 999
SQL> set pagesize 999
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920f94ecae5c'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920f94ecae5c
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 735 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 15 | 735 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=1)
24 rows selected.
SQL>
Origin: AUTO-CAPTURE shown above tell us that this baseline is captured automatically.
Lets now create an index and gather stats over index.
SQL> create index t_idx on t(col1);
Index created.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => 'ADVAITD_DBA', INDNAME=>'T_IDX');
Run the same query now, since the index is created, we expect the query to use the index.
SQL> explain plan for
2 select * from t where col1 = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 735 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 15 | 735 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=1)
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_1d83920f94ecae5c" used for this statement
17 rows selected.
SQL>
The reason we are seeing full table scan is because of the NOTE at the end, which says “SQL plan baseline “SYS_SQL_PLAN_1d83920f94ecae5c” used for this statement”
Since we have a baseline created for this SQL, it will not allow the plan to be changed. This is the kind of stability that SQL Plan baseline gives.
But using an index will be beneficial in our case.
If we check DBA_SQL_PLAN_BASELINES we can see a new plan has been created (PLAN_NAME = SYS_SQL_PLAN_1d83920fae82cf72), but it is not yet ACCEPTED. The plan is enabled though.
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
2 from dba_sql_plan_baselines
3 WHERE sql_text like 'select * from t%';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c YES YES NO
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920fae82cf72 YES NO NO
We can check what the new plan looks like using dbms_xplan.display_sql_plan_baseline
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920fae82cf72
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 735 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 15 | 735 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=1)
25 rows selected.
SQL>
As seen above, new plan uses index. Lets evolve this plan now.
Evolving a plan includes evaluating the cost of the plan and accepting if the plan seems to be better than all accepted plan for this query.
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_1447BA3A1D83920F')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_1447ba3a1d83920f
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_1d83920fae82cf72
-----------------------------------
Plan was verified: Time used .01 seconds.
Passed performance criterion: Compound improvement ratio >= 7.33
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 0 0
CPU Time(ms): 0 0
Buffer Gets: 22 3 7.33
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
Sometimes, your plan may not get evolved because oracle see that there are other already ACCEPTED plans which are better than the plan you are trying to evolve.
But if you know your plan will be better and still want to deploy the same, you can do so by manually changing the attributes ACCEPTED and ENABLED as shown below.
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SYS_SQL_1447ba3a1d83920f',
plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72',
attribute_name => 'ENABLED',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SYS_SQL_1447ba3a1d83920f',
plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72',
attribute_name => 'ACCEPTED',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
You can also set the value of attribute FIXED using the above function. Here is the meaning of ENABLED, ACCPETED and FIXED
ENABLED – ‘YES’ means the plan is available for use by the optimizer. It may or may not be used depending on accepted status.
ACCPETED – ‘YES’ means the plan will be used by optimizer while running the query. ‘NO’ means optimizer will not use the plan.
FIXED – ‘YES’ means the SQL plan baseline is not evolved over time. A fixed plan takes precedence over a non-fixed plan.
Once you evolve the plan, you can see that plan is ACCEPTED now.
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
2 from dba_sql_plan_baselines
3 WHERE sql_text like 'select * from t%';
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c YES YES NO
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920fae82cf72 YES YES NO
Now if you run the explain plan you can see Index T_IDX is getting used.
SQL> explain plan for
2 select * from t where col1 = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 735 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 15 | 735 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=1)
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement
18 rows selected.
SQL>
Hope this helps.
Part 2 can be viewed at -http://avdeo.com/2011/06/07/oracle-sql-plan-management-%E2%80%93-part-2/
References:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm
http://www.comp.dit.ie/btierney/oracle11gdoc/appdev.111/b28419/d_spm.htm
