Re: Q: Filesystem choice for log_archive_dest

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 24 Jun 2005 11:01:55 +0200

Ran 3 tests today:

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):

=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=49 lid=0 tim=47809245240406 hv=1193829252 ad='bc0a8dfc'
alter system archive log current
END OF STMT
PARSE #1:c=0,e=840,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=47809245240384
WAIT #1: nam='control file sequential read' ela= 624 p1=0 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 514 p1=1 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 471 p1=2 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 583 p1=0 p2=2043 p3=1
WAIT #1: nam='control file sequential read' ela= 549 p1=0 p2=9 p3=1
WAIT #1: nam='control file sequential read' ela= 520 p1=0 p2=7 p3=1
WAIT #1: nam='control file sequential read' ela= 485 p1=0 p2=9 p3=1
WAIT #1: nam='control file sequential read' ela= 509 p1=0 p2=7 p3=1
WAIT #1: nam='control file parallel write' ela= 3392 p1=3 p2=3 p3=3
WAIT #1: nam='control file parallel write' ela= 1358 p1=3 p2=3 p3=3
WAIT #1: nam='switch logfile command' ela= 57150 p1=0 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 93270 p1=1128660998 p2=0 p3=0
WAIT #1: nam='control file sequential read' ela= 499 p1=0 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 715 p1=1 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 568 p1=2 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 750 p1=0 p2=2044 p3=1
WAIT #1: nam='control file sequential read' ela= 523 p1=0 p2=9 p3=1
WAIT #1: nam='enqueue' ela= 2931705 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='enqueue' ela= 2935854 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='enqueue' ela= 2938877 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='enqueue' ela= 2930296 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='enqueue' ela= 2932823 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='enqueue' ela= 2937294 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='enqueue' ela= 2394548 p1=1464598532 p2=1 p3=191285
WAIT #1: nam='control file sequential read' ela= 498 p1=0 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 478 p1=1 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 439 p1=2 p2=1 p3=1
WAIT #1: nam='control file sequential read' ela= 541 p1=0 p2=2044 p3=1
WAIT #1: nam='control file sequential read' ela= 473 p1=0 p2=10 p3=1
EXEC #1:c=0,e=22114542,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=47809267431905
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 7548196 p1=1650815232 p2=1 p3=0
=====================




Regards,
Dimitre


----- Original Message ----- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
To: <kevinc@xxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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?

--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2005