AWR – Automatic Workload Repository

Some useful commands to look up AWR information

select d.tablespace_name, d.file_id, file_name, ceil(max((e.block_id+e.blocks)*8192)/1024/1024) min_mb , d.bytes/1024/1024 MB 
from dba_extents e, dba_data_files d
where e.file_id (+) = d.file_id
and d.tablespace_name = 'SYSAUX'
group by d.tablespace_name, d.file_id, file_name, d.bytes;

 Run $ORACLE_HOME/rdbms/admin/awrinfo.sql connected as sysdba.  This would give space usage per AWR component


287679.1 - Space Management In Sysaux Tablespace with AWR in Use
782974.1 - How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ?
329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OT

AWR tracing, Baseline, Snapshot

alter session set events 'immediate trace name awr_test level 16';

Check how many Baselines you have for this AWR repository:

select  count(*) from DBA_HIST_BASELINE;

EXECUTE DBMS_WORKLOAD_REPOSITORY.
DROP_BASELINE (baseline_name => ‘PEAK_MONTH’);
EXECUTE DBMS_WORKLOAD_REPOSITORY.
DROP_SNAPSHOT_RANGE(102, 105);

set pagesize 400
 col baseline_name form a20
  select * from wrm$_baseline order by baseline_id;
  select count(*) from wrh$_sqltext where ref_count != 0 ;
  select count(*) from wrh$_sqltext where sql_id not in (select sql_id from
  wrh$_sqlstat union select sql_id from wrh$_sqlstat_bl);
 select min(snap_id), max(snap_id) from dba_hist_snapshot ;
 .
 select snap_id, count(*)
 from WRH$_SQL_PLAN t
 group by snap_id
 order by snap_id
 /
 .
 select SNAP_ID, TIMESTAMP from WRH$_SQL_PLAN  order by TIMESTAMP;
 /
 .
 

No comments:

Post a Comment