Performance Impact of Using Cache and Nocache in Sequences

When Caching is used the data dictionary is updated once till the final cached value is used. If you cache 1000 values then the data dictionary is updated once for every 1000 sequence numbers are used.

When Nocaching is used then the data dictionary is updated for every sequence number. Frequent updation means there will be lock to that particular object hence when 5 sessions simultaneously request the nextval then the four sessions will be waiting for "row cache lock" wait resulting in poor performance.

According to Metalink,

When caching and ordering is used in an RAC database, then the session wanting to get the NEXTVAL of a sequence need to get an exclusive instance SV lock before inserting or updating the sequence values in the the shared pool. When multiple sessions want the nextval of the same sequence, then some sessions will wait on 'DFS lock handle' waitevent.

As a consequence of those serialisation mechanisms, the sequence throughput, i.e. the maximum speed at which it is possible to increment a sequence, doesn't scale with the number of RAC nodes, when the sequence is not cached and/or when the sequence is ordered. The sequence throughput is always better for cached sequences compared to non cached sequences.

In summary, due to the serialization mechanisms for non-cached ordered sequences, it takes more time to get a row cache lock or the SQ enqueue when more nodes are involved. That decrease effect is certainly visible when going from 1 to 2 nodes and is still perceptible to a lower extent when going from 2 to 3 nodes, but flat away with more nodes.

Thanks

Gaps in Sequences in Non RAC databases

Gaps in Sequences may occur as a result of one of the following,

1. Rollback of a statement
2. Instance Crash or shutdown
3. Metadata aged out of the Data Dictionary (Use keep pool to avoid)
4. Process cleaned by PMON

As you can see above the number of instances have no part in obtaining Gaps. Hence you can get gaps both in RAC databases and non RAC single instance databases. Let us see an example of how we get Gaps in Single instance Databases.

Create sequence gapSeq start with 1 increment by 1 cache 20;

User A queries nextval and get 1
User B queries nextval and get 2
User B commits.
User A rollbacks.
User C queries nextval and get 3.

Hence the sequence value 1 is lost due to rollback of userA.

Also when you flush the shared pool the cached sequece numbers are lost.

Therefore the Gaps will occur in both the single instance and RAC databases..

Thanks

How Sequences Behave in RAC

Sequences in RAC environment should be used carefully otherwise there will be major performance problems.

The two main concern for the developers in using the sequences are 1. Gaps 2. Order.

Click Here to know How Gaps in sequences can occur even in Non-RAC databases.

The DBAs must know how efficiently to use the options like Cache/NoCache and Order/Noorder.

Let us see when to use the various combinations of Cache/Nocache and order/Noorder and their performance impact.

Cache + NoOrder

• It is the default while creating Sequences without mentioning the options.
• Each instances caches a distinct set of values.
• The Sequences will not be globally ordered.
• Gaps will occur.
• Best Performance.

Cache + Order

• Each instances will cache the same set of values.
• Order of sequences is guaranteed.
• Gaps will occur. (Shared pool Flush or Instance Shutdown)
  
• Performance better than Nocache
  

Note: CACHE VALUE must not be too low otherwise significant performance problem.

NoCache + NoOrder

• No Gaps will occur.
• Ordering is not guaranteed.
• Performance better than Nocache/order

NoCache + Order

• No Gaps will occur.
• Ordering is guaranteed.
• Worst Performance of all.
  

Click Here to view the performance impact of Cache and NoCache options.

The following link will be useful
http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/