Scripts
Redo Log switch history per hour & per day (rollup & analytical function)
User Rating: / 2
- Details
-
Parent Category: Articles
-
Created on Monday, 31 October 2011 17:35
-
Last Updated on Monday, 25 March 2013 10:43
-
Published on Monday, 31 October 2011 17:35
-
Hits: 14013
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