SQL Plan Management
SQL Plan Management provide the platform to control the execution plan usability for oracle database. With help of SQL Plan management , You can have ability to manual interpretation to choose the best plan for you.
Create SQL plan baselines for SQL queries means accepted execution plans for all statements. The SQL plan baselines are stored in a plan history in the SYSAUX tablespace.
In Oracle database 12c the evolution job is done by an automated job as an advisor task called SYS_AUTO_SPM_EVOLVE_TASK, which is triggered by the Job “sql tuning advisor” present in auto task list table. If you can disable the job, it automatically stop the evolution task for sql baseline.
Check the automatic job status:
SELECT * FROM dba_autotask_task;
Automatic capturing of SQL Plan
Automatic capture plan enabled for SQL queries by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.(By default is false)
It’s start capturing the execution plan for SQL queries in SQL baseline. First plan is always has accepted state.
Note: Using SQL Plan Baseline,Parameter OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.(By default is true)
Check the SQL Plan baselines having SQL plan:
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines;
Manually capturing of SQL Plan
We have SCOTT schema as default provided by Oracle. We proceed with an example:
1. Checked the SQL Plan Baseline table is empty or using any plan by your oracle system.
SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines;
no rows selected
2. Execute following query which will proceed for FULL Table Scan
SQL> select * from scott.emp where ename='SCOTT';
Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
3. For manual load the SQL plan, Find the SQL ID from V$SQL view.
SELECT sql_id,sql_text FROM v$sql WHERE sql_text LIKE '%select * from scott.emp%';
SQL_ID SQL_TEXT ------------- ------------------------------------------- b4xzjkzz30q5j select * from scott.emp where ename='SCOTT'
4. Load the Plan manually in SQL baseline with help of package:
SET SERVEROUTPUT ON
DECLARE
l_plans_captured PLS_INTEGER;
BEGIN
l_plans_captured := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'b4xzjkzz30q5j');DBMS_OUTPUT.put_line('Plans captured: ' || l_plans_captured);
END;
/
5. Check again the SQL baselines table for SQL plan
SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%'
SQL_HANDLE PLAN_NAME ENA ACC --------------------- ------------------------------ --- --- SQL_e523596056c54c6e SQL_PLAN_fa8utc1bcam3fd8a279cc YES YES
6. Created the index on ename column and checked the execution plan again
Create index scott.emp_name_idx on scott.emp(ename);
set autotrace traceonly
select * from scott.emp where ename=’SCOTT’;
Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
7. Full scan is used in query,On Check SQL plan baseline table.One more plan but not in accepted state.
select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselinesSELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%';
SQL_HANDLE PLAN_NAME ENA ACC ---------------------- ------------------------------ --- --- SQL_e523596056c54c6e SQL_PLAN_fa8utc1bcam3f38fa3838 NO NO SQL_e523596056c54c6e SQL_PLAN_fa8utc1bcam3fd8a279cc YES NO
8. Manually Evolve the SQL Plan in 11g/12c
In 11g for envolve the plan:
SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_e523596056c54c6e')
FROM dual;
In 12c envolve the plan:
Step 1: Create task
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_e523596056c54c6e');
DBMS_OUTPUT.put_line('Task Name: ' || l_return);
END;
/
Task Name: TASK_1644
Step 2: Evolve task
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1644’);
DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);
END;
/
Execution Name: EXEC_1591
Step 3: Generate the report of task generated above.
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1644’, execution_name => ‘EXEC_1591’) AS output FROM dual;
OUTPUT --------------------------------------------------------------------------------GENERAL INFORMATION SECTION -------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : TASK_1644 Task Owner : SYS Execution Name : EXEC_1591 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 01/24/2017 00:32:24 Finished : 01/24/2017 00:32:25 Last Updated : 01/24/2017 00:32:25 Global Time Limit : 2147483646 Per-Plan Time Limit : UNUSED Number of Errors : 0 -------------------------------------------------------------------------------- SUMMARY SECTION -------------------------------------------------------------------------------- Number of plans processed : 1 Number of findings : 1 Number of recommendations : 1 Number of errors : 0 -------------------------------------------------------------------------------- DETAILS SECTION -------------------------------------------------------------------------------- Object ID : 2 Test Plan Name : SQL_PLAN_fa8utc1bcam3f38fa3838 Base Plan Name : SQL_PLAN_fa8utc1bcam3fd8a279cc SQL Handle : SQL_e523596056c54c6e Parsing Schema : SYS Test Plan Creator : SYS SQL Text : select * from scott.emp where ename='SCOTT' Execution Statistics: ----------------------------- Base Plan Test Plan ---------------------------- ------------------ Elapsed Time (s): .000009 .000007 CPU Time (s): 0 0 Buffer Gets: 0 0 Optimizer Cost: 3 2 Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 0 0 Executions: 10 10 FINDINGS SECTION -------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The plan was verified in 0.04600 seconds. It passed the benefit criterion because its verified performance was 3.50000 times better than that of the baseline plan. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1644', object_id => 2, task_owner => 'SYS'); EXPLAIN PLANS SECTION -------------------------------------------------------------------------------- Baseline Plan ----------------------------- Plan Id : 101 Plan Hash Value : 3634526668 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 | 00:00:01 | | * 1 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 | 00:00:01 | --------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("ENAME"='SCOTT') Test Plan ----------------------------- Plan Id : 102 Plan Hash Value : 955922488 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 38 | 2 | 00:00:01 | | *2 | INDEX RANGE SCAN | EMP_NAME_IDX | 1 | | 1 | 00:00:01 | --------------------------------------------------------------------------------Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("ENAME"='SCOTT') --------------------------------------------------------------------------------
Step 4: Accept the plan in oracle 12c
exec dbms_spm.accept_sql_plan_baseline(task_name => ‘TASK_1644’, object_id => 2, task_owner => ‘SYS’);
OR
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_21’);
DBMS_OUTPUT.put_line(‘Plans Accepted:’ || l_return);
END;
/
9. Check the status in sql plan baselines view, it show execution plan in accepted state
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC ------------------------- ----------------------------------- --- --- SQL_e523596056c54c6e SQL_PLAN_fa8utc1bcam3f38fa3838 YES YES SQL_e523596056c54c6e SQL_PLAN_fa8utc1bcam3fd8a279cc YES YES
10.Test the SQL Queries above used and found index is used. Now both the plan is in accepted state.
select * from scott.emp where ename=’SCOTT’;
Execution Plan ---------------------------------------------------------- Plan hash value: 2092603121 ----------------------------------------------------------------------------- | Id | Operation | Name |Rows|Bytes |Cost(%CPU) ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| | 1 |TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 38 | 2 (0)| |* 2 INDEX RANGE SCAN |EMP_NAME_IDX| 1 | | 1 (0)| -----------------------------------------------------------------------------
11. You can modified it by changing attribute of SQL plan baseline. Suppose you don’t want to use the new plan and switch to old plan.
You can modified the enabled parameter to No. So optimizer not able to use the new plan at all. you can alter attributes with ALTER_SQL_PLAN_BASELINE function.
enabled (YES/NO) : YES : plan is available for the optimizer to use.
fixed (YES/NO) : YES : Fixed plans got more priority than non-fixed plans.
Disable the latest plan as below:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_e523596056c54c6e',plan_name => 'SQL_PLAN_fa8utc1bcam3f38fa3838',
attribute_name => 'ENABLED', attribute_value => 'NO');
end;
/
12. Verify again the sql query, its not using index and switch to old plan.
SQL>
SQL>Select * from scott.emp where ename='SCOTT';
Execution Plan -------------------------- Plan hash value: 3956160932 ------------------------------------------------------------------ | Id | Operation | Name| Rows | Bytes| Cost(%CPU)| Time | ------------------------------------------------------------------ | 0| SELECT STATEMENT | | 1| 38| 3 (0)| 00:00:01| |* 1| TABLE ACCESS FULL| EMP | 1| 38| 3 (0)| 00:00:01| ------------------------------------------------------------------
Transfer the SQL plan in between two database/backup of sql plan before patch/upgrade.
1. Create a staging table for transfer the stats
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT',
tablespace_name => 'USERS');
END;
/
2. Load the plan baseline to staging table
SET SERVEROUTPUT ON
DECLARE
var_plans_loaded PLS_INTEGER;
BEGIN
var_plans_loaded := DBMS_SPM.pack_stgtab_baseline(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT');DBMS_OUTPUT.put_line('Plans loaded: ' || var_plans_loaded);
END;
/
Plans loaded: 2
3. Take an export/EXPDP backup of staging table
4. Move to another server
5. Import/IMPDP the staging table
6. Unload the execution plan to database server:
SET SERVEROUTPUT ON
DECLARE
l_plans_unloaded PLS_INTEGER;
BEGIN
l_plans_unloaded := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT');DBMS_OUTPUT.put_line('Plans Unloaded: ' || l_plans_unloaded);
END;
/
Plans Unloaded: 2
Drop the SQL plan
Specify only the sql_handle, it will drop all the plan related to SQLhandle.
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_e523596056c54c6e');
DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/
OR
Drop the specific plan present in SQL handle
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => NULL,
plan_name => 'SQL_PLAN_fa8utc1bcam3f38fa3838');
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
For displaying the SQL plan baselines plan
SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( sql_handle=>'SYS_SQL_209d10fabbedc741',format=>'basic'));
For verification purpose
Check whether oracle is using SQL baseline by joining v$SQL view:
Select s.sql_text, b.plan_name, b.origin, b.accepted
From dba_sql_plan_baselines b, v$sql s
Where s.exact_matching_signature = b.signature
And s.SQL_PLAN_BASELINE = b.plan_name;
Reference from
https://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95121
https://oracle-base.com/articles/12c/adaptive-sql-plan-management-12cr1
https://oracle-base.com/articles/11g/sql-plan-management-11gr1
Nice blog here! Also your website quite a bit up very fast! What host are you using? Can I get your affiliate link to your host? I desire my website loaded up as quickly as yours lol
LikeLike
I’m not positive where you are getting your info, but good topic. I needs to spend some time finding out more or understanding more. Thank you for fantastic info I was looking for this info for my mission.
LikeLike
Thanks
LikeLike
You can certainly see your enthusiasm within the work you write. The arena hopes for more passionate writers such as you who aren’t afraid to mention how they believe. Always follow your heart.
LikeLike
Thanks
LikeLike
Thank you, I have just been searching for info approximately this subject for a while and yours is the best I have came upon till now. However, what concerning the conclusion? Are you certain concerning the supply?
LikeLike
Thanks
LikeLike
Thanks for reply.
LikeLike
Fantastic site. Lots of useful info here. I’m sending it to several buddies ans also sharing in delicious. And naturally, thank you in your sweat!
LikeLike
Thanks
LikeLike
Thanks
LikeLike