1)
Here we are creating a BASELINE profiles from previous runs using AWR reports... if we think the execution plan in one database is working fine and want to have the same consistant execution plan for sql.
login as system user
1)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'BASIC_PROC_NAME',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/
2)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>4176, end_snap=>4179,basic_filter=>'sql_id = ''0cadrfdx9t0z1e''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'BASIC_PROC_NAME', populate_cursor=>cur);
CLOSE cur;
END;
/
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 => 'BASIC_PROC_NAME')
);
3)
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'BASIC_PROC_NAME',
basic_filter=>'plan_hash_value = ''4972313474'''
);
END;
/
Make sure you see the row….
SELECT * FROM dba_sql_plan_baselines ;
select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;
4) To backup the sql baseline to other environment... then continue.
exec DBMS_SPM.CREATE_STGTAB_BASELINE('SQL_PROF_BASELINE','SCHEMA_NAME');
select table_name,owner from dba_tables where table_name='SQL_PROF_BASELINE';
SELECT * FROM dba_sql_plan_baselines;
no rows initially
5)
var x number;
begin
:x := DBMS_SPM.PACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME', sql_handle => 'SQL_300901f0272d8837', plan_name => 'SQL_PLAN_30281y0mkv21r64b2d935' );
end;
/
6)
export ....
7)
Purge the existing which is not good....
exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');
8)
import.... as system user
unpack...
7)
var x number;
begin
:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME');
end;
/
SELECT * FROM dba_sql_plan_baselines;
***************************** END **********************************
DECLARE
v_dropped_plans number;
BEGIN
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_300901f0272d8837'
);
DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/
Hope this Helps....
Here we are creating a BASELINE profiles from previous runs using AWR reports... if we think the execution plan in one database is working fine and want to have the same consistant execution plan for sql.
login as system user
1)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'BASIC_PROC_NAME',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/
2)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>4176, end_snap=>4179,basic_filter=>'sql_id = ''0cadrfdx9t0z1e''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'BASIC_PROC_NAME', populate_cursor=>cur);
CLOSE cur;
END;
/
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 => 'BASIC_PROC_NAME')
);
3)
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'BASIC_PROC_NAME',
basic_filter=>'plan_hash_value = ''4972313474'''
);
END;
/
Make sure you see the row….
SELECT * FROM dba_sql_plan_baselines ;
select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;
4) To backup the sql baseline to other environment... then continue.
exec DBMS_SPM.CREATE_STGTAB_BASELINE('SQL_PROF_BASELINE','SCHEMA_NAME');
select table_name,owner from dba_tables where table_name='SQL_PROF_BASELINE';
SELECT * FROM dba_sql_plan_baselines;
no rows initially
5)
var x number;
begin
:x := DBMS_SPM.PACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME', sql_handle => 'SQL_300901f0272d8837', plan_name => 'SQL_PLAN_30281y0mkv21r64b2d935' );
end;
/
6)
export ....
7)
Purge the existing which is not good....
exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');
8)
import.... as system user
unpack...
7)
var x number;
begin
:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME');
end;
/
SELECT * FROM dba_sql_plan_baselines;
***************************** END **********************************
DECLARE
v_dropped_plans number;
BEGIN
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_300901f0272d8837'
);
DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/
Hope this Helps....
