Database Reference
In-Depth Information
The problem with the caching sequence value is that in case of instance failure, cached values are permanently
lost. For example, consider a sequence with a next value of 1,014 and the maximum cached value of 1,021 recorded in
a seq$ table. In an instance crash, values from 1,014 to 1,021 will be permanently lost. If the application code assumes
that sequence values will be in a strict sequential order, the loss of cached values can lead to unnecessary application
data issues. Also, note that losing sequence values does not imply a data loss scenario, only that cached sequence
values are permanently lost. Application designers alter attributes of sequences to
ORDER
,
NOCACHE
to impose strict
sequential order. The
ORDER
and
NOCACHE
attributes have adverse effects if the sequences are accessed very frequently.
Every access to the sequence forces an update to the seq$ table. As updates to the seq$ table are performed under the
protection of a row cache lock, if many sessions are trying to access sequence concurrently, then the
row cache lock
event can be seen as a major wait event.
■
updates to the seq$ table are not the only cause of row cache lock event waits. Further review of details is
warranted to identify the root cause. Chapter 11 discusses row cache locks further.
Note
The following few lines from a SQL trace shows that the seq$ table is updated for each access to the sequence if the
attributes of the sequence are set to
ORDER
,
NOCACHE
. The wait for row cache lock event is also shown in the following.
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,
audit$=:9,flags=:10,partcount=:11 where obj#=:1
nam='row cache lock' ela= 101 cache id=13 mode=0 request=5 obj#=89956 tim=1364157893629929
In the preceding wait line, cache_id is set to 13. Querying v$rowcache view, you can identify the type of rowcache
entry. In this example, the update to the seq$ table is the reason to lock a row cache.
select type, parameter from v$rowcache where cache#=13;
TYPE PARAMETER
----------- --------------------------------
PARENT dc_sequences
The lines from a tkprof output of a SQL trace file (see Listing 6-1) show the impact of uncached sequences in a
RAC database. About 255 seconds are spent on row cache lock waits in a total run time of 282 seconds. Note that there
are no waits for the DFS lock handle event since the sequence attribute is set to NOCACHE. This difference will be
relevant when I discuss cached sequences later in this section.
Listing 6-1.
Performance of Uncached Sequence
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 rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5001 7.71 282.75 3 333 20670 5001
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5002 7.71 282.75 3 333 20670 5001
