Oracle-L: Re: export with "first rows"

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Thu, 15 Aug 2002 14:53:36 -0800
Message-ID: <F001.004B7057.20020815145336@fatcity.com>

ALL_ROWS and FIRST_ROWS have serious problems when querying the data dictionary in all versions of Oracle from v7.x to v8.1.x (see previous posting on 10-Aug, subject "Re: OPTIMIZER_MODE recommendation for 9.2"), documented in bug #564434. The situation is finally fixed in 9iR2, and possibly in 9iR1. You can see some related discussions by searching on keyword "ALL_ROWS" in MetaLink...

The EXP and IMP utilties need to query the data dictionary in order to generate all of the SQL statements they use to do their job. Having FIRST_ROWS (or ALL_ROWS) set for OPTIMIZER_MODE causes those queries on your data dictionary in your export to run hundreds (perhaps thousands) of times longer than normal. The actual SELECTs to export the data probably don't change (much) during the export...

Set OPTIMIZER_MODE to CHOOSE.

Original Message ----- From: Ed Lewis To: Multiple recipients of list ORACLE-L Sent: Thursday, August 15, 2002 3:13 PM Subject: export with "first rows"

Hello,

We have the optimizer mode set to "first rows" on our (25gb) database to meet a vendor requirement. When I do an "direct" export, it runs for 6-7 hours.

When I change the optimizer mode to "choose" it runs in around an hour.
It also runs in an hour when I set the the optimizer mode to "first rows", and run stats on the "sys" schema.

I'm curious why this occurs. Has anyone experienced this ? Any ideas why this happens ? Is this normal behavior ? I could not find anything on metalink. thanks.

Oracle 8.1.7.2, aix 4.3.3.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tim Gorman
INET: Tim_at_SageLogix.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). Received on Thu Aug 15 2002 - 17:53:36 CDT