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

INT PARAMETER DLM_REQUESTS DLM_CONFLICTS DLM_RELEASES
---- -------------------------- ------------ ------------- ------------
3 dc_sequences 377785 85705 177
dc_histogram_defs 53829 426 20960
outstanding_alerts 43966 21165 25
dc_segments 33304 629 4952
dc_objects 22247 794 8886
dc_object_ids 14328 578 1646
dc_rollback_segments 2679 211 0
dc_tablespace_quotas 1175 64 2

The output above illustrates that the database sequences are used in more than one instance in the cluster and
are distributed requests from other instances in the cluster. During these requests, the sessions faces conflicts.

Step 3
Output from Step 1 indicated high enqueue misses for sequence requests across all the instances in the cluster;
the dc_sequence row cache statistics confirms this. The combination of gets, misses, updates, and the RAC-related
requests against the dc_sequence row cache (Step 2) entry indicated contentions on the sequences generators.
Normally, high contention on database sequences are noticed when they are created with the ORDER clause or
when the CACHE_SIZE is too small. Cache size usage can be monitored using the following query against the
V$_SEQUENCES 1 view:

Script: MVRACPDnTap_seqcachebal.sql
SELECT inst_id INT,
sequence_owner sowner,
sequence_name sname,
cache_size,
nextvalue,
highwater,
highwater - nextvalue balancecache,
background_instance_lock bil,
instance_lock_flags ilf,
active_flag,
order_flag
FROM gv$_sequences
WHERE sequence_owner NOT IN ( 'SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP' )
ORDER BY inst_id,
sequence_owner;

From the output (not shown) of the above query, certain sequences were found to be defined with default
cache value of 20 and with the ORDER clause, which causes the SQ enqueue wait event discussed in Step 1 to trigger
every time the cache needs to be replenished. Apart from the SQ enqueue there are other lock-related events that
are also triggered.

V$_SEQUENCES is an undocumented view.

1