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

When numbers are sequentially generated in ascending order, this has a significant effect in the distribution and
scalability for an insert-intensive application. With the reverse option specified with the index creation statement,
the potential issues described with the B-tree indexes (sequential indexes) can be avoided. Reverse key indexes are
created using the following command:
CREATE INDEX PK_JBHIST ON JOB_HISTORY (JOBHIST_ID) REVERSE
A drawback of using reverse key indexes is that they requires more I/O due to loss of proximity. Also, range scans
can cause significant full table scans due to wider spread of index leaf blocks. Local partitions and hash-partitioned
indexes are preferred over reverse keys.
Workshop 1
AWR and SQL based queries against the enqueue statistics view indicate a high amount of requests for database
sequences. Along with the number of requests, the wait times for this statistics also increase.
Step 1
Query the enqueue statistics view to determine the current performance average across the cluster. On noticing that
the 'SQ' type of enqueue has high wait times, perform additional investigation.
The subset of the output generated from the query 'SQ' indicates a high number of requests and high cumulative
wait times. Oracle raises the SQ enqueue when a lock is placed on the database sequence to ensure that only one
process can replenish the sequence cache. This occurs when cache frequently keeps running out and the new cache
has to be replenished more frequently.
Script: MVRACPDnTap_EnqStats.sql
SELECT inst_id INT,
eq_type eq,
eq_name,
total_req# tr,
total_wait# tw,
succ_req# sr,
failed_req# fr,
cum_wait_time cwt
FROM gv$enqueue_statistics
WHERE total_req# > 1000
AND cum_wait_time > 0
AND eq_type IN ( 'TX', 'TM', 'SQ' )
ORDER BY inst_id,
total_req# DESC,
total_wait# DESC,
cum_wait_time DESC;