17 | 08 | 2017
Latest Articles
Popular Articles

Scripts

Redo Log switch history per hour & per day (rollup & analytical function)

User Rating:  / 0
PoorBest 

Redo Log switch history per hour & per day .

 

Were there lot' s off log switches ( transactions ) yesterday ? the day before yesterday ? last week ?

 

Rollup function

 

select
trunc(first_time) DAY,
trunc(first_time,'HH24') DATE_HOUR,
count(*) LOG_SWITCHES
from v$log_history
group by rollup( trunc(first_time) , trunc(first_time,'HH24') )
order by 1,2;

 

Analytical function 

 

break on DAY

WITH row_source AS
( select
trunc(first_time) DAY,
trunc(first_time,'HH24') DAY_HOUR,
count(*) LOG_SWITCHES_PER_HOUR
from v$log_history
group by trunc(first_time) , trunc(first_time,'HH24') )
select DAY, DAY_HOUR, LOG_SWITCHES_PER_HOUR,
sum(LOG_SWITCHES_PER_HOUR) OVER (PARTITION BY DAY ORDER BY DAY_HOUR) "TOTAL_SWITCHES_SOFAR_TODAY"
from row_source
order by 1,2;

 

sample output -- rollup function

 

28/10/2011 00:00 28/10/2011 00:00                3
28/10/2011 00:00 28/10/2011 01:00                5
28/10/2011 00:00 28/10/2011 03:00                3
28/10/2011 00:00 28/10/2011 04:00                1
28/10/2011 00:00 28/10/2011 05:00                3
28/10/2011 00:00 28/10/2011 06:00                2
28/10/2011 00:00 28/10/2011 07:00                3
28/10/2011 00:00 28/10/2011 08:00                2
28/10/2011 00:00 28/10/2011 09:00                3
28/10/2011 00:00 28/10/2011 10:00                2
28/10/2011 00:00 28/10/2011 11:00                3
28/10/2011 00:00 28/10/2011 12:00                1
28/10/2011 00:00 28/10/2011 13:00                6
28/10/2011 00:00 28/10/2011 14:00                2
28/10/2011 00:00 28/10/2011 15:00                3
28/10/2011 00:00 28/10/2011 16:00                3
28/10/2011 00:00 28/10/2011 17:00                4
28/10/2011 00:00 28/10/2011 18:00                1
28/10/2011 00:00 28/10/2011 19:00                4
28/10/2011 00:00 28/10/2011 20:00                2
28/10/2011 00:00 28/10/2011 21:00                3
28/10/2011 00:00 28/10/2011 22:00                3
28/10/2011 00:00 28/10/2011 23:00                2
28/10/2011 00:00                                64
29/10/2011 00:00 29/10/2011 00:00               11
29/10/2011 00:00 29/10/2011 01:00               45
29/10/2011 00:00 29/10/2011 02:00               35
29/10/2011 00:00 29/10/2011 03:00               38
29/10/2011 00:00 29/10/2011 04:00               37
29/10/2011 00:00 29/10/2011 05:00               38
29/10/2011 00:00 29/10/2011 06:00               31
29/10/2011 00:00 29/10/2011 07:00                2
29/10/2011 00:00 29/10/2011 09:00               10
29/10/2011 00:00 29/10/2011 10:00                5
29/10/2011 00:00 29/10/2011 11:00                4
29/10/2011 00:00 29/10/2011 13:00                2
29/10/2011 00:00 29/10/2011 15:00                2
29/10/2011 00:00 29/10/2011 17:00                2
29/10/2011 00:00 29/10/2011 19:00                2
29/10/2011 00:00 29/10/2011 21:00                2
29/10/2011 00:00 29/10/2011 23:00                3
29/10/2011 00:00                               269
30/10/2011 00:00 30/10/2011 01:00                7
30/10/2011 00:00 30/10/2011 02:00                2
30/10/2011 00:00 30/10/2011 04:00                2
30/10/2011 00:00 30/10/2011 05:00                2
30/10/2011 00:00 30/10/2011 06:00                2
30/10/2011 00:00 30/10/2011 08:00                2
30/10/2011 00:00 30/10/2011 10:00                2
30/10/2011 00:00 30/10/2011 12:00                2
30/10/2011 00:00 30/10/2011 14:00                3
30/10/2011 00:00 30/10/2011 16:00                2
30/10/2011 00:00 30/10/2011 18:00                2
30/10/2011 00:00 30/10/2011 20:00                2
30/10/2011 00:00 30/10/2011 22:00                2
30/10/2011 00:00                                32
31/10/2011 00:00 31/10/2011 00:00                2
31/10/2011 00:00 31/10/2011 01:00                2
31/10/2011 00:00 31/10/2011 02:00                4
31/10/2011 00:00 31/10/2011 03:00                1
31/10/2011 00:00 31/10/2011 04:00                3
31/10/2011 00:00 31/10/2011 05:00                2
31/10/2011 00:00 31/10/2011 06:00                3
31/10/2011 00:00 31/10/2011 07:00                1
31/10/2011 00:00 31/10/2011 08:00                4
31/10/2011 00:00 31/10/2011 09:00                2
31/10/2011 00:00 31/10/2011 10:00                4
31/10/2011 00:00 31/10/2011 11:00                1
31/10/2011 00:00 31/10/2011 12:00                3
31/10/2011 00:00 31/10/2011 13:00                1
31/10/2011 00:00 31/10/2011 14:00                4
31/10/2011 00:00 31/10/2011 15:00                2
31/10/2011 00:00 31/10/2011 16:00                2
31/10/2011 00:00 31/10/2011 17:00                1
31/10/2011 00:00                                42


-- sample output analytical function


 

DAY              DAY_HOUR             LOG_SWITCHES   TOTAL_SWITCHES
---------------- ---------------- ---------------- ----------------
11/01/2013 00:00 11/01/2013 14:00                1                1
17/01/2013 00:00 17/01/2013 12:00                1                1
22/01/2013 00:00 22/01/2013 09:00                1                1
                 22/01/2013 11:00               60               61
                 22/01/2013 12:00               71              132
                 22/01/2013 13:00              100              232
                 22/01/2013 14:00               12              244
23/01/2013 00:00 23/01/2013 16:00               36               36
                 23/01/2013 17:00               31               67
24/01/2013 00:00 24/01/2013 14:00               86               86
25/01/2013 00:00 25/01/2013 14:00                2                2
28/01/2013 00:00 28/01/2013 13:00                1                1
31/01/2013 00:00 31/01/2013 14:00                1                1
01/02/2013 00:00 01/02/2013 16:00                1                1
13/02/2013 00:00 13/02/2013 14:00                2                2
15/02/2013 00:00 15/02/2013 09:00                1                1
                 15/02/2013 10:00                1                2
08/03/2013 00:00 08/03/2013 11:00                1                1
                 08/03/2013 12:00                1                2
                 08/03/2013 14:00                5                7

See also Replace and mirror redo logs