How to change AWR snapshot settings

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

 

Changing AWR snapshot settings like retention, interval, topnsql

Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql.

-– Get the dbid:

SQL> select dbid from v$database;

DBID
--------------
3970683413

– retention=>value in minutes so (20 days * 24 (hours per day) * 60 minutes per hour = 28800), max value can be set by passing a value of 0 which means forever
– interval=>30min (snap at this interval), a value of 0 will turn off AWR
– topnsql – top N sql size, specify value of NULL will keep the current setting

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>28800, interval=>30, topnsql=>100, dbid=>3970683413);

PL/SQL procedure successfully completed.

–- shows retention and interval after it was modified
SQL> 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,
topnsql
from dba_hist_wr_control;

SNAPSHOT_INTERVAL RETENTION_INTERVAL TOPNSQL
----------------- ------------------ ----------
               30              28800 100

-- Change snapshot interval to 15 minutes
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>15);
PL/SQL procedure successfully completed.

Oracle has published suggested storage requirements for AWR based on the activity and complexity of your 10g database:

	Active Sessions	number of CPU's (num_cpus)	number of schema objects	Monthly space required (gig)
Small	10	2	500	2
Medium	20	8	5,000	8
Large	100	32	50,000	20

Oracle uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically, when you create a new Oracle database.  

To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:

SELECT a.job_name, a.enabled, c.window_name, c.schedule_name, c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
      And a.schedule_name=b.window_group_name
      And b.window_name=c.window_name;

You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable('GATHER_STATS_JOB');

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable('GATHER_STATS_JOB');

Useful Queries

Standard reports
The SQL*Plus scripts are located in $ORACLE_HOME/rdbms/admin directory, the output in either text or HTML (default) format

ASH report (ashrpt.sql)
Helps answer questions about "What's going on right now and who is doing it?" for a specified time period

AWR report (awrrpt.sql)
Breakdown of what was consuming “DB Time” for a specified time period

AWR "diff" report (awrddrpt.sql)
Compares and highlights what changed between two specified time periods.

Prompts for:
    - Html or text type
    - First pair of Begin and End snapshot ids
    - Report name
    - Provides a report name beginning with awrdiff…

AWR "SQL" report (awrsqrpt.sql)
Displays all recorded information about a specific SQL during a specified time period. Good when you want to focus on a particular SQL statement.

To get a quick report of any SQL statement's execution plans within the past 7 days, if you have the statement's SQL ID value:
select * from table(dbms_xplan.display_awr('sqlid'));

If you don't have the SQL statement's SQL ID, it can be found in:
- A standard AWR report or EM DB Console or Grid Control  or
- Query the V$SQL or DBA_HIST_SQLTEXT view:
select sql_id, sql_text from v$sql where lower(sql_text) like '%phrase%';
select sql_id, sql_text from dba_hist_sqltext where lower(sql_text) like '%phrase%';