Ask the Expert

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?

    Requires Free Membership to View

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 first published in December 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.