# AWR, ADDM and ASH
report for the period.
sqlplus / as sysdba
@?/rdbms/admin/ashrpt.sql #
activity during a period of time
@?/rdbms/admin/awrrpt.sql # database performance within a specified
period of time
@?/rdbms/admin/awrrpti.sql #
Workload Repository Report Instance (RAC)
@?/rdbms/admin/addmrpt.sql #
database performance findings
@?/rdbms/admin/awrsqrpt.sql #
historical performance of a single SQL statement (identified by SQL_ID)
@?/rdbms/admin/sqltrpt.sql
# In 11gR2 there are
two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC
awrgrpt.sql -- AWR Global Report (RAC) (global report)
awrgdrpt.sql -- AWR Global Diff Report (RAC)
# Some other
important scripts under $ORACLE_HOME/rdbms/admin
spawrrac.sql -- Server Performance RAC report
awrddrpt.sql -- Period diff on current instance
# AWR
Create
EXECUTE dbms_workload_repository.create_snapshot();
Frequency
begin
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,15); end;
# every 15
minutes in 1440 (one day)
Drop
exec
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
high_snap_id =>
32, dbid => 3310949047);
Modify
interval and time
exec
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, dbid => 123456789); #retention is in mins
Modify interval and topnsql
Exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 43200,
interval => 30, topnsql => 100, dbid => 123456789);
# awr views
select owner, view_name
from dba_views
where view_name like 'DBA\_HIST\_%' escape '\';
# awr cpu usage
report
select a.metric_name, a.begin_time, a.intsize,
a.num_interval,
a.minval, a.maxval, a.average, a.standard_deviation
sd, b.metric_unit
from dba_hist_sysmetric_summary a,
dba_hist_metric_name b
where a.metric_id = 2075
and a.metric_id=b.metric_id
order by 2
/
