Re: export with "first rows"

[Tim Gorman]

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.               ed