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;
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
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);
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;
/
.
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;
/
.
