Sequence cache and performance

oracle database-performance
being_uncertain picture being_uncertain · Jun 19, 2014 · Viewed 22.9k times · Source

I could see the DBA team advises to set the sequence cache to a higher value at the time of performance optimization. To increase the value from 20 to 1000 or 5000.The oracle docs, says the the cache value,

Specify how many values of the sequence the database preallocates and keeps in memory for faster access.

Somewhere in the AWR report I can see,

select SEQ_MY_SEQU_EMP_ID.nextval from dual

Can any performance improvement be seen if I increase the cache value of SEQ_MY_SEQU_EMP_ID.

My question is:

Is the sequence cache perform any significant role in performance? If so how to know what is the sufficient cache value required for a sequence.

Answer

VikiYang picture VikiYang · Jun 19, 2014

We can get the sequence values from oracle cache before them used out. When all of them were used, oracle will allocate a new batch of values and update oracle data dictionary. If you have 100000 records need to insert and set the cache size is 20, oracle will update data dictionary 5000 times, but only 20 times if you set 5000 as cache size.

More information maybe help you: http://support.esri.com/en/knowledgebase/techarticles/detail/20498

Related questions

How do NULL values affect performance in a database search?

In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or …

Read More
What does Active Sessions and User I/O mean in Oracle Enterprise Manager?

What does "Active Session" mean? I need to analyze database performance by using this graph taken from Oracle Enterprise Manager (OEM), but I don't understand what is "Active Session" and why there are 22 of them of User I/O? Could …

Read More
Why is Oracle ignoring index with ORDER BY?

My intention is to obtain a paginated resultset of customers. I am using this algorithm, from Tom: select * from ( select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN from CUSTOMER C ) where RN between 1 and 20 order …

Read More