Monday, February 18, 2013

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

image

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

image

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

 image

List number of snapshot

select snap_id,end_interval_time from DBA_HIST_SNAPSHOT
order by snap_id

image

 

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;

 

image

image

image

image

image

Delete the snapshot

 

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

image

image

Manage AWR setting

col SNAP_INTERVAL format a20
col RETENTION format a20
col TOPNSQL format a10
select * from DBA_HIST_WR_CONTROL;

image

  • 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)

image

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');

image

Drop the baseline

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

image

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

image

Sample report for awrsqrpt.sql

image

 

Estimate the AWR space usage: utlsyxsz.sql

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

 

image

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates