Many Oracle DBA's are aware of power of Oracle AWR (Automated Workload Repository) feature. If you have license for it then using it's statistic reports may be very useful to find present hot spots as well one in the history in whole database. Interval retention of snapshot generation is controlled with history period and time between two snapshots.To retrieve how many days retention is setup, use this query:
SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;
Normally DBA control these settings by the predefined Oracle package dbms_workload_repository, by calling it's procedure
dbms_workload_repository.modify_snapshot_settings
In next example, retention period will be defined as 30 days long (43200 min) and the interval between each snapshot is 15 min (every 15 minutes one snapshot will be taken). Those settings are enough and satisfactory for most of the today database configurations:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(43200, 15);
But however long retention policy is, most of DBAs run AWR statistic from time to time, manually. This is true in two cases:
- When something "strange" happened in their database (fire fighter DBA).
- Some of us run from time to time to see if anything "is strange", what ever that means (proactive DBA).
To overcome all mentioned, following solution gives you chance to automate collecting statistic and save them in plain html file, which could be stored and analyzed later with no chance of lost of any moment of life of your database.
/* ---------------------------------------------------------------------------
Filename: create_awr_report_for_database.sql
CR/TR# :
Purpose : In directory defined with v_dir, create awr reports for ALL instances (RAC)
time for analyse is 07-18, so put in crontab to run at 18:16 on daily basis
Date : 09.09.2009.
Author : Damir Vadas
Remarks : Run as privileged user
Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */
set serveroutput on
set linesize 166
set pagesize 600
set trimout on
DECLARE
cursor c_instance is
SELECT instance_number, instance_name
FROM gv$instance
ORDER BY 1
;
c_dir CONSTANT VARCHAR2(256) := '/home/oracle';
v_dir VARCHAR2(256) ;
v_dbid v$database.dbid%TYPE;
v_dbname v$database.name%TYPE;
v_inst_num v$instance.instance_number%TYPE := 1;
v_begin NUMBER;
v_end NUMBER;
v_start_date VARCHAR2(20);
v_end_date VARCHAR2(20);
v_options NUMBER := 8; -- 0=no options, 8=enable addm feature
v_file UTL_FILE.file_type;
v_file_name VARCHAR(50);
BEGIN
-- get database id
SELECT dbid, name
INTO v_dbid, v_dbname
FROM v$database;
-- get end snapshot id
SELECT MAX(snap_id)
INTO v_end
FROM dba_hist_snapshot
WHERE to_char(begin_interval_time,'HH24') = '18';
dbms_output.put_line('end snap_id '||v_end);
-- get start snapshot id
SELECT MAX(snap_id)
INTO v_begin
FROM dba_hist_snapshot
WHERE to_char(begin_interval_time,'HH24') = '07'
AND snap_id < v_end;
dbms_output.put_line('begin snap_id '||v_begin);
SELECT to_char(begin_interval_time,'YYMMDD_HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin
AND instance_number = v_inst_num
;
dbms_output.put_line('v_start_date '||v_start_date);
SELECT to_char(begin_interval_time,'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end
AND instance_number = v_inst_num
;
dbms_output.put_line('v_end_date '||v_end_date);
-- Thanx to Yu Denis Sun - we must have directory defined as v_dir value!
execute immediate('create or replace directory xx_some_temp_dir as '''||v_dir||'''');
-- let's go to real work...write awrs to files...
FOR v_instance IN c_instance LOOP
dbms_output.put_line('v_instance.instance_name:'||v_instance.instance_name);
v_file := UTL_FILE.fopen('XX_SOME_TEMP_DIR', 'awr_' || v_instance.instance_name ||'_'|| v_instance.instance_number || '_' || v_start_date || '_' || v_end_date || '.html', 'w', 32767);
FOR c_report IN (
SELECT output
FROM TABLE(dbms_workload_repository.awr_report_html( v_dbid,
v_instance.instance_number,
v_begin,
v_end,
v_options
)
)
) LOOP
UTL_FILE.PUT_LINE(v_file, c_report.output);
END LOOP;
UTL_FILE.fclose(v_file);
END LOOP;
execute immediate('drop directory xx_some_temp_dir');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
IF UTL_FILE.is_open(v_file) THEN
UTL_FILE.fclose(v_file);
END IF;
BEGIN
execute immediate('drop directory xx_some_temp_dir');
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
/
To run this script there are two minor requirements/constraints that must be obeyed prior running it:
- From my point of view, the most important time to monitor database is 07-18 hours, but you may change it as you wish.
- "xx_some_temp_dir" id dinamically created with v_dir value. So create directory privilege must be available to user which run this script. Keep in mind that Windows has different path definition against Linux (i.e. c_dir CONSTANT VARCHAR2(256) := 'c:\'; ). Change any of those values to apply your situation and configuration.
Last step of automation is to place this script in crontab (or windows schedule) and run it on daily basis at 18:16 (or later).
16 18 * * * . /home/oracle/MY_DB.env && sqlplus -s "/ as sysdba" @/u01/rman_backup/scripts/create_awr_report_for_database.sql
Result will be placed in v_dir directory, one file per day, giving you opportunity to analyze them whenever you like and need. Here is an example for RAC database MY_DB with 4 instances:
Last (but not least) benefit of this approach is that your retention period may be smaller-7 days would be perfectly fine for most of the cases, because there is statistic already recorded for the whole past period. As previously said, you define it like:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(10080, 15);
If you need whole day monitoring (let us say with night shift as well) my suggestion is to modify script to run against different period, assume 18:00-07:00. As you can see, automatically, result will be saved in different file name in the same directory. Dividing monitoring on two parts is, from my point of view, really necessary, and enables DBA to view database in two very different situations OLTP against night (batch) time. Time that in database life really differ in numbers, statistic values and logical interpretation of them.
Cheers!
