SPM Notes

select count(*) from dba_sql_plan_baselines;

select count(distinct sql_handle) from dba_sql_plan_baselines;

select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines
where sql_handle='SYS_SQL_32814757f4b84f6b';


Display SQL_HANDLE having more than one baseline AKA SQL_PLAN
================================================================
select sql_handle from dba_sql_plan_baselines group by sql_handle
having count(*) > 1

select sql_handle,plan_name,OPTIMIZER_COST,enabled,accepted from dba_sql_plan_baselines where sql_handle in
(select sql_handle from dba_sql_plan_baselines group by sql_handle having count(*) > 1)
and PARSING_SCHEMA_NAME='&SCHEMA_NAME'
order by 1 asc,5 desc;


Displaying SQL Plan Baselines
=================================
SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_d90440b9ed3324c0'));

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'&P_Plan_name'));

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'&P_sql_handle'));



To Evolve Plan and Acctpt it after VERIFY
===========================================
-- set long as below to get evolve, displayed on sql-plus
set long 9999

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9') from dual;

--Specify COMMIT=>'NO' , if we need to just verify and report But Not to accept.

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',VERIFY=>'YES',COMMIT=>'NO') from dual;

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&SQL_HANDLE',VERIFY=>'YES',COMMIT=>'NO') from dual;

SELECT DBMS_SPM.evolve_sql_plan_baseline(plan_name => '&plan_name',VERIFY=>'YES',COMMIT=>'NO') from dual;

Load Baseline Plan for A SQL from Cursor Cache
===================================
1) Find SQL_id from v$SQL

SELECT sql_id
FROM v$sql
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND sql_text NOT LIKE '%EXPLAIN%';

2) Load SQL_ID into Baseline from Cursor Cache

SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'gat6z1bc6nc2d');

DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1



Load the Sql Plan Baseline from STS.
========================================

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => '&STS_NAME',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/


set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => '&STS_NAME');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/

To Check Cache for SPM Baseline USAGE
========================================
select decode(SQL_PLAN_BASELINE,null,'NO','YES'),count(*) from V$SQL
group by decode(SQL_PLAN_BASELINE,null,'NO','YES');



disbale Plan for specific sql_handle with specific plan_name
=============================================================
declare
pbsts varchar2(30);
begin
pbsts := dbms_spm.alter_sql_plan_baseline('&SQL_HANDLE','&Plan_Name','accepted','NO');
end;
/

Enable Plan for specific sql_handle with specific plan_name
=============================================================
declare
pbsts varchar2(30);
begin
exec :pbsts := dbms_spm.alter_sql_plan_baseline('SYS_SQL_7de69bb90f3e54d2','SYS_SQL_PLAN_0f3e54d254bc8843','accepted','YES');
end;
/

To drop all the plans associated with a sql_handle.
====================================================
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>null);
END;
/

To drop only a specific sql_handle with specific plan_name
=========================================================
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>'SYS_SQL_PLAN_f4b84f6bbce35b3e');
END;
/

The following PL/SQL code will drop the entire SQL PLAN MANAGEMENT Repositry
============================================================================
connect / as sysdba
select count(*) from dba_sql_plan_baselines;

declare
pgn number;
sqlhdl varchar2(30);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines;
begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;

close hdl_cur;
commit;
end;
/

select count(*) from dba_sql_plan_baselines;



The following PL/SQL code will drop the non-accepted PLANs
============================================================================
connect / as sysdba
select count(*) from dba_sql_plan_baselines where accepted = 'NO' and parsing_schema_name='&SCHEMA_NAME';

declare
pgn number;
sqlhdl varchar2(100);
planname varchar2(100);
cursor hdl_cur is
select distinct sql_handle,plan_name from dba_sql_plan_baselines where accepted = 'NO' and parsing_schema_name='&SCHEMA_NAME';

begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl,planname;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl,plan_name=>planname);
end loop;

close hdl_cur;

commit;
end;
/

select count(*) from dba_sql_plan_baselines where accepted = 'NO' and parsing_schema_name='&SCHEMA_NAME';




The following PL/SQL code will drop baseline except for specified PARSING_SCHEMA's
============================================================================
connect / as sysdba
select count(*) from dba_sql_plan_baselines ;

declare
pgn number;
sqlhdl varchar2(100);
planname varchar2(100);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines where parsing_schema_name not in ('&SCHEMA_NAME1','&SCHEMA_NAME2');

begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;

close hdl_cur;

commit;
end;
/

select count(*) from dba_sql_plan_baselines ;




To PAck SPM BaseLine And unpack on Different/same database
==========================

Step1==> Create staging table
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => '&Table_nam',
table_owner => 'VAZEP',
tablespace_name => 'USERS' );
END;
/


BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => '&Table_nam' );
END;
/

Step2==>Pack Baseline in staging table
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => '&Table_Nam',
table_owner => 'VAZEP');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => '&Table_Nam');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => '&Table_Nam',
sql_handle => '&SQL_HANDLE');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

Step3==>Transfer table to destination database using DB link or exp/imp

exp vazep/Nationwide1 tables=SPM_0715 file=spm_0715_exp.dmp log=spm0715_exp.log

imp vazep/Nationwide1 file=spm_0715_exp.dmp full=y log=spm0715_imp.log

Step4==>Unpack staging table into SPM

SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'spm_0709',
table_owner => 'VAZEP',
creator => 'VAZEP');

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => '&Table_nam' );
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/