0 Automatic Workload Repository (AWR)

From 10g, there are 2 new oracle background processes: MMON ( Memory Monitor) and MMNL ( Memory Monitor Light). Those 2 processes take the snapshot for the system performance related matrix ( default is 6o minutes ) and store in the SGA shared pool . When Shared Pool does not have enough space, the MMNL would write them to the SYSAUX table space.

 

• Automatic Workload Repository (AWR) related tables
• What does AWR collect :  V$STATISTICS_LEVEL
• Create AWR Snapshot
• Delete the snapshot
• Manage AWR setting
• Create the baseline
• Drop the baseline
• Create AWR report
• Estimate the AWR space usage: utlsyxsz.sql

Automatic Workload Repository (AWR) related tables

• WRM$ : Meta data
• WRI$ : Internal
• WRH$ : History

What does AWR collect :  V$STATISTICS_LEVEL

The parameter statistics_level is defined what level is currently set. There are three levels:

BASIC,TYPICAL and ALL. BASIC means close all the AWR collection.

show parameter statistics_level

The V$statistics_level view shows each collection set defined for each level.

select statistics_name,session_status,session_settable,system_status,activation_level,statistics_view_name from v$statistics_level;

 

List number of snapshot

select snap_id,end_interval_time from DBA_HIST_SNAPSHOT

order by snap_id

 

create AWR Snapshot

select count(*) from wrh$_active_session_history;

begin

dbms_workload_repository.create_snapshot();

end;

/

select dbms_workload_repository.create_snapshot() from dual;

 

Delete the snapshot

 

exec  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id => 48,high_snap_id => 48,dbid=>2102609525)

Manage AWR setting

col SNAP_INTERVAL format a20

col RETENTION format a20

col TOPNSQL format a10

select * from DBA_HIST_WR_CONTROL;

• SNAP_INTERVAL: take snapshot every 1 hr.
• RETENTION: 8 days

Use dbms_workload_repository.modify_snapshot_settings to change the SNAP interval and retention. The unit is in minute.

exec dbms_workload_repository.modify_snapshot_settings( interval=>30,retention=>10080)

Create the BASELINE

 

col baseline_name format a20

SELECT baseline_id, baseline_name, start_snap_id, end_snap_id

FROM dba_hist_baseline;

exec DBMS_WORKLOAD_REPOSITORY.create_baseline( start_snap_id=> 40,end_snap_id=> 42,baseline_name=>'BASELINE1');

Drop the baseline

exec DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name=>'BASELINE1');

Create AWR report

The report is output to the current directory if no path is provided.

• @$ORACLE_HOME/rdbms/admin/awrinfo.sql –> Space usage for the current AWR.
• @$ORACLE_HOME/rdbms/admin/awrrpt.sql –> Use 2 Snap id and generate the report.
• @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql –> Use 2 Snap id and the SQL ID  to generate the report. The SQL ID can be found in the V$SQL. ( V$SQL store the SQL in the shared pool).

 

Sample report for awrrpt.sql

Sample report for awrsqrpt.sql

 

Estimate the AWR space usage: utlsyxsz.sql

utlsyxsz.sql can be found under $ORACLE_HOME/rdbms/admin

 

 

Reference

• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/CMI6/Default.aspx
• http://learnwithme11g.wordpress.com/tag/utlsyxsz-sql/