Locate Log Switch Interval on Day/Hourly Basis

–List Timing for Log Switch on different clustered instances with thread details

SET LINESIZE 150
COLUMN TIMESTAMP FORMAT A30
COLUMN THREAD_ID FORMAT 999
COLUMN INSTANCE_ID FORMAT 999
SELECT  to_char(first_time,’DD-MON-YYYY HH24:MI’) TIMESTAMP,thread# THREAD_ID,inst_id INSTANCE_ID
FROM    GV$log_history
where INST_ID=&&INST_ID and trunc(first_time) =trunc(sysdate)
ORDER BY 1 desc;

– Will return a graph indicating log switches done on daily/hourly basis

SET LINESIZE 150
COLUMN DAY FORMAT A3
COLUMN Total FORMAT 99G990
COLUMN h00 FORMAT 999
COLUMN h01 FORMAT 999
COLUMN h02 FORMAT 999
COLUMN h03 FORMAT 999
COLUMN h04 FORMAT 999
COLUMN h05 FORMAT 999
COLUMN h06 FORMAT 999
COLUMN h07 FORMAT 999
COLUMN h08 FORMAT 999
COLUMN h09 FORMAT 999
COLUMN h10 FORMAT 999
COLUMN h11 FORMAT 999
COLUMN h12 FORMAT 999
COLUMN h13 FORMAT 999
COLUMN h14 FORMAT 999
COLUMN h15 FORMAT 999
COLUMN h16 FORMAT 999
COLUMN h17 FORMAT 999
COLUMN h18 FORMAT 999
COLUMN h19 FORMAT 999
COLUMN h20 FORMAT 999
COLUMN h21 FORMAT 999
COLUMN h22 FORMAT 999
COLUMN h23 FORMAT 999
COLUMN h24 FORMAT 999
BREAK ON REPORT
COMPUTE MAX OF “Total” ON REPORT
COMPUTE MAX OF “h00″ ON REPORT
COMPUTE MAX OF “h01″ ON REPORT
COMPUTE MAX OF “h02″ ON REPORT
COMPUTE MAX OF “h03″ ON REPORT
COMPUTE MAX OF “h04″ ON REPORT
COMPUTE MAX OF “h05″ ON REPORT
COMPUTE MAX OF “h06″ ON REPORT
COMPUTE MAX OF “h07″ ON REPORT
COMPUTE MAX OF “h08″ ON REPORT
COMPUTE MAX OF “h09″ ON REPORT
COMPUTE MAX OF “h10″ ON REPORT
COMPUTE MAX OF “h11″ ON REPORT
COMPUTE MAX OF “h12″ ON REPORT
COMPUTE MAX OF “h13″ ON REPORT
COMPUTE MAX OF “h14″ ON REPORT
COMPUTE MAX OF “h15″ ON REPORT
COMPUTE MAX OF “h16″ ON REPORT
COMPUTE MAX OF “h17″ ON REPORT
COMPUTE MAX OF “h18″ ON REPORT
COMPUTE MAX OF “h19″ ON REPORT
COMPUTE MAX OF “h20″ ON REPORT
COMPUTE MAX OF “h21″ ON REPORT
COMPUTE MAX OF “h22″ ON REPORT
COMPUTE MAX OF “h23″ ON REPORT

SELECT TRUNC(first_time) AS “Date”,
TO_CHAR(first_time, ‘Dy’) AS “Day”,
COUNT(1) AS “Total”,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’00′,1,0)) AS “h00″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’01′,1,0)) AS “h01″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’02′,1,0)) AS “h02″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’03′,1,0)) AS “h03″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’04′,1,0)) AS “h04″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’05′,1,0)) AS “h05″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’06′,1,0)) AS “h06″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’07′,1,0)) AS “h07″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’08′,1,0)) AS “h08″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’09′,1,0)) AS “h09″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’10′,1,0)) AS “h10″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’11′,1,0)) AS “h11″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’12′,1,0)) AS “h12″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’13′,1,0)) AS “h13″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’14′,1,0)) AS “h14″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’15′,1,0)) AS “h15″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’16′,1,0)) AS “h16″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’17′,1,0)) AS “h17″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’18′,1,0)) AS “h18″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’19′,1,0)) AS “h19″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’20′,1,0)) AS “h20″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’21′,1,0)) AS “h21″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’22′,1,0)) AS “h22″,
SUM(DECODE(TO_CHAR(first_time, ‘HH24′),’23′,1,0)) AS “h23″
FROM V$log_history
GROUP BY TRUNC(first_time), TO_CHAR(first_time, ‘Dy’)
ORDER BY 1;
CLEAR BREAKS;