Sunday, March 3, 2013

ORACLE - AWR

How to  generate AWR report ?
/u01/app/oracle/product/11.2.0/db_1/rdbms/admin> ls -rlt awrr*
-rw-r--r-- 1 oracle dba 1999 Oct 24  2003 awrrpt.sql
-rw-r--r-- 1 oracle dba 7704 Jul 25  2011 awrrpti.sql

Find out Snap ID for a AWR report:
select d.dbid ,i.instance_number ,i.instance_name "Instance" ,d.name "DB name"
,snap_id "Snap Id" ,TO_CHAR(dbhs.end_interval_time,'DD/MM/YYYY HH24:MI') "Snap Started"
,snap_level "Snap Level" from v$database d , dba_hist_snapshot dbhs, v$instance i
where d.dbid = dbhs.dbid and dbhs.startup_time BETWEEN SYSDATE -3 AND SYSDATE ORDER BY 5;

AWR report for a snap ID:
select output FROM TABLE(dbms_workload_repository.awr_report_text(4211587007,1,677,690));
select output FROM TABLE(dbms_workload_repository.awr_report_html(4211587007,1,677,690));

AWR report for a date range:
select output FROM TABLE(dbms_workload_repository.ash_report_html(4211587007,1,SYSDATE-30/1440, SYSDATE-1/1440));
select output FROM TABLE(dbms_workload_repository.ash_report_text(4211587007,1,SYSDATE-30/1440, SYSDATE-1/1440));

AWR report for a SQL ID:
SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_html(4211587007, 1, 677, 690, 'fr0xt5q6dr3zc'));

No comments:

Post a Comment