Extracting info from AWR

The Automatic Workload Repository stores a wealth of performance related data in an Oracle 10g Database. Historical data can be viewed from the DBA_HIST* views depending on the retention period.

One can either write a query against any of the dba_hist_* views or use the API
dbms_sqltune.select_workload_repository

For e.g
To find out all sql stmts which took more than 1 hour to execute between 7:00 am and 10:00 am this morning

• Find the snapshots for that period
select snap_id,BEGIN_INTERVAL_TIME from DBA_HIST_SNAPSHOT
where BEGIN_INTERVAL_TIME between to_timestamp('09-JAN-2007 07:00:00','DD-MON-YYYY HH24:MI:SS')and to_timestamp('09-JAN-2007 10:00:00','DD-MON-YYYY HH24:MI:SS')


5064 09-JAN-07 09.00.45.370 AM
5062 09-JAN-07 07.00.45.786 AM
5063 09-JAN-07 08.00.47.623 AM

• Find the sql ids of statements which meet the criteria

SQL> select sql_id,ELAPSED_TIME_TOTAL,ELAPSED_TIME_DELTA from
dba_hist_sqlstat where snap_id between 5062 and 5064 and ELAPSED_TIME_TOTAL > 3600000000


5umujs4y5q05f 252683861535 14085330457
5umujs4y5q05f 266739511867 14055650332
5umujs4y5q05f 280852257550 14112745683

• Find the sql statement corresponding to this sql_id

SQL> select sql_text from dba_hist_sqltext where sql_id='5umujs4y5q05f';

SELECT A.JOURNAL_ID, B.SOURCE, B.ACCOUNTING_PERIOD, A.ACCOUNT, A.DEPTID, A.PROJE
CT_ID, A.CLASS_FLD, A.MONETARY_AMOUNT, A.LINE_DESCR, A.JRNL_LN_REF, TO_CHAR(A.JO
URNAL_DATE,'YYYY-MM-DD'), B.JRNL_HDR_STATUS, TO_CHAR(SYSDATE,'YYYY-MM-DD') FR
OM PS_JRNL_LN A, PS_JRNL_HEADER B, PS_DEPT_TBL C WHERE B.BUSINESS_UNIT = A.BU
SINESS_UNIT AND B.JOURNAL_ID = A.JOURNAL_ID AND B.JOURNAL_DATE = A.JOU
RNAL_DATE AND B.UNPOST_SEQ = A.UNPOST_SEQ AND ( C.EFFDT = (SE
LECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED WHERE C.SETID = C_ED.SETID
AND C.DEPTID = C_ED.DEPTID AND C_ED.EFFDT <= SYSDATE)
AND A.BUSINESS_UNIT = 'UNSYD' AND A.LEDGER = 'ACTUALS' AND A.CURREN
CY_CD = 'AUD' AND C.DEPTID = A.DEPTID AND B.FISCAL_YEAR = :1 A
ND B.ACCOUNTING_PERIOD BETWEEN :2 AND :3 AND ( A.ACCOUNT = :4 OR A.A
CCOUNT = :5 OR ( A.ACCOUNT = :6 OR A.ACCOUNT = :7 AND A.JOURNA
L_ID IN (SELECT D.JOURNAL_ID FROM PS_JRNL_LN D WHERE ( D.ACCOUNT = :8
OR D.ACCOUNT = :9)))) AND B.JRNL_HDR_STATUS IN ('P','U') ) ORDER BY 3
, 1, 4


You can obtain the same result with a single statement using the dbms_sqltune.select_workload_repository API as shown below

SELECT VALUE(P).sql_text FROM table(dbms_sqltune.select_workload_repository(5062,5064,'elapsed_time > 3600000000')) P ;

SELECT A.JOURNAL_ID, B.SOURCE, B.ACCOUNTING_PERIOD, A.ACCOUNT, A.DEPTID, A.PROJE
CT_ID, A.CLASS_FLD, A.MONETARY_AMOUNT, A.LINE_DESCR, A.JRNL_LN_REF, TO_CHAR(A.JO
URNAL_DATE,'YYYY-MM-DD'), B.JRNL_HDR_STATUS, TO_CHAR(SYSDATE,'YYYY-MM-DD') FR
OM PS_JRNL_LN A, PS_JRNL_HEADER B, PS_DEPT_TBL C WHERE B.BUSINESS_UNIT = A.BU
SINESS_UNIT AND B.JOURNAL_ID = A.JOURNAL_ID AND B.JOURNAL_DATE = A.JOU
RNAL_DATE AND B.UNPOST_SEQ = A.UNPOST_SEQ AND ( C.EFFDT = (SE
LECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED WHERE C.SETID = C_ED.SETID
AND C.DEPTID = C_ED.DEPTID AND C_ED.EFFDT <= SYSDATE)
AND A.BUSINESS_UNIT = 'UNSYD' AND A.LEDGER = 'ACTUALS' AND A.CURREN
CY_CD = 'AUD' AND C.DEPTID = A.DEPTID AND B.FISCAL_YEAR = :1 A
ND B.ACCOUNTING_PERIOD BETWEEN :2 AND :3 AND ( A.ACCOUNT = :4 OR A.A
CCOUNT = :5 OR ( A.ACCOUNT = :6 OR A.ACCOUNT = :7 AND A.JOURNA
L_ID IN (SELECT D.JOURNAL_ID FROM PS_JRNL_LN D WHERE ( D.ACCOUNT = :8
OR D.ACCOUNT = :9)))) AND B.JRNL_HDR_STATUS IN ('P','U') ) ORDER BY 3
, 1, 4


P.S You have to be licensed to use the Diagnostics Pack to use AWR and licensed to use Oracle Tuning Pack to use SQL Tuning Sets. For more information please refer to the Oracle Licensing documentation at
http://download-west.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#sthref80