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.
