Simple question – and more a reminder to myself as I’d assume that many people have their examples somewhere already. But in upgrade and migration projects it’s sometime useful to simply select SQL statements directly from between two AWR snapshots into a SQL Tuning Set (STS). This will give you the possibility to later put a failing plan directly into a SQL Plan Baseline, part of the free Enterprise Edition’s SQL Plan Management feature.
Please find a scripted example for download here:
You’ll need to create a SQL Tuning Set and simply fill it with staments from an AWR snapshot interval:
exec DBMS_SQLTUNE.CREATE_SQLSET('MD_STS','SYS')
/
DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_workload_repository(
begin_snap => 4711,
end_snap => 4788,
basic_filter => 'parsing_schema_name not in
(''DBSNMP'',''SYS'',''ORACLE_OCM'')',
ranking_measure1 => 'elapsed_time',
result_limit => 250)) p;
dbms_sqltune.load_sqlset('MD_STS', cur);
close cur;
END;
/
And fixing a failing plan and overwriting it with the plan from before the upgrade would work that way:
DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'MD_STS', basic_filter => 'sql_id="b25h7qc53gowp"', fixed => 'YES'); END; /
–Mike
1 Response
[…] How to select statements from AWR? […]