SQL Plan Management – Oracle Database 11g
In SPM part 1 we saw information about SPM and how to automatically capture the baseline.In this part we will see how to manually capture the baseline and the affect of using FIXED variable.
Capturing Baseline Manually
Occasionally we have to to capture the baseline manually. This could happen while tuning the query in our database where we have optimizer_capture_sql_plan_baselines parameter set to FALSE.We have 2 procedures available for capturing the baselines manually.
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_SQLSET procedure is used mainly while doing the upgrade from Oracle database 10g to Oracle database 11g.
The procedure is to
- create a SQL Tuning set in 10g and pack all the SQLs in the library cache along with there plans into SQL tuning set – Procedure DBMS_SQLTUNE.CREATE_SQLSET and DBMS_SQLTUNE.LOAD_SQLSET
- Then to create a staging table and load this SQL tuning set into the staging table – Procedure DBMS_SQLTUNE.CREATE_STGTAB_SQLSET and DBMS_SQLTUNE.PACK_STGTAB_SQLSET
- Update database to 11g
- Unpack the staging table into SQL Tuning set you created before – Procedure DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET
- Create SQL Baselines from the SQL tuning set – Procedure DBMS_SPM.LOAD_PLANS_FROM_SQLSET
SQL> select col1, count(1) from T group by col1; COL1 COUNT(1) ---------- ---------- 1 1 2 3 3 98304 SQL> show parameters capture NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE SQL>Currently we dont have any baselines in the database.
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
no rows selected
SQL>
SQL> explain plan for
2 select * from T where col1 = 1;
Explained.
SQL> set line 999
SQL> set pagesize 999
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 | | 49154 | 2976K| 260 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 49154 | 2976K| 260 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=1)
13 rows selected.
SQL>
Lets create a baseline manually for this statement. For creating baselines manually we need to have the statement in cache first.Lets run the statement once and find the SQL ID.
SQL> select sql_id, sql_text from v$sql where sql_text like 'select * from T where%'; SQL_ID SQL_TEXT ------------- ------------------------------------------------------------------------------------------ 5pvxxjg6n7mrp select * from T where col1 = 1 Now we will use LOAD_PLANS_FROM_CURSOR_CACHE to create a baseline SQL> DECLARE 2 l_plans_loaded PLS_INTEGER; 3 BEGIN 4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( 5 sql_id => '5pvxxjg6n7mrp'); 6 END; 7 / PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX ------------------------------ ------------------------------ -------------------------------- --- --- --- SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c select * from T where col1 = 1 YES YES NO SQL>So we can see that SQL Plan baseline is created as well as accepted.
We will check out the plan from baseline now, but it should be full table scan.
SQL> SET LONG 10000
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: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49154 | 2976K| 260 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 49154 | 2976K| 260 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=1)
24 rows selected.
SQL>
Now lets create an index on the table on COL1.
SQL> create index T_IDX on T(COL1);
Index created.
SQL>
Now since the baseline is used, same query will go for a full table scan even after creating an 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 | | 49154 | 2976K| 260 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 49154 | 2976K| 260 (2)| 00:00:04 |
--------------------------------------------------------------------------
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>
So as you can see we have a baseline SYS_SQL_PLAN_1d83920f94ecae5c getting used for this query.Lets go ahead and try creating another baseline for this SQL. May be this time we will get better plan.
SQL ID remains the same.
SQL> DECLARE 2 l_plans_loaded PLS_INTEGER; 3 BEGIN 4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( 5 sql_id => '5pvxxjg6n7mrp'); 6 END; 7 / PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX ------------------------------ ------------------------------ -------------------------------- --- --- --- SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c select * from T where col1 = 1 YES YES NO SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920fae82cf72 select * from T where col1 = 1 YES NO NOAnd yes, we can see Oracle has created another baseline here. But ACCEPTED is still NO for the new plan.
This is because we haven’t evaluated the plan yet. If we would have set optimizer_capture_sql_plan_baselines parameter to true, it would have automatically captured and made ACCEPTED=YES after doing automatic evaluation.
Since this is false, we have to evaluate whether new plan is better or not. This process of evaluating new baseline is called evolving.
Evolving a plan actually compares the cost and several other factors like DISK_READS, BUFFER_GETS, ELAPSED_TIME etc for all the plans that are available in DBA_SQL_PLAN_BASELINES table for that particular SQL HANDLE.
So in our case when we evolve the plan it will compare plan SYS_SQL_PLAN_1d83920f94ecae5c and SYS_SQL_PLAN_1d83920fae82cf72
Just to be certain we will display the plan SYS_SQL_PLAN_1d83920fae82cf72
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 | | 49154 | 2976K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 49154 | 2976K| 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 49154 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=1)
25 rows selected.
SQL>
So it is indeed using the index on the table.Here there are 2 things again.
- Evolving the plan automatically – Using procedure DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
- Manually accepting the plan – Using procedure ALTER_SQL_PLAN_BASELINE setting ACCEPTED=>YES
So its upto the optimizer to decide which plan is best among the available plan.
In method 2, we can decide and select which ever plan we think is correct. This based on our knowledge of the type of SQL we are running and what resources we have with us.
Can we have 2 plans in ACCEPTED status for a query?
Yes we can.Which plan optimizer will choose out of the two?
Always the best among the two. If more than 1 plan is ACCEPTED than optimizer will choose the best plan to be used for the query.Example if we use a query “select * from T where col = :B1″
Here we have used a bind variable. Lets say we have 2 plans with us right now
Plan 1) Full table scan
Plan 2) Index Range scan
Which plan optimizer will choose depends upon the value of bind variable.
Yes, in 11g we have something called “Adaptive Cursor Sharing”, but this is little off the topic. We will cover “Adaptive Cursor Sharing” in different article.
So if we supply value as 1 or 2 and since there is only 1 row with col1=1 and 3 rows with col1=2, optimizer will use Plan 2) Index Range Scan.
If we supply value as 3, optimizer will use Plan 1) Full table scan.
So imagine a case where we have 5-6 plans based on selectivity of different tables involved in a complex query, in that case its a good idea to keep all those plans as ACCEPTED. Optimizer will always choose the best among those based on selectivity of the data.
We have seen evolving the plan in Part 1 – http://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/
Lets set ACCEPTED=TRUE manually now.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => 'SYS_SQL_1447ba3a1d83920f',
6 plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72',
7 attribute_name => 'ACCEPTED',
8 attribute_value => 'YES');
9
10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
11 END;
12 /
Plans Altered: 1
PL/SQL procedure successfully completed.
SQL>
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c select * from T where col1 = 1 YES YES NO
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920fae82cf72 select * from T where col1 = 1 YES YES NO
SQL>
Lets check the explain plan for value 1.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 | | 49154 | 2976K| 56 (2)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 49154 | 2976K| 56 (2)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 49154 | | 10 (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>
So its using SYS_SQL_PLAN_1d83920fae82cf72 baselines.What is the affect of using FIXED=YES ?
Fixed attribute will fix the plan for a query. So even if we have a better plan, the query will still not be using it. It is like disabling (ENABLED=NO, ACCEPTED=NO) all the plans for that SQL Handle.Let make FIXED=YES for the first baseline (SYS_SQL_PLAN_1d83920f94ecae5c) which does full table scan.
To change this attribute we can again use DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure
SQL> set serveroutput on
SQL> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => 'SYS_SQL_1447ba3a1d83920f',
6 plan_name => 'SYS_SQL_PLAN_1d83920f94ecae5c',
7 attribute_name => 'FIXED',
8 attribute_value => 'YES');
9
10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
11 END;
12 /
Plans Altered: 1
PL/SQL procedure successfully completed.
SQL>
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c select * from T where col1 = 1 YES YES YES
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920fae82cf72 select * from T where col1 = 1 YES YES NO
SQL>
Lets check out the explain plan for “select * from T where col1 = 1″ querySQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49154 | 2976K| 260 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 49154 | 2976K| 260 (2)| 00:00:04 |
--------------------------------------------------------------------------
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>
So if you see now, same query which was earlier using index scan has now shifted to full table scan. Thats the magic of SPM !!I would recommend not using FIXED attribute at all. You are just killing the functionality provided by 11g.
However, if we make both plans as FIXED then ??
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 => 'FIXED',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920f94ecae5c select * from T where col1 = 1 YES YES YES
SYS_SQL_1447ba3a1d83920f SYS_SQL_PLAN_1d83920fae82cf72 select * from T where col1 = 1 YES YES YES
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 | | 49154 | 2976K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 49154 | 2976K| 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 49154 | | 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>
It will again go back to best among the fixed plans.
In the next part, I will cover how to transfer the baseline from one database to another and also how to set a specific plan for a query using hint and then creating baseline for that.
Hope this helps !!
-----------------------------------------------------------------------------------------------------------
Related links :
LATCHES LOCKS PINS AND MUTEXES
LIBRARY CACHE LOCK/PIN DEMONSTRATED
HOME
LATCHES LOCKS PINS AND MUTEXES
MUTEXES AND LATCHES
PARENT AND CHILD CURSORS IN ORACLE
QUICK OVERVIEW OF SHARED POOL TUNING
SHARED POOL ARCHITECTURE
SQL PLAN MANAGEMENT – ORACLE 11g : Part-1
http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%E2%80%93%20ORACLE%20%2011g%20%3A%20Part-1
SQL PLAN MANAGEMENT – ORACLE 11g : Part-2
http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%20%E2%80%93%20ORACLE%2011g%20%3A%20Part-2
CURSOR_SHARING DEMYSTIFIED
CURSOR_SHARING DEMYSTIFIED
LIBRARY CACHE LOCK/PIN DEMONSTRATED
SHARED POOL TUNING - A DEMONSTRATION
http://koenigocm.blogspot.in/search/label/SHARED%20POOL%20TUNING%20-%20A%20DEMONSTRATION
MIGRATE OUTLINES TO SQL MANAGEMENT BASE
http://koenigocm.blogspot.in/search/label/MIGRATE%20OUTLINES%20TO%20SQL%20MANAGEMENT%20BASE
MIGRATE OUTLINES TO SQL MANAGEMENT BASE
http://koenigocm.blogspot.in/search/label/MIGRATE%20OUTLINES%20TO%20SQL%20MANAGEMENT%20BASE
No comments:
Post a Comment