I already had another blog for sql profile however at times we have scenario where sql profile is not used and we still see sql plan flip . In such case we have to prefer creating sql plan baseline .
There are nany articles online already on sql baselines however i wanted to create my own
The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default. Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.
Creating Sql plan baseline using Sql tuning sets :
=> Drop existing baseline for same sqlid if any
BEGIN
DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_f9948h4y2aa22');
END;
/
==> Create new sql plan baseline :
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_f9948h4y2aa22',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/
==> Load all plan details in sts :
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>6450, end_snap=>6451,basic_filter=>'sql_id = ''f9948h4y2aa22''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_f9948h4y2aa22', populate_cursor=>cur);
CLOSE cur;
END;
/
-- if only current plan needs to be uploaded we can use cursor
DECLARE
cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cursor1 FOR SELECT VALUE(p)
FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''94rn6s4ba24wn''')) p;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);
END;
/
==> Check the loaded Plan details.
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 => 'STS_f9948h4y2aa22')
);
==> Load Good SQL Plan from STS to Baseline
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'STS_f9948h4y2aa22',
basic_filter=>'plan_hash_value = ''3166698470'''
);
END;
/
Or
Set serveroutput on;
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'BI_LOAD',sqlset_owner=>'PAYER1',basic_filter=>'sql_id='||CHR(39)||'08tz4k2shvbrr'||CHR(39)||' and plan_hash_value=2073609144',fixed=>'YES',enabled=>'YES');
dbms_output.put_line(my_plans);
END;
/
Select sql_handle, sql_text, plan_name, accepted, enabled, fixed, reproduced, executions
FROM dba_sql_plan_baselines;
Using DBMS_SPM.ALTER_SQL_PLAN_BASELINE disable the bad plan :
SQL> variable cnt number;
SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE => 'SYS_SQL_bf5c9b08f72bde3e',PLAN_NAME=> 'SQL_PLAN_byr4v13vkrrjy42949306',
ATTRIBUTE_NAME => 'enabled',ATTRIBUTE_VALUE => 'NO');
set linesize 125
col sql_id format a14
col sql_handle format a22
col plan_name format a32
col sql_text format a40
col enabled format a8
col accepted format a8
SELECT
dbms_sql_translator.sql_id(sql_text) AS sql_id,
sql_handle ,
plan_name ,
enabled ,
accepted ,
reproduced ,
fixed
FROM
dba_sql_plan_baselines
WHERE
dbms_sql_translator.sql_id(sql_text) = '3wwhfmb2ztb40';
==> Drop baseline when done
set serveroutput on;
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (plan_name=>'SQL_PLAN_fn4qykbuqnt9bcf84f431');
dbms_output.put_line(my_plans);
END;
/
Other Ways to force sql plan without STS as per Doc ID 2885167.1:
variable x number
BEGIN
:x := dbms_spm.load_plans_from_awr(
begin_snap => 292,
end_snap => 294,
basic_filter => q'# sql_id='3wwhfmb2ztb40' and plan_hash_value='3080277828' #');
END;
/
print x
Export Import Baseline :
Oracle Database supports the export and import of SQL plan baselines using its import and export utilities or Oracle Data Pump. Use the DBMS_SPM package to define a staging table, which is then used to pack and unpack SQL plan baselines.
To import a set of SQL plan baselines from one system to another:
On the original system, create a staging table using the CREATE_STGTAB_BASELINE procedure:
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'stage1');
END;
/
This example creates a staging table named stage1.
Pack the SQL plan baselines you want to export from the SQL management base into the staging table using the PACK_STGTAB_BASELINE function:
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
creator => 'dba1');
END;
/
This example packs all enabled plan baselines created by user dba1 into the staging table stage1. You can select SQL plan baselines using the plan name (plan_name), SQL handle (sql_handle), or by any other plan criteria. The table_name parameter is mandatory.
Export the staging table stage1 into a flat file using the export command or Oracle Data Pump.
Transfer the flat file to the target system.
Import the staging table stage1 from the flat file using the import command or Oracle Data Pump.
Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function:
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'stage1',
fixed => 'yes');
END;
/
This example unpacks all fixed plan baselines stored in the staging table stage1.
Export Import STS ( Sql tuning Sets ) :
Transferring SQL Tuning Sets
In the examples listed above, the tests have been performed on the same system. In reality you are more likely to want to create a tuning set on your production system, then run the SQL Performance Analyzer against it on a test system. Fortunately, the DBMS_SQLTUNE package allows you to transport SQL tuning sets by storing them in a staging table.
First, create the staging table using the CREATE_STGTAB_SQLSET procedure.
CONN sys/password@prod AS SYSDBA
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB',
schema_name => 'SPA_TEST_USER',
tablespace_name => 'USERS');
END;
/
Next, use the PACK_STGTAB_SQLSET procedure to export SQL tuning set into the staging table.
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'SPA_TEST_SQLSET',
sqlset_owner => 'SYS',
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA_TEST_USER');
END;
/
Once the SQL tuning set is packed into the staging table, the table can be transferred to the test system using Datapump, Export/Import or via a database link. Once on the test system, the SQL tuning set can be imported using the UNPACK_STGTAB_SQLSET procedure.
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => '%',
sqlset_owner => 'SYS',
replace => TRUE,
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA_TEST_USER');
END;
/
The SQL tuning set can now be used with the SQL Performance Analyzer on the test system.
Reference :
How to Create A SQL Plan Baseline From A Historical Execution Plan In The Automatic Workload Repository (AWR) [RDBMS Version 12.2 or Higher] (Doc ID 2885167.1)
https://oracle-base.com/articles/11g/sql-plan-management-11gr1
