Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000? That is to say does modifying this value to something around 1,000,000 give me any benefit during a transaction where > 200 million sequences will be used?

share|improve this question
1  
If you want to tuen an existing load, you should run a AWR and/or ASH report to find out where you really spend the time. –  a_horse_with_no_name Sep 26 '12 at 14:50
add comment

1 Answer

up vote 6 down vote accepted

The larger the CACHE on the sequence, the fewer times that you'll need to wait for Oracle to acquire the latch to be able to update the data dictionary when a new set of values need to be generated. There is definitely at least the potential for some performance benefit by increasing the size of the cache. On the other hand, if the CACHE is already at 10,000, you're only going to need to update the data dictionary 20,000 times in the course of a 200 million row data load (assuming one call to the sequence.nextval per row). Without seeing your system, it seems unlikely that these 20,000 updates would account for a meaningful fraction of the time you're spending in a 200 million row data load. You would potentially shave a couple of seconds off the load by increasing the size of the cache but that probably isn't meaningful given the amount of time you're likely spending on I/O.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.