AWR features

MMON transfers cumulative statistics from memory to repository tables on disk

used by ADDM

AWR:Repository Workload set of tables (in SYSAUX tbs : WR*)where performance data can be stored

• AWR : Snapshot

AWR : Snapshot pulls infromations from fixed tables in memory : not ressource-intensive

identify by a unique snapshot ID : unique within repository

RAC : all nodes share a snapshot ID

 

SQL commands

  

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; Creating a AWR snapshot

BEGIN

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.

interval => 30); -- Minutes. Current value

retained if NULL.

END;

/

-- Modifiying snapshot settings

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

low_snap_id => 22,

high_snap_id => 32);

END;

/ Dropping snapshots range

SELECT *

FROM dba_hist_snapshot

WHERE begin_interval_time > TO_DATE ('13/10/2010', 'dd/mm/yyyy')

AND end_interval_time < TO_DATE ('14/10/2010', 'dd/mm/yyyy'); Retried snapid from dates

PARAMETERS : STATISTICS_LEVEL

• Transporting AWR

Oracle Database enables you to transport AWR data between systems. This is useful in cases where you want to use a separate system to perform analysis of the AWR data.

To transport AWR data, you must first extract the AWR snapshot data from the database on the source system,

then load the data into the database on the target system

1 ORACLE_HOME/rdbms/admin/awrextr.sql Extracting AWR Data

2 ORACLE_HOME/rdbms/admin/awrload.sql Loading AWR Dat