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 @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