Q

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

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close