Log switch
Question: I want to size my archived redo logs and I understand
that there are scripts that will show the redo log switch frequency. How do I monitor the switch frequency, and
track log switching activity?
Answer: In a production environment it is important
to size your online redo log files to ensure that the frequency of redo log switches is not more than 5 log switches per hour
during peak DML activity.
Oracle
provides the v$log_history view to
allow us to track the historical frequency of log switches, and this can be
correlated to periods of high update activity when log switching is peaking.
Here
are some sample scripts to display the log switch frequency:
SELECT
to_char(first_time,'YYYY-MON-DD')
day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99')
"00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99')
"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99')
"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99')
"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99')
"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99')
"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99')
"06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99')
"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99')
"0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99')
"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99')
"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99')
"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99')
"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99')
"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99')
"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99')
"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99')
"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99')
"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99')
"18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99')
"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99')
"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99')
"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99')
"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99')
"23"
from
see
code depot for full script
v$log_history
GROUP
by
to_char(first_time,'YYYY-MON-DD');
This
log switch script is handy because it displays the log switch activity as a
two-dimensional table, showing log switches by hours of the day and log
switches by date:
LOG SWITCH
FREQUENCY REPORT
DAY 00
01 02 03
04 05 06
07 08 09
10 11 12
13 14 15
16 17 18
-----
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --
01/04 0
0 0 0
0 0 0
1 2 0
0 0 5
11 1 0
0 1 0
01/05 0
0 0 15
0 0 0
0 4 1
6 5 5
32 0 1
0 3 3
01/06 1
0 0 0
0 0 0
0 1 4
6 1 3
9 10 5
0 1 1
01/07 1
0 0 0
0 0 0
0 2 1
2 0 7
14 0 1
1 0 0
01/08 1
0 0 0
0 0 0
0 0 0
0 0 0
21 0 0
0 0 0
01/09 1
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
01/10 0
0 0 0
0 0 0
0 0 1
9 4 1
60 1 2
0 0 0
01/11 1
0 0 14
0 0 0
0 2 1
1 1 1
1 0 0
1 0 0
12/12 0
0 0 0
2 0 0
2 1 0
35 1 1 37
31 10 17 0 1
What is a log
switch?
A log switch
occurs when LGWR stops writing to one
redo log group and starts writing to another. By default, a log switch
occurs automatically when the current
redo log file group fills.
You can force a
log switch to make the currently active
group inactive and available for redo log maintenance operations. For example, you
want to drop the currently active group, but are not able to do so until the
group is inactive. You may also wish to force a log switch if the currently
active group needs to be archived at a specific time before the members of the
group are completely filled. This option is useful in configurations with large
redo log files that take a long time to fill.
RAC: If you are running RAC, the ALTER SYSTEM ARCHIVE LOG CURRENT will switch the logs on all RAC nodes
(instances), whereas ALTER SYSTEM SWITCH
LOGFILE will only switch he logfile on the instance where you issue the
switch command. Hence, ALTER SYSTEM
ARCHIVE LOG CURRENT is a best practice for RAC systems.
Comments
Post a Comment