Database Reference
In-Depth Information
Listing 11-7.
DFS Lock Handle Trace File Output
REM A line from a trace file.
nam='DFS lock handle' ela= 4362 type|mode=
1398145029
id1=86033 id2=0 obj#=-1 ...
SQL> SELECT chr(bitand(&&p1,-16777216)/16777215) || chr(bitand(&&p1,16711680)/65535) type,
mod(&&p1, 16) md
FROM dual
/
Enter value for p1: 1398145029
TY MD
-- ----------
SV 5
You can query v$lock_type to understand the functionality of a lock type. In this example, SV lock type is used for
Sequence Ordering, and
id1
column value indicates the object_id. Lock type SV will be covered later in this chapter.
exec print_table ( 'select * from v$lock_type where type=''SV''');
TYPE : SV
NAME : Sequence Ordering
ID1_TAG : object #
ID2_TAG : 0
IS_USER : NO
DESCRIPTION : Lock to ensure ordered sequence allocation in RAC mode
After identifying the lock type and mode, you can probe further to understand the root cause of DFS lock handle
contention. In the next section, I will probe individual lock types and discuss practical methods to resolve the
locking contention.
SV Resources
As discussed in the preceding section, SV enqueue is used for sequence-related actions. Each instance caches
sequence values in SGA. In RAC, due to instance-level caching, queried sequence values may not correlate well with
time-based column values. Designers typically tend to resolve this problem by creating sequences with nocache or
ORDER attributes.
The CACHE attribute of a sequence dictates that sequence values are cached in instance SGA. The ORDER
attribute of a sequence dictates that values will be retrieved in strict sequential order. Every retrieval from a sequence
with ORDER NOCACHE attribute modifies the seq$ dictionary table to guarantee a strict sequential order. This
method of retrieval leads to excessive amount of “row cache lock” waits (changes to data dictionary tables require row
cache locks to be held) and global cache wait events.
If a sequence is defined with ORDER CACHE attributes, then GES layer is used to maintain the strict sequential
order. Essentially, DFS lock handle mechanism with an SV resource is used for this purpose. To explain DFS lock
handle contention for SV resource, I will create a sequence
T1_SEQ
with
ORDER
and
CACHE
attributes. Then, I will access
the sequence a few times and increase the sequence value to 9.
REM Create a sequence with cache and order attributes.
create sequence T1_SEQ order cache 100;
SQL> select t1_seq.nextval from dual;
