Database Reference
In-Depth Information
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
row cache lock 4586 0.76 255.01
Disk file operations I/O 7 0.00 0.00
db file sequential read 3 0.01 0.03
gc current block busy 1064 0.46 7.08
gc current block 2-way 2660 0.05 3.36
In a RAC database, sequence values can be cached in all instances. This sequence caching leads to retrieved
sequence values not correlating with a time component. Consider the following scenario:
1.
Session 1 connects to PROD1 instance and accesses a sequence emp_id_seq, retrieving a
value of 1,022. Values in the range of 1,022 to 1,041 will be cached in PROD1 SGA for that
sequence, assuming a default cache of 20.
2.
Another session connecting to PROD2 will retrieve a value of 1,042 from that sequence.
Values in the range of 1,042 to 1,061 will be cached in the PROD2 instance SGA.
3.
If session 1 connected to the PROD1 instance accesses the sequence again, a value of 1,023
will be returned.
So, the retrieved sequence values sorted by time will be 1,022, 1042, and 1,023, clearly not correlating with a
time component.
In a RAC database, if the attributes of a frequently accessed sequence are set to
ORDER
,
NOCACHE
, performance
problems will be magnified. As uncached sequences trigger an update to a seq$ table for every access, row cache locks
are acquired aggressively. In a RAC database, row cache locks are globalized as GES layer locks, so additional GES
locks must be acquired before updating dictionary tables. Essentially, a magnification effect comes into play, and a
huge amount of waiting for GES locks and row cache lock wait events will be the result.
If you must retrieve sequence values in a strict sequential order, one option is to use
ORDER
,
CACHE
attributes of
sequences. In RAC, a GES resource is used to maintain the current maximum cached value of a sequence, reducing
updates to a dictionary table. Using the attributes
ORDER
and
CACHE
is a much better option if you must retrieve values
in a strict sequential order. Still, if the resource master instance of the sequence crashes, then there is a possibility of
losing cached sequence values. Note that an excessive amount of access to sequences with
ORDER
and
CACHE
attributes
results in longer waits for the DFS lock handle event.
The test case in Listing 6-1 was repeated with sequence attributes set to
ORDER
,
CACHE
20. The lines from the tkprof
output file in Figure
6-2
show that the insert statement completed in about 12 seconds compared to 282 seconds in
the case of uncached sequences test case in Listing 6-2.
Listing 6-2.
Sequence with ORDER and CACHE
INSERT INTO RS.T_GEN_SEQ_02
VALUES
( RS.T_GEN_SEQ_02_SEQ.NEXTVAL, LPAD ('Gen',25,'DEADBEEF')
call count cpu elapsed disk query current rowsf
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 5001 0.94 12.60 0 910 16440 5001
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5002 0.94 12.62 0 910 16440 5001
