Oracle sqlplamanagement:
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 xxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011
SQL> select * from t where col1 = 1;
COL1 COL2 COL3
---------- -------------------------------------------------- -----------
1 xxxxxxxxxxxxxxxxxxxxxxxx 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'));
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_1447ba0f
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>
================================================
BAD SQL ID:- XXXXXXXXXXXXX
##################################################################################
Step 1) Using script to look at the plan_hash_value over time from AWR, it was clear that the CBO had picked a new, bad, explain plan :-
##################################################################################
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
In above script just replace the sql id with bad sql id according to situation
OUTPUT :-
-------------
From above out we can clearly see the Currently above sql having the two PLAN's currently using bad plan compare to previous plan.
PLAN_HASH_VALUES from above output:-
2339166878 ====> is Bad plan by watching above
4248587209 =====> Good one better than above one
So we knew the sql_id, and we knew the plan_hash_value of the plan which we wanted the CBO to use. But how to do this?
Write in detail about how and what SQL Plan Baselines are, but in essence it lets you tell Oracle which plan to use (or optionally, prefer) for a given sql_id.
Since the desired plan_hash_value was no longer in the cursor cache, we could get it back from AWR, loaded in via a SQL Tuning Set. Here’s the code with in-line comments explaining the function of each block:-
####################################################################################################################################################################
Step 2) Set up a SQL Baseline using known-good plan, sourced from AWR snapshots. In this example, sql_id is XXXXXXXXX and the plan_hash_value of the good plan that we want to force is 4248587209 #
####################################################################################################################################################################
-- Create SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'MySTS01',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
-- Populate STS from AWR, using a time duration when the desired plan was used
-- List out snapshot times using : SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
-- Specify the sql_id in the basic_filter (other predicates are available, see above output)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>20615, end_snap=>20623,basic_filter=>'sql_id = ''XXXXXXXXXXXXXXXXXX''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
CLOSE cur;
END;
/
This query is using good plan between 20615 & 20623 snapshots from above step 1 pasted output.
-- List out SQL Tuning Set contents to check we got what we wanted
SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
);
-- Here MySTS01 is Tuning set which was we created for this query.
######################################
Step 3) Lets Pin good plan to this query #
######################################
-- List out the Baselines to see what's there
SELECT * FROM dba_sql_plan_baselines ;
-- Load desired plan from STS as SQL Plan Baseline
-- Filter explicitly for the plan_hash_value here if you want
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MySTS01',
basic_filter=>'plan_hash_value = ''4248587209'''
);
END;
/
-- List out the Baselines
SELECT * FROM dba_sql_plan_baselines ;
########################################################################################################################
Step 4) Flush the cursor cache after loading the baseline to make sure it gets picked up on next execution of the sql_id. #
###########################################################################################################
Since from above first step we dealt with single query we will try to flush that query from cursor cache as below.
1) If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the GV$SQLAREA view. Here is an example:
select INST_ID,ADDRESS, HASH_VALUE,OLD_HASH_VALUE,PLAN_HASH_VALUE from gV$SQLAREA where SQL_ID like 'XXXXXXXXXXXXXXX';
INST_ID ADDRESS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- -------------- ---------------
4 0000000419923CB8 3373908091 2718803809 2339166878
2) The syntax for the PURGE the PLAN_HASH_VALUE procedure is shown below.
procedure PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)
SQL> exec DBMS_SHARED_POOL.PURGE ('0000000419923CB8, 3373908091', 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like' XXXXXXXXXX ';
no rows selected ( Since we purged the plan its wont return the any values If return we need to flush the shared pool to clear the all plans from cursor cache )
####################################################################
Step 6) Check the query Got picked up New plan which was we pinned through Baselines:-
####################################################################
In above out you can see New plan was picked up which was we pinned through baselines.
If you execute below statement also will show you plan has been picked in awr or not :-
----------------------------------------------------------------------------------------------------------
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
In above at around 9.00 PM New plan was picked up due to we forced trough baselines.
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 xxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011
SQL> select * from t where col1 = 1;
COL1 COL2 COL3
---------- -------------------------------------------------- -----------
1 xxxxxxxxxxxxxxxxxxxxxxxx 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'));
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_1447ba0f
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>
================================================
BAD SQL ID:- XXXXXXXXXXXXX
##################################################################################
Step 1) Using script to look at the plan_hash_value over time from AWR, it was clear that the CBO had picked a new, bad, explain plan :-
##################################################################################
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
In above script just replace the sql id with bad sql id according to situation
OUTPUT :-
-------------
From above out we can clearly see the Currently above sql having the two PLAN's currently using bad plan compare to previous plan.
PLAN_HASH_VALUES from above output:-
2339166878 ====> is Bad plan by watching above
4248587209 =====> Good one better than above one
So we knew the sql_id, and we knew the plan_hash_value of the plan which we wanted the CBO to use. But how to do this?
Write in detail about how and what SQL Plan Baselines are, but in essence it lets you tell Oracle which plan to use (or optionally, prefer) for a given sql_id.
Since the desired plan_hash_value was no longer in the cursor cache, we could get it back from AWR, loaded in via a SQL Tuning Set. Here’s the code with in-line comments explaining the function of each block:-
####################################################################################################################################################################
Step 2) Set up a SQL Baseline using known-good plan, sourced from AWR snapshots. In this example, sql_id is XXXXXXXXX and the plan_hash_value of the good plan that we want to force is 4248587209 #
####################################################################################################################################################################
-- Create SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'MySTS01',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
-- Populate STS from AWR, using a time duration when the desired plan was used
-- List out snapshot times using : SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
-- Specify the sql_id in the basic_filter (other predicates are available, see above output)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>20615, end_snap=>20623,basic_filter=>'sql_id = ''XXXXXXXXXXXXXXXXXX''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
CLOSE cur;
END;
/
This query is using good plan between 20615 & 20623 snapshots from above step 1 pasted output.
-- List out SQL Tuning Set contents to check we got what we wanted
SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
);
-- Here MySTS01 is Tuning set which was we created for this query.
######################################
Step 3) Lets Pin good plan to this query #
######################################
-- List out the Baselines to see what's there
SELECT * FROM dba_sql_plan_baselines ;
-- Load desired plan from STS as SQL Plan Baseline
-- Filter explicitly for the plan_hash_value here if you want
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MySTS01',
basic_filter=>'plan_hash_value = ''4248587209'''
);
END;
/
-- List out the Baselines
SELECT * FROM dba_sql_plan_baselines ;
########################################################################################################################
Step 4) Flush the cursor cache after loading the baseline to make sure it gets picked up on next execution of the sql_id. #
###########################################################################################################
Since from above first step we dealt with single query we will try to flush that query from cursor cache as below.
1) If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the GV$SQLAREA view. Here is an example:
select INST_ID,ADDRESS, HASH_VALUE,OLD_HASH_VALUE,PLAN_HASH_VALUE from gV$SQLAREA where SQL_ID like 'XXXXXXXXXXXXXXX';
INST_ID ADDRESS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- -------------- ---------------
4 0000000419923CB8 3373908091 2718803809 2339166878
2) The syntax for the PURGE the PLAN_HASH_VALUE procedure is shown below.
procedure PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)
SQL> exec DBMS_SHARED_POOL.PURGE ('0000000419923CB8, 3373908091', 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like' XXXXXXXXXX ';
no rows selected ( Since we purged the plan its wont return the any values If return we need to flush the shared pool to clear the all plans from cursor cache )
####################################################################
Step 6) Check the query Got picked up New plan which was we pinned through Baselines:-
####################################################################
In above out you can see New plan was picked up which was we pinned through baselines.
If you execute below statement also will show you plan has been picked in awr or not :-
----------------------------------------------------------------------------------------------------------
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
In above at around 9.00 PM New plan was picked up due to we forced trough baselines.
No comments:
Post a Comment