Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 590

SQ—Sequence Number
Sequence numbers are used to generate surrogate keys when inserting data into tables to form primary key indexes.
To help improve overall performance, the cache clause preallocates a set of sequence numbers on respective
instances in the cluster. This helps keep sequence numbers in memory to help with faster access when required by
user sessions. However, when these cache values are depleted, a new set of values will be cached. Frequent cache
duplication from multiple instances and frequent requests for new values to be cached causes Oracle to generate SQ
enqueues when the dictionary needs to be updated with the new max value.
Applications that perform high insert operations to the database need to have higher values of sequence numbers
to reduce contention when updates are made to the dictionary. The sequence refresh rates could be determined by
checking the V$ROWCACHE view. In the following output, dc_sequences show high updates almost equal to the GETS
column, which means that almost always, every time a sequence was required, the database sequence had
to be accessed:
Script: MVRACPDnTap_rowcache.sql
SELECT inst_id int
, parameter
, sum(gets) gets
, sum(getmisses) misses
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM GV$ROWCACHE
WHERE gets > 0
HAVING sum(modifications) > 0
GROUP BY INST_ID, parameter
ORDER BY inst_id,
6 desc;
In PARAMETER GETS MISSES PCT_SUCC_GETS UPDATES
---- -------------------------- ----------- -------- ------------- --------
1 dc_histogram_defs 93039 8807 90.5 815
dc_sequences 23432 5480 76.6 23432
dc_objects 63 27 57.1 63
dc_rollback_segments 16549 21 99.9 21
dc_segments 17404 1771 89.8 4
2 dc_histogram_defs 109521 6293 94.3 1060
dc_sequences 16049 4159 74.1 16049
dc_objects 67 26 61.2 67
dc_rollback_segments 19312 31 99.8 42
dc_segments 15475 1510 90.2 16
RAC level contention and global cache related row cache information can also be obtained from V$ROWCACHE view
by querying the DLM_REQUESTS , DLM_CONFLICTS , and DLM_RELEASES columns.