SQL Queries to View, Modify Awr Snapshot Interval Setting

advertisements

_____________________________________________________________________________________________________________________

To view the current settings of AWR (Automatic Workload Repository) snapshot retention and interval, you can query the DBA_HIST_WR_CONTROL view. Here's a SQL query along with a brief explanation and example output

col SNAP_INTERVAL for a40 col RETENTION for a40 

SELECT
  SNAP_INTERVAL,
  RETENTION
FROM
  DBA_HIST_WR_CONTROL;

advertisements

 

The query retrieves the snapshot interval and retention settings from the DBA_HIST_WR_CONTROL view. Here's what the query does: SNAP_INTERVAL: This column represents the interval between AWR snapshots. The value is stored in the format DAYS HH:MI:SS, indicating the number of days, hours, minutes, and seconds between snapshots. RETENTION: This column indicates the retention period for AWR snapshots. The value is stored in the format DAYS HH:MI:SS, representing the number of days, hours, minutes, and seconds that AWR snapshots are retained in the database.

SNAP_INTERVAL				 RETENTION
---------------------------------------- ----------------------------------------
+00000 01:00:00.0			 +00008 00:00:00.0

To modify the AWR (Automatic Workload Repository) snapshot interval setting in Oracle, you need to adjust the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. Here's a script along with a brief explanation, theory, and example output to guide you in modifying the AWR snapshot interval setting: 

 Method 1

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 7, interval => 60);

This single line of code will set the snapshot retention to 7 days and the snapshot interval to 60 minutes. Please ensure that you have the necessary privileges to execute the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. 

Method 2

 

DECLARE
  retention_num NUMBER;
  interval_num NUMBER;
BEGIN
  -- Set the desired snapshot retention period (in days)
  retention_num := 8;

  -- Set the desired snapshot interval (in minutes)
  interval_num := 60;

  -- Modify the snapshot settings
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => retention_num*24*60,
    interval => interval_num
  );
  
  -- Commit the changes
  COMMIT;
  
  DBMS_OUTPUT.PUT_LINE('AWR Snapshot Settings Modified Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    ROLLBACK;
END;
/

This PL/SQL script modifies the AWR snapshot settings by invoking the

 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. Here's what the script does: retention_num: You can set this variable to the desired retention period for AWR snapshots, in days. For example, setting it to 7 retains AWR snapshots for the last 7 days. interval_num: Set this variable to the desired interval between AWR snapshots, in minutes. For instance, setting it to 60 captures AWR snapshots every hour. DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS: This procedure is used to modify the AWR snapshot settings. It takes the retention and interval parameters as arguments. COMMIT: This statement commits the changes made by modifying the snapshot settings. DBMS_OUTPUT.PUT_LINE: This prints a message indicating the successful modification of AWR snapshot settings. EXCEPTION: This section handles any exceptions that might occur during the modification process. If an error occurs, it prints an error message and rolls back the transaction. Theory: The AWR is a built-in Oracle feature used to collect and store performance statistics for diagnosing database performance issues. AWR snapshots capture performance data at regular intervals, and you can adjust the retention period and interval settings based on your monitoring and diagnostic needs. By modifying the snapshot interval setting, you control the frequency of data collection, which can help you strike a balance between capturing detailed performance information and minimizing the impact on system resources.

_____________________________________________________________________________________________________________________

Website Stats