| |
 |
|
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 v$log_history
group by substr(time,1,5) ;
You can also see redo log switching
in Ion:
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.
This, and many other Oracle performance metrics are discussed in
my book "Oracle
Tuning" by Rampant TechPress. You can buy it directly from
the publisher and save 30% at this link:
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
