CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle AWR Report
Version 11.1
AWR Objects
Note: The AWR is licensed as part of the diagnostic pack. Be sure you are licensed before you use this in production.
Source {ORACLE_HOME}/rdbms/admin/awrrpt.sql
{ORACLE_HOME}/rdbms/admin/awrrpti.sql
{ORACLE_HOME}/rdbms/admin/awrinput.sql
 
Query of Available Repository Snapshots

Available Snapshots Query
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;
 
Change Statistics Collection
Statistics Level STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}
SELECT name, value
FROM gv$parameter
WHERE name like 'stat%';

-- modifyable with ALTER SYSTEM and ALTER SESSION
 
Produce Report
Run AWR Report {$ORACLE_HOME}/rdbms/admin/awrrpt.sql
SQL> @?/rdbms/admin/awrrpt.sql
 
Related Topics
Active Session History
DBMS_WORKLOAD_REPOSITORY
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [51 users online]    © 2010 psoug.org