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/