使用包DBMS_WORKLOAD_REPOSITORY修改AWR的預設設定

Modifying Snapshot Settings

You can adjust the interval, retention, and captured Top SQL of snapshot generation for a specified database Id, but note that this can affect the precision of the Oracle diagnostic tools.

The INTERVAL setting affects how often in minutes that snapshots are automatically generated. The RETENTION setting affects how long in minutes that snapshots are stored in the workload repository. The TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, and Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior. for the AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the cursor cache, though by doing so (or by setting the value to a very high number) may lead to possible space and performance issues since there will more data to collect and store. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:

 

BEGIN
              DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, 
              interval => 30, topnsql => 100, dbid => 3310949047);
END;
/