Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: exp performance question ( direct=y)

RE: exp performance question ( direct=y)

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Fri, 22 Jun 2001 13:26:29 -0700
Message-ID: <F001.0033466D.20010622133242@fatcity.com>

I took
his comment the wrong as it was something people complained about when I was on LazyDBA.
I
don't think he meant anything negative by it.  
"Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence <FONT
face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS" size=2>Fuelspot

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Koivu, Lisa   [mailto:lisa.koivu_at_efairfield.com]Sent: Friday, June 22, 2001 4:33   PMTo: 'ORACLE-L_at_fatcity.com';
  'cspence_at_fuelspot.com'Subject: RE: exp performance question (   direct=y)
  Christopher, this is exactly why you
  should keep posting no matter what they whiners say. <FONT   face=Arial color=#0000ff size=2>Thanks for sending this to the list.  I   learned something today.
  Have a great weekend!
  Lisa Koivu <FONT
  face=Arial color=#0000ff size=2>Ft. Lauderdale, FL, USA   

    -----Original Message----- <FONT

    face=Arial size=1>From:   <FONT face=Arial 
    size=1>Christopher Spence [SMTP:cspence_at_FuelSpot.com] <FONT 
    face=Arial size=1>Sent:   <FONT face=Arial 
    size=1>Friday, June 22, 2001 4:45 PM <FONT face=Arial 
    size=1>To:     <FONT face=Arial 
    size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
    size=1>Subject:        <FONT 
    face=Arial size=1>RE: exp performance question ( direct=y) 
    1)  You can expect to see as much as 75%     performance gain on export.  There <FONT face=Arial     size=2>is just about no performance gain on the import.  The reason for     this is it avoids 3 copies and 2
    character conversions when exporting the data. This is <FONT     face=Arial size=2>due to the conversion from column major to row major when     bringing in the buffers into the buffer     cache, then converting back to column major to <FONT face=Arial     size=2>handle the select.  I would recomend looking at the recordlength     parameter as it has a good (1-4%) effect     on performance initially, but after 32k it <FONT face=Arial     size=2>generally has very little performance gain.  During conventional     exports, recordlength can prove as much     as 6% performance gain.  Ussually setting it <FONT     face=Arial size=2>to 32k is the best you will see.     One thing to know about exports is conventional     exports are cpu bound where as direct     exports are Io bound.  But either will only use a fractional     portion of the cpu/io.  Also note, if your     using consistent exports it will incur     locking as well as redo/rollback generation.  So time for     exports cannot be 100% accurately
    consistent.
    Another thing is direct exports use as much as     65% less memory than conventional and     about 35% less cpu.  Also direct exports tend to do a three     fold increase in io throughput over     conventional.  You can ussually export a <FONT face=Arial     size=2>max export sustained speed of around 5-6% of max i/o.     2)  Target, source, and client used to     export must be of the same character set     and you cannot export lobs.
    This is perhaps more than you asked for, but     hopefully it helps.
    "Walking on water and developing software from a     specification are easy if both are
    frozen."
    Christopher R. Spence <FONT face=Arial     size=2>Oracle DBA Fuelspot     

    -----Original Message----- <FONT
    face=Arial size=2>Sent: Friday, June 22, 2001 12:56 PM <FONT     face=Arial size=2>To: Multiple recipients of list ORACLE-L     Oracle : 8.0.5 <FONT face=Arial
    size=2>Platform : Sun
    Currently we have cron job every night (starting     from 11pm) to do export. I changed the     setting "direct" to "y" two days ago while leaving all other     parameters unchanged, hoping to gain some     performance. I am a bit surprused to find     that it did not. It actually took longer to create dump file with     less data to export. The whole exp
    process takes about 2 hours to finish. <FONT face=Arial     size=2>Yes, there could be lots of other unix processes running during that     time. But I would still expect to see     some improvement because we are doing this <FONT face=Arial     size=2>way for quite a while. So my questions are:

  1. From your "real" export experience, how much performance boost did you see when you set "direct=y"?
  2. If "direct=y" improves the performance, why would anyone want to use  <FONT face=Arial size=2>"direct=n"? Thanks. Guang
    • here is my orcle dump file's time stamp: (dmp.1 and dmp.2 are from direct=y, dmp.3, dmp.4 and dmp.5 are from direct=n). -rw-rw-r--   1 mt       prog     1042197132 Jun 18 01:05 oracle.dmp.5.gz <FONT face=Arial size=2>-rw-rw-r--   1 mt       prog     1042375633 Jun 19 01:04 oracle.dmp.4.gz -rw-rw-r--   1 mt       prog     1042556662 Jun 20 00:25 oracle.dmp.3.gz <FONT face=Arial size=2>-rw-rw-r--   1 mt       prog     1034773279 Jun 21 01:17 oracle.dmp.2.gz -rw-rw-r--   1 mt       prog     1035237986 Jun 22 01:22 oracle.dmp.1.gz --here is the parameter file: <FONT face=Arial size=2>BUFFER = 64000 COMPRESS
      • Y CONSISTENT = N <FONT face=Arial size=2>CONSTRAINTS = Y DIRECT
      • Y FILE = /oracle/exports/oracle.dmp.pipe #FULL = Y GRANTS = Y <FONT face=Arial size=2>INDEXES = Y LOG = /oracle/exports/export.log ROWS = Y USERID = xxx/yyy <FONT face=Arial size=2>OWNER = (aaa,bbb) <FONT face=Arial size=2>_________________________________________________________________ Get your FREE download of MSN Explorer at <A target=_blank href="http://explorer.msn.com">http://explorer.msn.com
    • Please see the official ORACLE-L FAQ: <A target=_blank href="http://www.orafaq.com">http://www.orafaq.com <FONT face=Arial size=2>-- Author: Guang Mei   INET: zlmei_at_hotmail.com

    Fat City Network Services    --
    (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial     size=2>San Diego, California        --     Public Internet access / Mailing Lists <FONT face=Arial

    size=2>-------------------------------------------------------------------- 
    To REMOVE yourself from this mailing list, send     an E-Mail message to:
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     the message BODY, include a line containing:     UNSUB ORACLE-L (or the name of mailing     list you want to be removed from).  You may <FONT face=Arial     size=2>also send the HELP command for other information (like     subscribing). -- <FONT
    face=Arial size=2>Please see the official ORACLE-L FAQ: <A target=_blank 
    href="http://www.orafaq.com">http://www.orafaq.com <FONT 
    face=Arial size=2>-- Author: Christopher 
    Spence   INET:
    cspence_at_FuelSpot.com
    Fat City Network Services    --
    (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial     size=2>San Diego, California        --     Public Internet access / Mailing Lists <FONT face=Arial
    size=2>-------------------------------------------------------------------- 
    To REMOVE yourself from this mailing list, send     an E-Mail message to:
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     the message BODY, include a line containing:     UNSUB ORACLE-L (or the name of mailing     list you want to be removed from).  You may <FONT face=Arial     size=2>also send the HELP command for other information (like     subscribing). Received on Fri Jun 22 2001 - 15:26:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US