Just trying to estimate the speed of writing from Oracle instance to different filesystems.
1. select * from table with 108127 records, spooling on local filesystem (observe the elapsed time/bytes received via SQL*Net from client):
108127 rows selected.
Elapsed: 00:00:30.85
Statistics -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- 0 recursive calls 0 db block gets 7622 consistent gets 0 physical reads 0 redo size 2864593 bytes sent via SQL*Net to client 79791 bytes received via SQL*Net from client 7210 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 108127 rows processed
2. select * from the same table with 108127 records, spooling on the OFA's filesystem with direct IO (observe the elapsed time/bytes received via SQL*Net from client):
I stopped the query with Ctrl+C after 6 min ...
32245 rows selected.
Elapsed: 00:06:48.80
Statistics -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- 0 recursive calls 0 db block gets 2277 consistent gets 0 physical reads 0 redo size 847415 bytes sent via SQL*Net to client 24142 bytes received via SQL*Net from client 2152 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 32245 rows processed
So, 79791 bytes received via SQL*Net from client(confirmed writings) for 00:30.85min vs 24142(confirmed writings) for 06:48.80min. If this is "normal" because of the memory vs direct disk write speed difference, how the online redo copy to the same direct IO disk could be beneficial? Or better, what is the advantage of having the log archive destination on direct IO filesystem if it's not for the speed?
This is the trace of the "archive log current" command (observe the enqueue and SQL*Net message from client ela values):
-- -- Original Message -- -- From: "Radoulov, Dimitre" <cichomitiko@(protected)> To: <kevinc@(protected)>; <oracle-l@(protected)> Sent: Thursday, June 23, 2005 9:45 PM Subject: Re: Q: Filesystem choice for log_archive_dest
> 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 >
