Oracle says about sql plan management :
"
SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information."
During the life cycle of our product some times an event causes the SQL execution plan of a sql request changed and introduce some regressions in SQL performance. From the tuner/profiler point of view, it will be relevant to be aware of this modification and check that the new SQL execution plan is correct.
This section will give some inputs to achieve this goal.
How to register SQL Plan
SQL plan management provides a mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines.
Each SQL business request will be linked to an SQL plan baseline that contains an history of all execution plans build by oracle over the time for this request.
As a starting point, we need to capture execution plan for SQL request and add it in the SQL baseline.
This could be done automatically during a product run or could be done one by one by selecting a specific sql request.
Automatic SQL plan capture
To enable automatic plan capture, set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is set to FALSE.
1) Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
2) Run all your product use cases to capture all SQL execution plan
3) Restore the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to its default value.
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
4) Look at the DBA_SQL_PLAN_BASELINES table to see all sql plans in relation to the schema you need to monitor:
select sql_handle, plan_name, enabled, accepted, fixed,OPTIMIZER_COST,sql_text from dba_sql_plan_baselines where PARSING_SCHEMA_NAME='FMOSSE';
SQL_HANDLE PLAN_NAME ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ --- --- --- -------------- --------------------------------------------------------------------------------
SYS_SQL_495711156939d306 SQL_PLAN_4kpsj2pnmmns68e3968ca YES YES NO 0 delete from FMOSSE.S_PROPERTY where PROPERTYENTRY = :1
SYS_SQL_4a52f8b0626b170b SQL_PLAN_4nnrsq1j6q5sb659ff4e8 YES YES NO 1 UPDATE S_ACCOUNT SET ACCOUNTSTATUS = :1 WHERE (ACCOUNTID = :2 )
SYS_SQL_4c596a52d15cd98f SQL_PLAN_4sqbaab8ptqcg657f43ee YES YES NO 2 SELECT COUNT(FILEID) FROM S_FILE
SYS_SQL_4db332738e676fb7 SQL_PLAN_4vctkff76fvxr5c9cc6bc YES YES NO 2 select FOLDERID, FOLDERNAME, FOLDERDATECREATION, FOLDERIDPARENT, FOLDERSHARED, F
SYS_SQL_4e84758c387d2c28 SQL_PLAN_4x13pjhw7ub181e49339d YES YES NO 2 delete from FMOSSE.S_FILE
SYS_SQL_4fc3800bd9da7784 SQL_PLAN_4zhw01gcxnxw4291c9d40 YES YES NO 1 SELECT FOLDERID, FOLDERSHARED, FOLDERNAME, FOLDERSIZE, FOLDERDATECREATION, FOLDE
SYS_SQL_513d44ab9e0b94d8 SQL_PLAN_52ga4pfg0r56sacc4c75e YES YES NO 9 select count(*), sum(f.filesize), round(avg(f.filesize), 2), max(f.filesize), mi
SYS_SQL_51a8ae22d631730b SQL_PLAN_53a5f4bb32wsb7b701d8a YES YES NO 3 select PROPERTYENTRY, PROPERTYVALUE from FMOSSE.S_PROPERTY order by PROPERTYENTR
SYS_SQL_51e053d0f1bfb831 SQL_PLAN_53s2mu3svzf1jc7624a29 YES YES NO 4 SELECT t0.ACCOUNTID, t0.ACCOUNTNAME, COUNT(t3.FILEID), TO_CHAR(NVL(SUM(t3.FILESI
SYS_SQL_521f93c697ccfbb2 SQL_PLAN_547wmsubwtyxk29b1bccc YES YES NO 2 select arf.accountid from s_accountresourcefolder arf inner join s_folder f on f
SYS_SQL_56ceb93fab2da70d SQL_PLAN_5dmpt7ypkv9sd42ba1be5 YES YES NO 0 DELETE FROM S_ACCOUNT WHERE (ACCOUNTID = :1 )
Manual SQL plan capture
1) To capture a sql execution plan manually, you have to take a look at the V$SQL view and select the request you need to monitor :
SELECT sql_id, sql_fulltext FROM V$SQL WHERE sql_text LIKE '%SELECT COUNT(FILEID)%' and PARSING_SCHEMA_NAME='FMOSSE';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
1xjd9vbxt5f7w SELECT COUNT(FILEID) FROM S_FILE
2) Using the SQL_ID create a SQL plan baseline for the statement.
variable cnt number;
EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'1xjd9vbxt5f7w');
3) Check the DBA_SQL_PLAN_BASELINES tables
select B.SQL_HANDLE, B.PLAN_NAME, B.SQL_TEXT, B.ORIGIN, B.PARSING_SCHEMA_NAME, B.ENABLED, B.ACCEPTED, B.FIXED from DBA_SQL_PLAN_BASELINES B, V$SQL S WHERE S.SQL_PLAN_BASELINE=B.PLAN_NAME AND S.SQL_ID='1xjd9vbxt5f7w';
SQL_HANDLE PLAN_NAME SQL_TEXT ORIGIN PARSIN ENA ACC FIX
------------------------------ ------------------------------ --------------------------------- -------------- ------ --- --- ---
SYS_SQL_4c596a52d15cd98f SQL_PLAN_4sqbaab8ptqcg657f43ee SELECT COUNT(FILEID) FROM S_FILE AUTO-CAPTURE FMOSSE YES YES NO
SQL Plan Baseline activation
Now we need to indicate to the optimizer to refere to the SQL plan baseline defined for each request :
alter system set optimizer_use_sql_plan_baselines=TRUE;
The optimizer will initially look for a given query if there is a baseline and compare both the cost of the execution plan from the baseline and cost of the execution plan of the current query. If the cost has changed and the execution plan of the query is better then the cost of the execution plan of the baseline then the new execution plan will be added to the baseline.
To illustrate this:
1) I create first a table and populate some data in it.
create table test (id NUMBER, VALUE VARCHAR2) NOLOGGING;
declare
i number;
begin
for i in 1..300000 loop
insert /*+ APPEND */ into test values( i, 'LABEL# ' || i);
end loop;
commit;
end;
2) Then i perform a select :
alter system set cursor_sharing=force;
select * from test where id=215445;
3) Using the v$sql view, i found the sql_id of the new request and load its execution plan in the sql plan baseline:
variable cnt number;
EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'05abfh82j5z85');
4) Now i display the baseline
select SQL_HANDLE, PLAN_NAME, OPTIMIZER_COST, SQL_TEXT, ORIGIN, PARSING_SCHEMA_NAME, ENABLED, ACCEPTED, FIXED from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME OPTIMIZER_COST SQL_TEXT ORIGIN PARSING_SCHEMA_NAME ENA ACC FIX
------------------------------ ------------------------------ -------------- -------------------------------------------------------------------------------- -------------- ------------------------------ --- --- ---
SYS_SQL_d223cef6c0ff72ee SQL_PLAN_d48yfyv0gywrf97bbe3d0 104 select * from test where id=:"SYS_B_0" MANUAL-LOAD TEST YES YES NO
5) Now, i create an contraint on the id column and so on an associated index:
alter table test add constraint PK_TEST_ID primary key( id);
Do an other select with a different id value.
6) Look again for the baseline:
select SQL_HANDLE, PLAN_NAME, OPTIMIZER_COST, SQL_TEXT, ORIGIN, PARSING_SCHEMA_NAME, ENABLED, ACCEPTED, FIXED from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME OPTIMIZER_COST SQL_TEXT ORIGIN PARSING_SCHEMA_NAME ENA ACC FIX
------------------------------ ------------------------------ -------------- -------------------------------------------------------------------------------- -------------- ------------------------------ --- --- ---
SYS_SQL_d223cef6c0ff72ee SQL_PLAN_d48yfyv0gywrf97bbe3d0 104 select * from test where id=:"SYS_B_0" MANUAL-LOAD TEST YES YES NO
SYS_SQL_d223cef6c0ff72ee SQL_PLAN_d48yfyv0gywrf7ad59d6c 2 select * from test where id=:"SYS_B_0" AUTO-CAPTURE TEST YES NO NO
What we can see it that a new sql plan has been added in the baseline with a better optimizer cost (2 instead of 104).
We can also see that the first sql plan has been added manually in the baseline (ORIGIN=MANUAL-LOAD) and that the second one has been added by oracle itself (ORIGIN=AUTO-CAPTURE).
As we add a new index, the sql plan of the request as changed due to a database structure modification. In this case the new plan has been added in the base line with ENABLED=YES (could be choose by the optimizer) but with ACCEPTED=NO due to the fact that the DBA has not yet validate the new SQL plan.
If we want to force the use of the first plan we need to set the plan as FIXED :
variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SYS_SQL_d223cef6c0ff72ee',
PLAN_NAME => 'SQL_PLAN_d48yfyv0gywrf97bbe3d0',
ATTRIBUTE_NAME => 'fixed',
ATTRIBUTE_VALUE => 'YES');
How to overwrite an existing execution plan
Some time during a profiling session we need to overwrite an execution plan for a specific request because we improve its performance.
To do that we need :
1) to disable the current sql plan in the baseline:
variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SYS_SQL_d223cef6c0ff72ee',
PLAN_NAME => 'SQL_PLAN_d48yfyv0gywrf97bbe3d0',
ATTRIBUTE_NAME => 'enabled',
ATTRIBUTE_VALUE => 'NO'
);
2) register the new plan under the same sql_handle but with the sql_id and plan_hash_value of the new request (values found in the v$sql view)
variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_ID => '72fb94sq0karh',
PLAN_HASH_VALUE => '1357081020',
SQL_HANDLE => 'SYS_SQL_d223cef6c0ff72ee'
);
3) Ensure that the new plan has been accepted in the base line :
select SQL_HANDLE, PLAN_NAME, OPTIMIZER_COST, SQL_TEXT, ORIGIN, PARSING_SCHEMA_NAME, ENABLED, ACCEPTED, FIXED from DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME OPTIMIZER_COST SQL_TEXT ORIGIN PARSING_SCHEMA_NAME ENA ACC FIX
------------------------------ ------------------------------ -------------- -------------------------------------------------------------------------------- -------------- ------------------------------ --- --- ---
SYS_SQL_d223cef6c0ff72ee SQL_PLAN_d48yfyv0gywrf97bbe3d0 2 select * from test where id=:"SYS_B_0" MANUAL-LOAD TEST YES YES NO
How to display an execution plan registered under the baseline
To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SYS_SQL_d223cef6c0ff72ee',
format=>'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_d223cef6c0ff72ee
SQL text: select * from test where id=:"SYS_B_0"
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d48yfyv0gywrf7ad59d6c Plan id: 2060819820
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3803811902
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST |
| 2 | INDEX UNIQUE SCAN | PK_TEST_ID |
--------------------------------------------------
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan name: SQL_PLAN_d48yfyv0gywrf97bbe3d0 Plan id: 2545673168
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 1357081020
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
34 rows selected.
Oracle documentation
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm
