REM
/*
 * RedoRate.Sql (c) Tim Onions 2006
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
set verify off
COLUMN a PRINT FORMAT A32 word HEADING 'ARC name'
COLUMN f PRINT FORMAT A20 WORD HEADING 'Log Start Time'
COLUMN l PRINT FORMAT A10 WORD HEADING 'Log End|Time'
COLUMN c PRINT FORMAT A10 WORD HEADING 'ARC End|Time'
COLUMN u PRINT FORMAT 999,999 HEADING 'In use|(mins)'
COLUMN r PRINT FORMAT 999,999 HEADING 'Arc time|(secs)'
COLUMN b PRINT FORMAT 9,999,999 HEADING 'File size|(MB)'
COLUMN t PRINT FORMAT 9,999 HEADING 'Mb/sec'
SET PAGES 60 LINES 140 FEEDBACK on HEAD on
TTITLE off
ACCEPT flavour PROMPT "*nix or Windows? (U/W):"

SELECT SUBSTR(name,INSTR(name,DECODE(UPPER('&&flavour'),'U','/','\'),-1)+1) a
	,TO_CHAR(first_time,'DD-MON-YYYY HH24:MI:SS') f
	,TO_CHAR(next_time,'HH24:MI:SS') l
	,TO_CHAR(completion_time,'HH24:MI:SS') c
	,FLOOR((next_time-first_time)*1440) u
	,(completion_time-next_time)*86040 r
	,(blocks*block_size)/1048576 b
	,FLOOR((blocks*block_size)/(((completion_time-next_time)*8640)+1)/1024/1024) t
FROM v$archived_log
WHERE completion_time BETWEEN SYSDATE-&&HoursFrom/24 AND SYSDATE-&&HoursTo/24
ORDER BY next_time DESC
/

UNDEF hoursfrom
UNDEF hoursto

SET DOC off
/* SUMMARY - Show the redo log files archived within a given time-range,giving size; time in use;
 * time to create to archive; speed of archive file write. 
 *
 * DETAIL - this simple single table SQL*Plus script on the V$archived_log fixed view gives a really
 * useful look at how much redo the database generated for a given time-span. It relies on the 
 * database being in archive log mode (otherwise the script will find no data to report on). From the
 * results you can see when a redo log switch occured, how long each redo log file was in use (and hence
 * the frequency of log switches), how much data was written to each redo log file, how long it took
 * to create the archive log file and (derived from this) how fast the archive process was able to write
 * the redo data to the archive log drive.
 *
 * As well as showing you how much redo the database is writing, the final column of data - the rate at which
 * redo data can be written to the archive logs - is also a pretty good indication of how fast the drive that
 * archive logs are written to is. With EMC symmetrix class SAN storage the rate can be up to 50 MB/sec. Note,
 * however, that the rate is impacted by other factors and not just the raw power of the underlying storage. The
 * rate has been seen to dramatically drop when the CPU utilisation of the server approaches 100% (which is 
 * to be expected as the archive process will be competing with the rest of the database application for CPU
 * cycles). Contention for the underlying components of the disk infrastructure will also have a major impact.
 * In one case a SAN with a throughput known to be capable of in excess of 30MB/s was observed to be reduced 
 * to a peak rate of 9Mb/s as a result of badly laid out disks leading to competition for SAN resources.
 *
 * The archive log filename displayed has the directory removed - and to cater for *nix or Windows directory structures
 * requires a U or W to be entered (so that the script knows which kind of "slash" to look for).
 *
 * The time span is specified in hours from the current time - the "from" parameter is the number of hours from now
 * to find the first redo log file whilst the "to" parameter is the the number of hours from now to find the last redo log
 * file to report on. So, to get the redo log files for the last day enter a "from" of 24 (24 hours back from now) and a
 * "to" of 0 (until now).
 * Acknowedgements go to my former colleague S. Atty for the intial inspiration towards this.
 *
 * Sample usage of this script might result in the following output:
 *
 *
 * SQL> start D:\TimOnionsCom\redorate.sql
 * *nix or Windows? (U/W):U
 * Enter value for hoursfrom: 148
 * Enter value for hoursto: 0
 *                                    Log End  ARC End  In use Arc   Size Mb/
 * ARC name       Log Start Time       Time     Time     (mins) (s)  (MB)  sec
 * -------------- -------------------- -------- -------- -----  --- ----- ---
 * RACDB1_113.arc 04-JAN-2007 03:50:56 08:00:08 08:00:09 1,689    1    43  39
 * RACDB2_896.arc 04-JAN-2007 22:01:39 01:54:07 01:54:09   232    2    43  36
 * RACDB2_895.arc 03-JAN-2007 19:00:15 22:01:39 22:01:41 1,621    2    43  36
 * RACDB2_560.arc 03-JAN-2007 09:34:17 08:55:06 08:55:08 1,400    2    43  36
 * RACDB1_118.arc 03-JAN-2007 08:00:07 03:50:56 03:50:57 1,190    1    43  39
 * RACDB2_894.arc 02-JAN-2007 09:05:38 19:00:15 19:00:16 2,034    1    43  39
 * RACDB2_559.arc 02-JAN-2007 22:01:35 09:34:17 09:34:19   692    2    43  36
 * RACDB1_181.arc 02-JAN-2007 09:05:36 08:00:07 08:00:08 1,374    1    43  39
 * RACDB2_558.arc 02-JAN-2007 09:05:31 22:01:35 22:01:36   776    1    43  39
 * RACDB1_180.arc 02-JAN-2007 09:05:30 09:05:36 09:05:37     0    1    43  39
 * RACDB1_179.arc 02-JAN-2007 09:05:23 09:05:30 09:05:34     0    4    43  30
 * RACDB1_178.arc 02-JAN-2007 09:05:16 09:05:23 09:05:25     0    2    43  36
 * RACDB2_892.arc 02-JAN-2007 05:16:09 09:05:16 09:05:20   229    4     9   6
 * RACDB1_177.arc 02-JAN-2007 09:05:07 09:05:16 09:05:17     0    1    43  39
 * RACDB2_556.arc 02-JAN-2007 05:16:08 09:05:10 09:05:11   229    1    34  30
 * RACDB1_176.arc 02-JAN-2007 05:48:24 09:05:07 09:05:09   196    2    43  36
 * RACDB1_175.arc 02-JAN-2007 05:16:09 05:48:24 05:49:12    32   48     0   0
 */
REM