dba:oracle:docs:awr
Table of Contents
[DOC] AWR
Configuration
Actual config
SELECT * FROM dba_hist_wr_control ;
or, only timings:
SELECT EXTRACT( DAY FROM snap_interval) *24*60+ EXTRACT( HOUR FROM snap_interval) *60+ EXTRACT( MINUTE FROM snap_interval ) "Snapshot Interval", EXTRACT( DAY FROM retention) *24*60+ EXTRACT( HOUR FROM retention) *60+ EXTRACT( MINUTE FROM retention ) "Retention Interval" FROM dba_hist_wr_control /
retention
modify
Modify, units are minutes:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 20160); END; /
frequency
Modify, units are minutes:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( INTERVAL => 60); END; /
Number of SQL's captured on each snapshot
Modify, units are minutes:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( topnsql => 100); END; /
Basic queries
List AWR snapshots
SELECT DBID, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN FROM dba_hist_snapshot ORDER BY 1;
View SQL_TEXT of selected SQL_ID
SELECT SQL_TEXT FROM WRH$_SQLTEXT WHERE SQL_ID='<SQL_ID>';
dba/oracle/docs/awr.txt · Last modified: 2023/01/31 08:22 by dodger