Rem Filename   : loghistogram.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : V9.x
Rem
Rem Modification history
Rem --------------------
Rem 04-Oct-2005    V1.0  - Based on script by Karel De Gang.
Rem                      - Added report header.
Rem                      - Some minor formatting changes.
Rem                      - Reverted sorting order.
Rem                      - Removed ALTER SESSION set dateformat
Rem 
Rem ===========================================================================================================================================================
Rem
Rem Description : Lists a histogram of the available redo log information
Rem
Rem               Date       : The date (including weekday) of the histogram.
Rem               24u total  : The total number of log switches for the date displayed.
Rem               00 ... 24  : The number of logswithes grouped per hour.
Rem 
Rem Sample output
Rem -------------
Rem MXXXXXXX<beantem5:RDB> @loghistogram
Rem 
Rem --------------------------------------------------------------------------------------------------------------------
Rem                                                   Redo log histogram
Rem 
Rem                                          of database PDB1 on server deeisep1
Rem --------------------------------------------------------------------------------------------------------------------
Rem Current date and time : Tuesday   04-OCT-2005 13:58:25
Rem --------------------------------------------------------------------------------------------------------------------
Rem 
Rem                 24h
Rem Date          Total   00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
Rem ------------- ----- ------------------------------------------------------------------------------------------------
Rem 12-Sep-05 Mon     3                                                                                    1   1       1
Rem 13-Sep-05 Tue    31    1       1   2   1   1       1   1   1   1   1   6   5   1   1   1   1   1   1   1   1   1
Rem 14-Sep-05 Wed    30    1   1   1   2   1       1   1   7   1   1   1   1   1   1   1       2       2   1   1   1   1
Rem 15-Sep-05 Thu    37    1   1       3   1   1       1   1   1   1   1   1   1   1  14   2   1   1   1   1   1       1
Rem 16-Sep-05 Fri    32    1   1   1   2   1       1   2  11   1   1   1   1   1   1       1   1   1   1   1   1
Rem 17-Sep-05 Sat     9                1           1   1       1   1   1   1       1                   1
Rem 18-Sep-05 Sun    97                            1                  53  39   1           1                       1   1
Rem 19-Sep-05 Mon    33    1   1   1   3   1       1   1   1   1   1  11       1   1   1   1   1   1   1   1   1       1
Rem 20-Sep-05 Tue    53    1   1   1   2   1   1       1   1   1   1   1   1  31   1   1   1   1       2   1   1       1
Rem 21-Sep-05 Wed    31    1   1   1   2   1   1   1       1   1   9   2   1   1   1   1       1   1   1   1   1   1
Rem 22-Sep-05 Thu    31    1   1   1   2   1   1       1   1  10   1   1   1   1   1   1       2       1   1   1   1
Rem 23-Sep-05 Fri    30    1   1   1   2   1       1   1   1   1   1   1  10   1   1   1       1   1   1   1   1
Rem 24-Sep-05 Sat    32                1                           3   1              25   1           1
Rem 25-Sep-05 Sun    13                                1           3   3       3                               1   1   1
Rem 26-Sep-05 Mon    50        1   1   3   1   1       1  12   1   1   1   1  12   2   2   1   1   1   1   1   1   3   1
Rem 27-Sep-05 Tue    48    1   1       2   1       1   1  10   1   1   1   2   6   7   6   1   1   1   1   1   1   1
Rem 28-Sep-05 Wed    47    6   1   1   2   1       1   1   4   8   1   1   1   1      10   1   1   1   1   1   1   1   1
Rem 29-Sep-05 Thu    25        1   1   2   1       1   1   1   1   5   2   1   1   1   1       1   1   1   1       1
Rem 30-Sep-05 Fri    11                2                       1   6   1                               1
Rem 01-Oct-05 Sat     5                1                   1   1       1                               1
Rem 02-Oct-05 Sun     2                                    1                                                   1
Rem 03-Oct-05 Mon     5                2                               1                               1           1
Rem 04-Oct-05 Tue    25    1   1   1   2   1       1   1   1   6   8   1   1
Rem
Rem ===========================================================================================================================================================

SET PAGESIZE  9999
SET TRIMSPOOL ON
SET VERIFY    OFF
SET FEEDBACK  OFF
SET LINESIZE 3500

@@header_title 116 DC 'Redo log histogram'

COLUMN day  format a13 HEADING "Date"
COLUMN dayt format a5 HEADING "  24h|Total"
COLUMN tot  HEADING "  00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23"

SELECT substr(TO_CHAR(FIRST_TIME,'DD-Mon-YY Day'),1,13)    day
     , TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'00',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'01',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'02',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'03',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'04',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'05',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'06',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'07',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'08',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'09',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'10',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'11',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'12',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'13',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'14',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'15',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'16',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'17',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'18',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'19',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'20',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'21',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'22',1,0))
             + SUM(DECODE(to_char(first_time,'HH24'),'23',1,0)),'9999') dayt
     , TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'00',1,0)),'B999') 
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'01',1,0)),'B999') 
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'02',1,0)),'B999') 
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'03',1,0)),'B999') 
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'04',1,0)),'B999') 
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'05',1,0)),'B999') 
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'06',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'07',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'08',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'09',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'10',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'11',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'12',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'13',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'14',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'15',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'16',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'17',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'18',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'19',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'20',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'21',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'22',1,0)),'B999')
    || TO_CHAR(SUM(DECODE(to_char(first_time,'HH24'),'23',1,0)),'B999') tot
  FROM V$LOG_HISTORY
 GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM_DD'), substr(TO_CHAR(FIRST_TIME,'DD-Mon-YY Day'),1,13)
 ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM_DD') ASC;

SET FEEDBACK ON

PROMPT

