Database Reference
In-Depth Information
Listing 11-9.
Identify Lock Type from p1.REM Identify Lock Type
SELECT chr(bitand(&&p1,-16777216)/16777215) || chr(bitand(&&p1,16711680)/65535) type,
mod(&&p1, 16) md
FROM dual
/
TY MD
-- ----------
CI 5
There are numerous reasons for DFS lock handle waits with enqueue type of CI. Fortunately, note 34631.1
provides mapping between id1, id2 values and the semantics of the combination, but the list is not complete.
You might need to engage Oracle support for further analysis, as performance issues due to CI resource type could
be a software bug too.
A quick approach to gain more knowledge about specific id1, id2 value combinations of CI lock type is to identify
the background process associated with that resource. Using id1=13 and id2=2, we can coin a resource name as
[0xd][0x2],[CI]
and search in gv$ges_enqueue view. In Listing 11-10, PID 23557 in node 2 is a DBW process holding
locks on this resource. You can make an educated guess about the problem with SQL statement affected and the
background process involved.
Listing 11-10.
Resources with CI Enqueue
SELECT resource_name1, inst_id, pid, blocker, blocked
FROM gv$ges_enqueue
WHERE resource_name1 LIKE '%[0xd][0x2],[CI]%'
/
RESOURCE_NAME1 INST_ID PID BLOCKER BLOCKED
------------------------------ ---------- ---------- ---------- ----------
[0xd][0x2],[CI][ext 0x0,0x0] 2 23557 0 0
[0xd][0x2],[CI][ext 0x0,0x0] 3 4213 0 0
[0xd][0x2],[CI][ext 0x0,0x0] 1 10753 0 0
A few ASM-related CI events are printed in Table
11-4
. For example, “ASM map resize message” event will use
the resource [0x2a][0x0],[CI], where 0x2a is the hexadecimal representation of 42. These resources will be visible in
ASM instance(s).
Table 11-4.
ASM-Specific CI - id1
CI - id1
Meaning in ASM instance
42
ASM map resize message
43
ASM map lock message
44
ASM map unlock message (phase 1)
Events for ASM-related operations are usually short-lived. However, when you are add a new datafile to a
tablespace or extend a datafile, in a database with higher activity, then ASM-related wait events will be prominent.
Note that, usually, DFS lock handle contention with CI as resource type is just a symptom of another root
cause lurking underneath. For example, excessive truncates of tables in huge buffer caches induce DFS lock handle
contention with CI resource type. The root cause in that example is excessive truncation, and use of global temporary
