User Tools

Site Tools


oracle:awr

[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 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>';
oracle/awr.txt · Last modified: 2019/07/18 09:17 (external edit)