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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: