| |
 |
|
Oracle
Metric
log file switch completion
Oracle Tips by Burleson Consulting
|
The log file switch
completion
Oracle metric occurs when it is
waiting for a log switch to
complete.
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 redo log switching 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
See code
depot for full script
v$log_history
group by substr(time,1,5) ;
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
There are many ways to display the log switching frequency using
v$log_history. This script display log switches in a
linear format and joins the v$log_history table against
itself to count log switches over time:
select
b.recid,
to_char(b.first_time,’dd-mon-yy hh24:mi:ss’) start_time,
a.recid,
to_char(a.first_time,’dd-mon-yy hh24:mi:ss’) end_time,
round(((a.first_time-b.first_time)*25)*60,2)
minutes
from
See code depot
for full script
v$log_history
a,
v$log_history b
where
a.recid = b.recid+1
and
a.first_time between
to_date(’2010-04-14:00:00:00′,’yyyy-mm-dd:hh24:mi:ss’)
and
to_date(’2010-04-15:00:00:00′,’yyyy-mm-dd:hh24:mi:ss’)
order by
a.first_time asc;
This script will show the hourly count of log
switches:
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
See code depot for full script
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;
For a full set of working log switch monitoring scripts, I
recommend my
Oracle script download.
You can also see redo log switching
in Ion for Oracle:
The popular
Ion tool is
the easiest way to analyze Oracle log file switch behavior
over time (see above plot of log file parallel writes), and Ion
allows you to spot hidden redo log performance trends.
Ion is
our favorite Oracle tuning tool, and the only 3rd party
tool that we use.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
