Tuesday, July 24, 2012

RAC: Log switch frequency script

The following script reports how much time passed between log switches in a time period.
Although it's written to provide information per instance in RACs, it will also work in Single Instances.
SELECT C.INSTANCE,
         C.THREAD#,
         B.SEQUENCE# "START SEQUENCE",
         TO_CHAR (B.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "START TIME",
         A.SEQUENCE# "END SEQUENCE",
         TO_CHAR (A.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "END TIME",
         TO_CHAR (
            TRUNC (SYSDATE)
            + NUMTODSINTERVAL ( (A.FIRST_TIME - B.FIRST_TIME) * 86400,
                               'SECOND'),
            'HH24:MI:SS')
            DURATION
    FROM V$LOG_HISTORY A, V$LOG_HISTORY B, V$THREAD C
   WHERE     A.SEQUENCE# = B.SEQUENCE# + 1
         AND A.THREAD# = C.THREAD#
         AND B.THREAD# = C.THREAD#
         AND A.FIRST_TIME BETWEEN TO_DATE ('23-07-2012 00:00:00',
                                           'DD-MM-YYYY HH24:MI:SS')
                              AND TO_DATE ('24-07-2012 00:00:00',
                                           'DD-MM-YYYY HH24:MI:SS')
ORDER BY 4;

Let's try it for a day, e.g. 23/07/12:   
INSTANCETHREAD#START SEQUENCESTART TIMEEND SEQUENCEEND TIMEDURATION
OTE1143657122-07-12 23:59:4443657223-07-12 00:08:1200:08:27
OTE2240295122-07-12 23:59:4640295223-07-12 00:15:3100:15:45
OTE1143657223-07-12 00:08:1243657323-07-12 00:11:4800:03:36
.....................
OTE2240331523-07-12 23:57:5140331623-07-12 23:59:4200:01:51
OTE1143687323-07-12 23:58:1543687423-07-12 23:59:1000:00:54
OTE1143687423-07-12 23:59:1043687523-07-12 23:59:4000:00:29

No comments:

Post a Comment