Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 282

When sequences are defined with CACHE and ORDER clauses in RAC environment, and when multiple instances
are inserting rows using the same SEQUENCE definition, the session requesting for the NEXTVAL of the sequence requires
an exclusive access to the sequence. It requests a SV lock before inserting or making updates to the sequence value in
the shared pool. Frequent requests from multiple instances for the NEXTVAL can cause severe contention and sessions
may have to wait for an Enq: SV - contention event (Figure 9-3 ), followed by other wait events due to the repel effect
such as gc current block busy and gc buffer busy acquire .
Figure 9-3. AWR report: Top Timed Events
While sequences can be defined with CACHE and ORDER clauses, performance impacts should be ascertained
before implementing this option. In a RAC environment, unless absolutely required to satisfy business requirements,
it's a good practice to use the CACHE and NOORDER clause when creating sequences.
Highly insert intensive tables that use sequences numbers for surrogate keys should have sufficiently high CACHE
size. This will reduce the frequent updates to the dictionary cache. The dictionary cache will be updated only once
with the new high watermark; in other words, when a NEXTVAL is requested, the row cache value of the sequence is
changed in the dictionary cache and increased by the cache value.
Step 4
Modify the sequence definitions to increase the CACHE_SIZE for the demanding sequence definitions; setting them to
NOORDER will reduce the contention and improve the overall response times.