Monday Jul 16, 2012

How to select statements from AWR?

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.

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

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers