This content has been marked as final. Show 7 replies
Are you using NOCACHE NOORDER? It is the best option for RAC config.
For your convinience I suggest you to read the MOS NOTE: RAC and Sequences [ID 853652.1]
There's a lot of influence using Sequences in RAC, be aware of it.
Just a preview:
1. Sequence setup
Different sequence setups are available.
a. CACHE + NOORDER
This setting has the least performance impact on RAC, and it is the default when creating a new sequence wihtout options. Each instances caches a distinct set of numbers in the shared pool. Sequences will not be globally ordered, and bigger gaps can occur when the shared pool is refreshed (e.g. instance shut down) like on single instance databases.
b. CACHE + ORDER
Each instances caches the same set of numbers. Ordering of sequence numbers is guaranteed, and bigger gaps can occur. Performance is better than with NOCACHE sequences. Gaps in the sequence numbering occur when the sequence cache is lost e.g. any shared pool flush or instance shutdown like an single instance databases.
c. NOCACHE + NOORDER
Use this setting when e.g. government regulations or laws legally require sequence numbers with limited gaps (see point 2). Ordering is not guaranteed. It has a better performance than NOCACHE / ORDER.
d. NOCACHE + ORDER
Use these settings when ordered sequences are required; no gaps will occur (except for point 2 examples) and ordering is guaranteed. This setting though has the most negative performance impact on RAC.
In Always RAC we have to use NOCACHE + NOORDER. Becuase when genarating sequence one instance second instance will try for same. So we have to give these two options so that second instance will genarate some number without order. So we will not get any locking issues or performance issues.
As stated, one of the application "problems" that can arise would be to "require" ordered sequences. This is the worst option for RAC specifically because of the multiple instances trying to do inserts concurrently. I would typically never used ORDERED sequences in a RAC environment. Requiring sequential ordered sequences are problematic even in a single node database. NOCACHED is also very costly in that for EACH request, you must lock+increment the sequence serially. This is extremely expensive in terms of locking.