How to change AWR snapshot interval in Oracle database

Introduction:

Performance statistics for the database are obtained, analysed and managed by the Automated Workload Repository (AWR). The collected data can be displayed in reports and views.

We can configure and modify the default snapshot interval and retention settings of snapshots. In this blog will discuss about the steps involved to do it.

Description:

AWR snapshot default interval is 60 minutes and its retention are 8 days. In order to investigate detailed database performance related problems, it is recommended to use an interval of 15 minutes and retention of 31 days.

AWS configuration commands:

• • To check the retention:

SQL> select snap_interval, retention from dba_hist_wr_control;

• • To create a manual snapshot:

SQL> exec dbms_workload_repository.create_snapshot;

• • To change retention and interval:

exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 44640) ;

• • To generate AWR report in Non-RAC:

To get metrics for single instance.

conn / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

• • To generate AWR report in RAC:

To gather metrics for all instances of the database.

SQL> @?/rdbms/admin/awrgrpt.sql

• • To find the DBID,instance number and list of snap ids since the last day:

SELECT DBID, instance_number, snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

where begin_interval_time > sysdate – 1

ORDER BY snap_id;

• • To generate HTML report based on snap ids:

set heading off

set trimspool off

set linesize 1500

set termout on

set feedback off

spool awr_from_console.htm

select output from table(dbms_workload_repository.awr_report_html(&dbid, &inst_num, &bid, &eid));

spool off;

• • To generate text report based on snap ids:

set heading off

set trimspool off

set linesize 1500

set termout on

set feedback off

spool awr_from_console.txt

select output from table(dbms_workload_repository.awr_report_text(&dbid, &inst_num, &bid, &eid));

spool off;

Bottom line:

For a better and more accurate investigation of database performance-related issues, the default snapshot interval and snapshot retention can be changed.