Sequence cache and performance
Asked Answered
M

5

13

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.

Maddeu answered 19/6, 2014 at 4:35 Comment(4)
How many times per second do you request for the next sequence value? How do you know SEQ_MY_SEQU_EMP_ID.nextval is the bottleneck?Smutty
When checked, SQL ordered by Executions of AWR report, seems 78.2% of CPU usage.Maddeu
Okay, how many in absolute values is it?Smutty
How to get that? I can see the Sequence Cache waits= 5778Maddeu
C
10

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

Critta answered 19/6, 2014 at 7:38 Comment(0)
A
9

If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default. Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.

Using the CACHE and NOORDER options together results in the best performance for a sequence. CACHE option is used without the ORDER option, each instance caches a separate range of numbers and sequence numbers may be assigned out of order by the different instances. So more the value of CACHE less writes into dictionary but more sequence numbers might be lost. But there is no point in worrying about losing the numbers, since rollback, shutdown will definitely "lose" a number.

CACHE option causes each instance to cache its own range of numbers, thus reducing I/O to the Oracle Data Dictionary, and the NOORDER option eliminates message traffic over the interconnect to coordinate the sequential allocation of numbers across all instances of the database. NOCACHE will be SLOW...

Read this

Asterisk answered 19/6, 2014 at 11:5 Comment(0)
M
2

Have done some research and found some relevant information in this regard:

  • We need to check the database for sequences which are high-usage but defined with the default cache size of 20 - the performance benefits of altering the cache size of such a sequence can be noticeable.
  • Increasing the cache size of a sequence does not waste space, the cache is still defined by just two numbers, the last used and the high water mark; it is just that the high water mark is jumped by a much larger value every time it is reached.
  • A cached sequence will return values exactly the same as a non-cached one. However, a sequence cache is kept in the shared pool just as other cached information is. This means it can age out of the shared pool in the same way as a procedure if it is not accessed frequently enough. Everything is the cache is also lost when the instance is shut down.
Maddeu answered 19/6, 2014 at 8:53 Comment(0)
C
1

By default in ORACLE cache in sequence contains 20 values. We can redefine it by given cache clause in sequence definition. Giving cache caluse in sequence benefitted into that when we want generate big integers then it takes lesser time than normal, otherwise there are no such drastic performance increment by declaring cache clause in sequence definition.

Choosy answered 19/6, 2014 at 5:42 Comment(0)
G
1

Besides spending more time updating oracle data dictionary having small sequence caches can have other negative effects if you work with a Clustered Oracle installation.

In Oracle 10g RAC Grid, Services and Clustering 1st Edition by Murali Vallath it is stated that if you happen to have

  • an Oracle Cluster (RAC)
  • a non-partitioned index on a column populated with an increasing sequence value
  • concurrent multi instance inserts

you can incur in high contention on the rightmost index block and experience a lot of Cluster Waits (up to 90% of total insert time). If you increase the size of the relevant sequence cache you can reduce the impact of Cluster Waits on your index.

an excerpt from the mentioned book

Gascon answered 10/1, 2018 at 15:50 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.