From Jim.Conboy@trw.com Fri, 22 Jun 2001 12:18:30 -0700 From: "Jim Conboy" Date: Fri, 22 Jun 2001 12:18:30 -0700 Subject: RE: exp performance question ( direct=y) Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 >>> MohanR@STARS-SMI.com 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:JTESTA@longaberger.com]Sent: Friday, June 22, 2001 3:43 PMTo: Multiple recipients of list ORACLE-LSubject: 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   >>> RROGERS@galottery.org 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>>> zlmei@hotmail.com 06/22/01 12:55PM >>>Oracle : 8.0.5Platform : SunCurrently 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 = 64000COMPRESS = YCONSISTENT = NCONSTRAINTS = YDIRECT = YFILE = /oracle/exports/oracle.dmp.pipe#FULL = YGRANTS = YINDEXES = YLOG = /oracle/exports/export.logROWS = YUSERID = xxx/yyyOWNER = (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: zlmei@hotmail.com Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Ron Rogers  INET: RROGERS@galottery.orgFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).