| 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/awrddrpi.sql -- reports on differences between snapshot pairs
{ORACLE_HOME}/rdbms/admin/awrddrpt.sql -- defaults the dbid and instance number
{ORACLE_HOME}/rdbms/admin/awrextr.sql -- extracts AWR data into a .DMP file
{ORACLE_HOME}/rdbms/admin/awrgdrpi.sql -- WR global compare period report
{ORACLE_HOME}/rdbms/admin/awrgdrpt.sql -- AWR global difference report
{ORACLE_HOME}/rdbms/admin/awrgrpti.sql -- WR RAC global report
{ORACLE_HOME}/rdbms/admin/awrgrpt.sql -- AWR global report
{ORACLE_HOME}/rdbms/admin/awrinfo.sql -- Output general AWR information
{ORACLE_HOME}/rdbms/admin/awrload.sql -- supports loading .DMP file data into AWR
{ORACLE_HOME}/rdbms/admin/awrrpt.sql -- defaults to current instance
{ORACLE_HOME}/rdbms/admin/awrrpti.sql -- select dbid and instance
{ORACLE_HOME}/rdbms/admin/awrsqrpi.sql -- difference report between two snapshots
{ORACLE_HOME}/rdbms/admin/awrsqrpt.sql -- same as awrsqrpi but defaults dbid & instance |
| |
| 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 |
