Database Reference
In-Depth Information
SQL> SELECT object_name, object_id FROM dba_objects WHERE object_id=11986507
/
OBJECT_NAME OBJECT_ID
------------------------------ ----------
T1_SEQ 11986507
In summary, if the derived lock type from
DFS lock handle
wait event is SV, then identify the sequence_name
and alter the sequence attributes matching with workload. Here are guidelines that will help you to decide the
attributes of sequences:
1.
If the sequence is heavily accessed, alter the sequence cache to upwards of 1000 with no
ORDER attribute.
2.
If the sequence is lightly accessed and if the requirement is to retrieve strictly sequential
values, then use
ORDER CACHE
attribute.
3.
If there is no requirement for ordered values, then use
CACHE
without any
ORDER
attribute.
CI Resources
Enqueues on CI resources are acquired to invoke instance-level action(s) in background processes. For example,
truncating a table T1 would require buffers to be flushed from other nodes. So, DBW processes in all nodes must be
posted to invalidate buffers in the buffer cache of that table T1. CKPT process coordinates the instance-level actions,
and DBW process will do the actual scanning and writes.
ASM instances running in clustered mode use DFS lock handle to coordinate the activity too. For example, the
addition of a data file or disk needs to modify extent map in all instances, and that coordination is performed through
DFS lock handle mechanism.
6
,
7
I will explain CI enqueues with a small example, by truncating a table with 10046 event trace on. Reviewing the
trace file, we can identify the trace line marking the wait for DFS lock handle wait event. A few wait events from the
trace file are printed below.
alter session set events '10046 trace name context forever, level 12';
truncate table t1;
alter session set events '10046 trace name context forever, level off';
REM from the trace file
...
nam='DFS lock handle' ela= 844 type|mode=1128857605 id1=13 id2=1 obj#=11984883
nam='DFS lock handle' ela= 914 type|mode=1128857605 id1=13 id2=3 obj#=11984883
nam='DFS lock handle' ela= 8159 type|mode=1128857605 id1=13 id2=2 obj#=11984883
...
Using the SQL statement shown in Listing 11-9, we can identify lock type and lock mode. In this case, lock type is
CI and mode is 5.
6
Sandesh
Rao points out that any disk group operation such as add disk, drop disks, add files, etc., would trigger DFS lock
handle-based concurrency control.
7
This concurrency control is improved in version 12c as concurrent disk group operations can be performed in the same instance.
