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
0 comments:
Post a Comment