Writing 'a' spoolfile/report from SQL*Plus to the buffers of a filesystem is completely different from writing huge amounts of archived redo log files by the ARCH process to disk (or OS cache, or whatever).
The report is small enough to be kept in the buffers. A continous stream of archived redo log files is not, they will fill up the buffer. From that point onwards the OS then needs to write a block to disk at the one end of the buffer before it can receive a new block from the ARCH process. So, after the buffers have been filled, but the disk-writes keep coming, the only difference between buffered and direct I/O is the extra step for buffer-handling. At that moment the buffered I/O starts being slower, consuming more resources than direct I/O. Apart from that, as stated in this thread before, buffering archived log files doesn't help. It is not likely that you will be reading them soon, so occupying cache space with these files doesn't help anywhere.
A similar explanation is valid for the fact that having 16 online redo log files made the problem 'disappear'. That is only true, because the application obviously doesn't create a continous amount of redo for the database. During the interval that less redo gets generated, the ARCH process is able to catch up. When the load increases (because the business gets more succesful), or just goes flat at a higher level, the problem might pop up again in the future. You should monitor the amount of unarchived online redo logs carefully. When that gets close to 16 at peak levels, it's time to raise the amount of online redo log groups again, or tune the destination of your archives in a better way.
Best regards, Carel-Jan
On Thu, 2005-06-23 at 21:45, Radoulov, Dimitre wrote:
> Agreed, > but may be I'm missing something here: if the "writing" from sqlplus to the > spool file is so much slower, why the would the writing from online redo to > filesystem archive be faster? What's the difference between the two > writings? > > > Thanks, > Dimitre > > > -- -- Original Message -- -- > From: "Kevin Closson" <kevinc@(protected)> > To: <oracle-l@(protected)> > Sent: Thursday, June 23, 2005 9:29 PM > Subject: RE: Q: Filesystem choice for log_archive_dest > > > > >phone company with all those SMS services). So now I'm trying > >>to find the technical explanation of why one could need a > >>direct IO filesystem for the archived log files. > > > > life is an unending series of choices. If you want to > > preserve your memory, you don't want to spool through the > > buffered path. If you cannot configure enough logs and > > fast enough disk to meet the requirement in the direct path, > > you have to go buffered. No cut and dried answer, but those > > are the ingredients to consider. > > > >>So, we ran another test today with another query, the results: > >>spooling on the direct IO filesystem: 30min, on the local > >>filesystem: 49s. > > > > there should be no mystery that writing to memory is > > orders of magnitude faster than disk. > > > > > > > >> > >> > >> > >>Thanks > >>Dimitre > >> > >> > >>-- -- Original Message -- -- > >>From: "Kevin Closson" <kevinc@(protected)> > >>To: <oracle-l@(protected)> > >>Sent: Thursday, June 23, 2005 6:21 PM > >>Subject: RE: Q: Filesystem choice for log_archive_dest > >> > >> > >>> > >>> >So I was wondering, could the direct IO be beneficial for the > >>> log_archive_dest filesystem in some cases? > >>> > >>> yep...see my last post. This just needs tuning. how many > >>> online logs do you have and what size are they? > >>> > >>> -- > >>> http://www.freelists.org/webpage/oracle-l > >> > >> > > -- > > http://www.freelists.org/webpage/oracle-l > > -- > http://www.freelists.org/webpage/oracle-l
=== If you think education is expensive, try ignorance. (Derek Bok) ===
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8"> <META NAME="GENERATOR" CONTENT="GtkHTML/3.0.10"> </HEAD> <BODY> Dimitri,<BR> <BR> Writing 'a' spoolfile/report from SQL*Plus to the buffers of a filesystem is completely different from writing huge amounts of archived redo log files by the ARCH process to disk (or OS cache, or whatever).<BR> <BR> The report is small enough to be kept in the buffers. A continous stream of archived redo log files is not, they will fill up the buffer. From that point onwards the OS then needs to write a block to disk at the one end of the buffer before it can receive a new block from the ARCH process. So, after the buffers have been filled, but the disk-writes keep coming, the only difference between buffered and direct I/O is the extra step for buffer-handling. At that moment the buffered I/O starts being slower, consuming more resources than direct I/O. Apart from that, as stated in this thread before, buffering archived log files doesn't help. It is not likely that you will be reading them soon, so occupying cache space with these files doesn't help anywhere. <BR> <BR> A similar explanation is valid for the fact that having 16 online redo log files made the problem 'disappear'. That is only true, because the application obviously doesn't create a continous amount of redo for the database. During the interval that less redo gets generated, the ARCH process is able to catch up. When the load increases (because the business gets more succesful), or just goes flat at a higher level, the problem might pop up again in the future. You should monitor the amount of unarchived online redo logs carefully. When that gets close to 16 at peak levels, it's time to raise the amount of online redo log groups again, or tune the destination of your archives in a better way.<BR> <BR> Best regards, Carel-Jan <BR> <BR> <BR> On Thu, 2005-06-23 at 21:45, Radoulov, Dimitre wrote: <BLOCKQUOTE TYPE=CITE> <PRE><FONT COLOR="#737373"><I>Agreed, but may be I'm missing something here: if the "writing" from sqlplus to the spool file is so much slower, why the would the writing from online redo to filesystem archive be faster? What's the difference between the two writings?
Thanks, Dimitre
-- -- Original Message -- -- From: "Kevin Closson" <kevinc@(protected)> To: <oracle-l@(protected)> Sent: Thursday, June 23, 2005 9:29 PM Subject: RE: Q: Filesystem choice for log_archive_dest
> >phone company with all those SMS services). So now I'm trying >>to find the technical explanation of why one could need a >>direct IO filesystem for the archived log files. > > life is an unending series of choices. If you want to > preserve your memory, you don't want to spool through the > buffered path. If you cannot configure enough logs and > fast enough disk to meet the requirement in the direct path, > you have to go buffered. No cut and dried answer, but those > are the ingredients to consider. > >>So, we ran another test today with another query, the results: >>spooling on the direct IO filesystem: 30min, on the local >>filesystem: 49s. > > there should be no mystery that writing to memory is > orders of magnitude faster than disk. > > > >> >> >> >>Thanks >>Dimitre >> >> >>-- -- Original Message -- -- >>From: "Kevin Closson" <kevinc@(protected)> >>To: <oracle-l@(protected)> >>Sent: Thursday, June 23, 2005 6:21 PM >>Subject: RE: Q: Filesystem choice for log_archive_dest >> >> >>> >>> >So I was wondering, could the direct IO be beneficial for the >>> log_archive_dest filesystem in some cases? >>> >>> yep...see my last post. This just needs tuning. how many >>> online logs do you have and what size are they? >>> >>> -- >>> </FONT><A HREF="http://www.freelists.org/webpage/oracle-l"><U>http ://www.freelists.org/webpage/oracle-l</U></A> <FONT COLOR="#737373">>> >> > -- > </FONT><A HREF="http://www.freelists.org/webpage/oracle-l"><U>http://www .freelists.org/webpage/oracle-l</U></A> <FONT COLOR="#737373"> --</FONT> <A HREF="http://www.freelists.org/webpage/oracle-l"><U>http://www.freelists.org /webpage/oracle-l</U></I></A></PRE> </BLOCKQUOTE> <PRE><TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%"> <TR> <TD> ===<BR> If you think education is expensive, try ignorance. (Derek Bok)<BR> ===<BR> <BR> <FONT COLOR="#ff0000" SIZE="2">DBA</FONT><FONT SIZE="2">!ert, Independent Oracle consultancy <BR> Kastanjelaan 61C<BR> 2743 BX Waddinxveen<BR> The Netherlands<BR> tel. +31 (0) 182 64 04 28<BR> fax +31 (0) 182 64 04 29<BR> e-mail info.dbalert@(protected)</FONT> </TD> </TR> </TABLE> </PRE> </BODY> </HTML>
