Introduction: The query optimizer normally uses information like object and system statistics, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan. A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved
Parameters
Two parameters allow you to control SPM.The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will be automatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.
The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.
Note:Setting optimizer_capture_sql_plan_baselines = true permanently will result in a SQL plan baseline being created for every repeatable SQL statement on the system. That includes all of the recursive SQL Oracle executes on your behalf as well as every 'select sysdate from dual;' or 'select * from v$sql;' the DBA may do on the system. This could lead to a very large number of SQL plan baselines being captured and an increased foot print in the SYSAUX tablespace. There are some customers who do have optimizer_capture_sql_plan_baselines = true and they have not encountered any problem other than a large SYSAUX tablespace!
Scenario where a query is working fine in one instance and working bad in another instance. Thus i will exporting/importing the baseline from one instance to another.
SYS@SAM AS SYSDBA> show parameter baseline;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
Identify the problematic sql text using awr, statspack etc.
Get the sql_id for the sql_text- "Select R.Rowid From Fnd_Concur%"
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%Select R.Rowid From Fnd_Concur%';
5ugwm6cpfpr7b
create a tuning set
SYS@SAM AS SYSDBA> begin
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01', DESCRIPTION => 'TEST SQL TUNE SET');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Populate sql tuning set
SYS@SAM AS SYSDBA> DECLARE
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT VALUE(P) FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''5ugwm6cpfpr7b''')) p;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> '5ugwm6cpfpr7b_STS01', POPULATE_CURSOR=>CUR);
CLOSE CUR;
END;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> SYS@SAM AS SYSDBA>
List out sql tuning set
SYS@SAM AS SYSDBA>
SELECT plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01'));
PLAN_HASH_VALUE
---------------
2836784050
LOAD DESIRED PLAN FROM ‘SQL TUNING SET’ AS SQL PLAN BASELINE
SYS@SAM AS SYSDBA> DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'5ugwm6cpfpr7b_STS01', BASIC_FILTER=> 'PLAN_HASH_VALUE=
''2836784050''' );
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
VERIFY IF SQL PLAN BASELINE GOT CREATED SUCCESSFULLY
select * from dba_sql_plan_baselines;
select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
========================================================================
Now we have to pack this sql baseline.
Create a staging table in system schema. It cannot be under sys schema
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage1');
END;
/
pack the sql baselines
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
SQL_HANDLE => 'SQL_a00583ac0188a3d6');
END;
/
Now export the sql baselines
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@SAM AS SYSDBA> CREATE OR REPLACE DIRECTORY test_dir AS '/xxx_out/dump';
Directory created.
SYS@SAM AS SYSDBA> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
Grant succeeded.
SYS@SAM AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@SAM2 /xxx_out> expdp system/*** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log
compression=all
Export: Release 11.2.0.3.0 - Production on Thu Jan 5 06:00:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp
logfile=stage.log compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
. . exported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/xxx_out/dump/stage.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 06:09:
--------------------------------------------------------------------------------------------
Import the dump to the target database.
check first if there any baselines present or not.
select * from dba_sql_plan_baselines;
Import the sql baseline
oracle@SAM1 /oracle/stage> impdp system/***** tables=stage1 dumpfile=stage.dmp
Import: Release 11.2.0.3.0 - Production on Thu Jan 5 06:17:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=stage1 dumpfile=stage.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 06:17:33
oracle@ /oracle/stage>
unpack the sql baseline
sqlplus system/<password>
SET SERVEROUTPUT ON
SYSTEM> DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STAGE1',
table_owner => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END; 2 3 4 5 6 7 8
9 /
Plans Unpacked: 1
PL/SQL procedure successfully completed.
SYSTEM@SAM1>
check if the baselines are there or not
SYSTEM@SAM1 > select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
check if the plan is accepted or not.
SYS@SAM1 AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
If the plan is not accepted, then we have to eveolve it, which is discussed later in the post.
========================================================================
Scenario-2 -Force the optimizer to choose a plan. The first query runs against an unindexed column, does a full scan. And then an index have been added but the optimizer still chooses to do a full table scan. By Evolving the baseline, we forced the optimizer to choose the index and at last we removed the original plan which was doing a full table scan.
oracle@SAM2 ~> sqlplus test/welcome123
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 02:57:07 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TEST@SAM >
TEST@SAM > create table t as select * from applsys.FND_OBJECTS;
Table created.
TEST@SAM >
TEST@SAM > exec DBMS_STATS.GATHER_SCHEMA_STATS ('test');
PL/SQL procedure successfully completed.
TEST@SAM > show parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
-------------------- ------------------------------------------ ------------------------------------------ --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376 select count(*) from t YES YES NO
Lets create an index and create a new baseline
TEST@SAM > create index t_idx on t (OBJECT_ID);
Index created.
TEST@SAM >TEST@SAM > exec dbms_stats.gather_schema_stats ('test');
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> alter system flush shared_pool;
System altered.
SYS@SAM AS SYSDBA>
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM >
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
Session altered.
TEST@SAM >
TEST@SAM > set autotrace on;
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy3fdbb376" used for this statement
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES NO NO
check the execution plan for this baseline.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_793213869456f9be'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_793213869456f9be
SQL text: select count(*) from t
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy89596ecb Plan id: 2304339659
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
35 rows selected.
SYS@SAM AS SYSDBA>
Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'NO' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = NO
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .12 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE
COMPLETE
Rows Processed: 1 1
Elapsed Time(ms):
2.817 2.538 1.11
CPU Time(ms): 2.888 2.555
1.13
Buffer Gets: 258 4 64.5
Physical Read
Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0
0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 0
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Now evolve the plan with commit option as YES.
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'YES' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = YES
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .07 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Plan was changed to an accepted plan.
Baseline
Plan Test Plan Stats Ratio
------------- ---------
-----------
Execution Status: COMPLETE COMPLETE
Rows Processed:
1 1
Elapsed Time(ms): 2.804 2.519 1.11
CPU
Time(ms): 2.888 2.555 1.13
Buffer Gets:
258 4 64.5
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0
0
Physical Write Bytes: 0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES YES NO
SQL_a00583ac0188a3d6 SQL_PLAN_a01c3ph0sj8yq562c7608
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_tex YES YES NO
t LIKE '%Select R.Rowid From Fnd_Concur%' order by L
AST_LOAD_TIME asc
TEST@SAM > set autotrace on
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy89596ecb" used for this statement
TEST@SAM >
If we need to drop the original plan, we can do by
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a00583ac0188a3d6',
plan_name => 'SQL_PLAN_a01c3ph0sj8yq562c7608');
dbms_output.put_line(drop_result);
end;
/
References:http://www.oracle.com/technetwork/articles/database/create-sql-plan-baseline-2237506.html
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
Parameters
Two parameters allow you to control SPM.The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will be automatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.
The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.
Note:Setting optimizer_capture_sql_plan_baselines = true permanently will result in a SQL plan baseline being created for every repeatable SQL statement on the system. That includes all of the recursive SQL Oracle executes on your behalf as well as every 'select sysdate from dual;' or 'select * from v$sql;' the DBA may do on the system. This could lead to a very large number of SQL plan baselines being captured and an increased foot print in the SYSAUX tablespace. There are some customers who do have optimizer_capture_sql_plan_baselines = true and they have not encountered any problem other than a large SYSAUX tablespace!
Scenario where a query is working fine in one instance and working bad in another instance. Thus i will exporting/importing the baseline from one instance to another.
SYS@SAM AS SYSDBA> show parameter baseline;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
Identify the problematic sql text using awr, statspack etc.
Get the sql_id for the sql_text- "Select R.Rowid From Fnd_Concur%"
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%Select R.Rowid From Fnd_Concur%';
5ugwm6cpfpr7b
create a tuning set
SYS@SAM AS SYSDBA> begin
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01', DESCRIPTION => 'TEST SQL TUNE SET');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Populate sql tuning set
SYS@SAM AS SYSDBA> DECLARE
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT VALUE(P) FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''5ugwm6cpfpr7b''')) p;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> '5ugwm6cpfpr7b_STS01', POPULATE_CURSOR=>CUR);
CLOSE CUR;
END;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> SYS@SAM AS SYSDBA>
List out sql tuning set
SYS@SAM AS SYSDBA>
SELECT plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01'));
PLAN_HASH_VALUE
---------------
2836784050
LOAD DESIRED PLAN FROM ‘SQL TUNING SET’ AS SQL PLAN BASELINE
SYS@SAM AS SYSDBA> DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'5ugwm6cpfpr7b_STS01', BASIC_FILTER=> 'PLAN_HASH_VALUE=
''2836784050''' );
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
VERIFY IF SQL PLAN BASELINE GOT CREATED SUCCESSFULLY
select * from dba_sql_plan_baselines;
select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
========================================================================
Now we have to pack this sql baseline.
Create a staging table in system schema. It cannot be under sys schema
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage1');
END;
/
pack the sql baselines
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
SQL_HANDLE => 'SQL_a00583ac0188a3d6');
END;
/
Now export the sql baselines
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@SAM AS SYSDBA> CREATE OR REPLACE DIRECTORY test_dir AS '/xxx_out/dump';
Directory created.
SYS@SAM AS SYSDBA> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
Grant succeeded.
SYS@SAM AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@SAM2 /xxx_out> expdp system/*** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log
compression=all
Export: Release 11.2.0.3.0 - Production on Thu Jan 5 06:00:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp
logfile=stage.log compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
. . exported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/xxx_out/dump/stage.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 06:09:
--------------------------------------------------------------------------------------------
Import the dump to the target database.
check first if there any baselines present or not.
select * from dba_sql_plan_baselines;
Import the sql baseline
oracle@SAM1 /oracle/stage> impdp system/***** tables=stage1 dumpfile=stage.dmp
Import: Release 11.2.0.3.0 - Production on Thu Jan 5 06:17:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=stage1 dumpfile=stage.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 06:17:33
oracle@ /oracle/stage>
unpack the sql baseline
sqlplus system/<password>
SET SERVEROUTPUT ON
SYSTEM> DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STAGE1',
table_owner => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END; 2 3 4 5 6 7 8
9 /
Plans Unpacked: 1
PL/SQL procedure successfully completed.
SYSTEM@SAM1>
check if the baselines are there or not
SYSTEM@SAM1 > select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
check if the plan is accepted or not.
SYS@SAM1 AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
If the plan is not accepted, then we have to eveolve it, which is discussed later in the post.
========================================================================
Scenario-2 -Force the optimizer to choose a plan. The first query runs against an unindexed column, does a full scan. And then an index have been added but the optimizer still chooses to do a full table scan. By Evolving the baseline, we forced the optimizer to choose the index and at last we removed the original plan which was doing a full table scan.
oracle@SAM2 ~> sqlplus test/welcome123
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 02:57:07 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TEST@SAM >
TEST@SAM > create table t as select * from applsys.FND_OBJECTS;
Table created.
TEST@SAM >
TEST@SAM > exec DBMS_STATS.GATHER_SCHEMA_STATS ('test');
PL/SQL procedure successfully completed.
TEST@SAM > show parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
-------------------- ------------------------------------------ ------------------------------------------ --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376 select count(*) from t YES YES NO
Lets create an index and create a new baseline
TEST@SAM > create index t_idx on t (OBJECT_ID);
Index created.
TEST@SAM >TEST@SAM > exec dbms_stats.gather_schema_stats ('test');
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> alter system flush shared_pool;
System altered.
SYS@SAM AS SYSDBA>
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM >
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
Session altered.
TEST@SAM >
TEST@SAM > set autotrace on;
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy3fdbb376" used for this statement
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES NO NO
check the execution plan for this baseline.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_793213869456f9be'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_793213869456f9be
SQL text: select count(*) from t
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy89596ecb Plan id: 2304339659
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
35 rows selected.
SYS@SAM AS SYSDBA>
Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'NO' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = NO
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .12 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE
COMPLETE
Rows Processed: 1 1
Elapsed Time(ms):
2.817 2.538 1.11
CPU Time(ms): 2.888 2.555
1.13
Buffer Gets: 258 4 64.5
Physical Read
Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0
0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 0
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Now evolve the plan with commit option as YES.
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'YES' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = YES
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .07 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Plan was changed to an accepted plan.
Baseline
Plan Test Plan Stats Ratio
------------- ---------
-----------
Execution Status: COMPLETE COMPLETE
Rows Processed:
1 1
Elapsed Time(ms): 2.804 2.519 1.11
CPU
Time(ms): 2.888 2.555 1.13
Buffer Gets:
258 4 64.5
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0
0
Physical Write Bytes: 0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES YES NO
SQL_a00583ac0188a3d6 SQL_PLAN_a01c3ph0sj8yq562c7608
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_tex YES YES NO
t LIKE '%Select R.Rowid From Fnd_Concur%' order by L
AST_LOAD_TIME asc
TEST@SAM > set autotrace on
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy89596ecb" used for this statement
TEST@SAM >
If we need to drop the original plan, we can do by
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a00583ac0188a3d6',
plan_name => 'SQL_PLAN_a01c3ph0sj8yq562c7608');
dbms_output.put_line(drop_result);
end;
/
References:http://www.oracle.com/technetwork/articles/database/create-sql-plan-baseline-2237506.html
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
Very Nice.Well Said. To the Point and Well Explained.
ReplyDeletetuning oracle database performance
oracle sql performance tuning
sql performance tuning
oracle sql performance tuning and optimization
improve sql query performance
oracle database performance tuning
oracle performance tuning tips
sql query performance tuning
sql tuning for oracle
performance tuning in oracle
oracle sql free download