 |
 |
||
 |
 |
 |
|
 |
 |
 |
|||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
 |
|||||
 |
|
 |
|||
 |
Donald K. Burleson Oracle Tips |
Click Here for online Oracle script downloads
A
script to check the frequency of log switches
In
a busy production environment, it is important to ensure that the frequency
of redo log switches is not more than 5 per hour.
As Oracle offloads redo log images from the redo log filesystem onto
the archived redo log filesystem, excessive I/O can occur and the archived
redo log must be promptly written to tape to keep the free space in the
filesystem. If the archived
redo log filesystem becomes full, the database grinds to a halt.
Not
many DBA’s are aware of the v$log_history view, and how it can be used to
plot the frequency of online redo log switches.
The following script can be run to provide a complete log history:
set
lines 120;
set
pages 999;
select
substr(time,1,5) day,
to_char(sum(decode(substr(time,10,2),'00',1,0)),'99')
"00",
to_char(sum(decode(substr(time,10,2),'01',1,0)),'99')
"01",
to_char(sum(decode(substr(time,10,2),'02',1,0)),'99')
"02",
to_char(sum(decode(substr(time,10,2),'03',1,0)),'99')
"03",
to_char(sum(decode(substr(time,10,2),'04',1,0)),'99')
"04",
to_char(sum(decode(substr(time,10,2),'05',1,0)),'99')
"05",
to_char(sum(decode(substr(time,10,2),'06',1,0)),'99')
"06",
to_char(sum(decode(substr(time,10,2),'07',1,0)),'99')
"07",
to_char(sum(decode(substr(time,10,2),'08',1,0)),'99')
"08",
to_char(sum(decode(substr(time,10,2),'09',1,0)),'99')
"09",
to_char(sum(decode(substr(time,10,2),'10',1,0)),'99')
"10",
to_char(sum(decode(substr(time,10,2),'11',1,0)),'99')
"11",
to_char(sum(decode(substr(time,10,2),'12',1,0)),'99')
"12",
to_char(sum(decode(substr(time,10,2),'13',1,0)),'99')
"13",
to_char(sum(decode(substr(time,10,2),'14',1,0)),'99')
"14",
to_char(sum(decode(substr(time,10,2),'15',1,0)),'99')
"15",
to_char(sum(decode(substr(time,10,2),'16',1,0)),'99')
"16",
to_char(sum(decode(substr(time,10,2),'17',1,0)),'99')
"17",
to_char(sum(decode(substr(time,10,2),'18',1,0)),'99')
"18",
to_char(sum(decode(substr(time,10,2),'19',1,0)),'99')
"19",
to_char(sum(decode(substr(time,10,2),'20',1,0)),'99')
"20",
to_char(sum(decode(substr(time,10,2),'21',1,0)),'99')
"21",
to_char(sum(decode(substr(time,10,2),'22',1,0)),'99')
"22",
to_char(sum(decode(substr(time,10,2),'23',1,0)),'99')
"23"
from
v$log_history
group
by substr(time,1,5)
;
Here is the listing from this script. In addition to showing the frequency of activity, this script is great for showing the relative activity of your database.
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
Here we see a complete hourly breakdown of
log switches.
In this output we see that there is a large amount of update activity
each day at 1:00 PM.
col day format a15;
col hour format a4;
col total format 999;
select
to_char(first_time,’yyyy-mm-dd’) day,
to_char(first_time,’hh24′) hour,
count(*)
total
from
v$log_history
group by
to_char(first_time,’yyyy-mm-dd’),to_char(first_time,’hh24′)
order by
to_char(first_time,’yyyy-mm-dd’),to_char(first_time,’hh24′)
asc;
As we know, we might want to increase the
size of the online redo logs if the log switches more than 5 times each
hour.
If
you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only
$41.95(I don’t think it is right to charge a fortune for books!) and you
can buy it right now at this link:
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
|
 |
E-mail us for BC Oracle support: 
Copyright © 1996 - 2009
by Burleson Enterprises, Inc. All rights reserved. Oracle® is the registered trademark of Oracle Corporation.
|
|