65 lines
2.1 KiB
SQL
65 lines
2.1 KiB
SQL
-- 1. Load AWR into SQL Tuning Set(STS)
|
|
-- Drop SQL Tuning Set (STS)
|
|
BEGIN
|
|
DBMS_SQLTUNE.DROP_SQLSET( SQLSET_NAME => 'VG_STS01' );
|
|
END;
|
|
/
|
|
|
|
-- Create SQL Tuning Set (STS)
|
|
BEGIN
|
|
DBMS_SQLTUNE.CREATE_SQLSET( SQLSET_NAME => 'VG_STS01', DESCRIPTION => 'SQL Tuning Set for loading plan from AWR into SQL Plan Baseline');
|
|
END;
|
|
/
|
|
|
|
-- Load AWR plan into STS
|
|
DECLARE
|
|
REF_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
|
|
BEGIN
|
|
OPEN REF_CUR FOR
|
|
SELECT VALUE(P)
|
|
FROM TABLE( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY ( BEGIN_SNAP => 16184
|
|
, END_SNAP => 16186
|
|
, BASIC_FILTER => ' SQL_ID = ''6h6cvx26whvny'' AND PLAN_HASH_VALUE = 2212718001 '
|
|
, ATTRIBUTE_LIST => 'ALL'
|
|
)
|
|
) p ;
|
|
BEGIN
|
|
DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME => 'VG_STS01'
|
|
, POPULATE_CURSOR => REF_CUR
|
|
) ;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
NULL;
|
|
END;
|
|
DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME => 'VG_STS01'
|
|
, POPULATE_CURSOR => REF_CUR
|
|
) ;
|
|
commit;
|
|
END;
|
|
/
|
|
|
|
/*
|
|
|
|
-- Load AWR plan into STS
|
|
BEGIN
|
|
OPEN :REF_CUR FOR
|
|
SELECT VALUE(P)
|
|
FROM TABLE( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY ( BEGIN_SNAP => 16184
|
|
, END_SNAP => 16186
|
|
, BASIC_FILTER => ' SQL_ID = ''6h6cvx26whvny'' AND PLAN_HASH_VALUE = 2212718001 '
|
|
, ATTRIBUTE_LIST => 'ALL'
|
|
)
|
|
) p ;
|
|
END;
|
|
/
|
|
|
|
print :ref_cur;
|
|
|
|
BEGIN
|
|
DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME => 'VG_STS01'
|
|
, POPULATE_CURSOR => :REF_CUR
|
|
) ;
|
|
END;
|
|
/
|
|
|
|
*/ |