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: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> 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: [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).
