RE: exp performance question ( direct=y)

Here are some numbers from my testing of various options for exp and imp... Table had 4.9 Mil rows. Oracle 8.1.7 on AIX 4.3.3. Time is average from 3 attempts.
Exporting the table:
Conventional (without any options) 412.81sec   recordlength=16384 399.65sec recordlength is set to the value of DB_BLOCK_SIZE
  buffer=1048576 251.79sec   buffer=1048576 recordlength=16384 248.75sec recordlength is set to the value of DB_BLOCK_SIZE
  buffer=1048576 recordlength=65535 235.38sec recordlength is set to the maximum value for the platform
  direct=y 66.20sec
  direct=y recordlength=16384 51.85sec recordlength is set to the value of DB_BLOCK_SIZE
  direct=y recordlength=65535 45.60sec recordlength is set to the maximum value for the platform
Importing the same table (Table had no PKEY constraints).   commit=y indexes=n ignore=y 594.88sec   commit=y indexes=n ignore=y buffer=1048576 312.96sec Approximately 16,131 rows in the insert array
  commit=y indexes=n ignore=y buffer=5242880 289.55sec Approximately 80,860 rows in the insert array
  commit=y indexes=n ignore=y buffer=10485760 288.12sec Approximately 161,320 rows in the insert array

Hope this helps...

Regards,

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Yosi_at_comhill.com [SMTP:Yosi_at_comhill.com]
> Sent: Friday, June 22, 2001 4:19 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: exp performance question ( direct=y)
>
> I believe so. It may have a slightly different meaning. Rows, commit,
> these things act a little differently. (Is that not specific enough?) And
> direct is an exp param, that greatly affects your imp.
>
> Statistics is another exp parameter that takes affect on import. The
> export puts a stats statement in the dump file, which imp finds and
> executes. I imagine direct works the same way.
>
> A quick test on a tiny table shows that conventional exp creates a
> dump file that's slightly bigger. Visually, in a text editor, both files
> look very much alike.
>
> And the timing difference - for the export - was BIG. The conventional
> exported in 12 seconds, the direct was INSTANT, less than a second.
>
> I've experienced the same on large tables, and I probably even have
> timings saved somewhere.
>
> HTH,
>
> Yosi
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

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: 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
also send the HELP command for other information (like subscribing).