Manage Learn to apply best practices and optimize your operations.

Slow performance when exporting referential integrity constraints

We have a database of 300 MB on P-4 Intel machine with 1 GB RAM. Previously, every evening when I used to take export it would finish in 15-20 mins. But for the last couple of months, it exports the users tables at the same speed it used to do, but it hangs -- it takes a lot of time to export referenitial integrity constraints when all the user tables are exported and it exports views, dimensions, etc. My user tables are exported within 15-20 mins but exporting referential integrity constraints takes around one hour. Can you help?
You didn't mention what kind of export you are doing. Are you doing a user by user export or is this a full export? Are there partitioned tables involved? Are you doing the export while users are on the system? Have you tried exporting without constraints and then doing a separate export of just the constraints (i.e. no table data, etc)? Also check your OPTIMIZER_MODE paramter. At one time there was a MetaLink note about slowness on exports if OPTIMIZER_MODE = FIRST_ROWS. Another thing to check is to make sure your statistics are current. Having "bad" statistics may also be a problem.
This was last published in December 2003

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.