RE: exp performance question ( direct=y)

[Mohan, Ross]

I'd set recordlength to 128 or 64K, depending on your OS....just a read stride... -----Original Message----- From: Jim Conboy [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 4:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: exp performance question ( direct=y) No, buffer is ignored for DIRECT=Y.  But RECORDLENGTH should be set to DB_BLOCK_SIZE, or a multiple of it.  I've had dramatic gains in performance doing this.   Also, pre-8i you couldn't do direct-mode export of LONG fields.  As of sometime in 8i you can (I know it works in 8.1.7).   Jim >>> [EMAIL PROTECTED] 06/22/01 04:00PM >>> I dunno. Does the BUFFER still matter when DIRECT=Y?   If so, I either remove it entirely, or multiply it by about a factor of five or so.   my $0.02 -----Original Message----- From: JOE TESTA [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 3:43 PM To: Multiple recipients of list ORACLE-L Subject: Re: exp performance question ( direct=y) Ron,   ok i'm now confused,   exp direct=n|y imp no such option sql_loader has direct also.   or am i missing something here?   joe   >>> [EMAIL PROTECTED] 06/22/01 03:00PM >>> I thought that DIRECT=Y was for imports only. It makes since because in the import you are placing the data directly into the blocks with out a redo log. I have no idea why there was a difference in the times unless it was the extra overhead for a command that was not used. ROR m���m >>> [EMAIL PROTECTED] 06/22/01 12:55PM >>> Oracle : 8.0.5 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. 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 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  "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 -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 -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: BUFFER = 64000 COMPRESS = Y CONSISTENT = N CONSTRAINTS = Y DIRECT = Y FILE = /oracle/exports/oracle.dmp.pipe #FULL = Y GRANTS = Y INDEXES = Y LOG = /oracle/exports/export.log ROWS = Y USERID = xxx/yyy OWNER = (aaa,bbb) _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guang Mei   INET: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers   INET: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).