问题描述
在 Oracle 数据库中出现 DFS lock handle 等待事件时,如果是 CI 锁,会有多种类型,可能的类型会有哪些种,经常遇到,请解答。
>select * from v$lock_type where type='CI';
TYPE NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
---------- ---------------------------------------- ---------- ---------- --- --------------------------------------------------
CI Cross-Instance Call Invocation opcode type NO Coordinates cross-instance function invocations
TIME SESSION_ID SQL_ID EVENT BLOCKING_SESSION Lock Mode P1 P2 P2TEXT P3 P3TEXT
------------------- ---------- ------------- -------------------- ---------------- ---- ---------- ---------- ---------- --------------- ---------- ---------------
2018/08/24 09:00:00 10 040mmwd8ptpkp DFS lock handle 2298 CI 5 1128857605 10 id1 5 id2
专家解答
通常的情形是这样的,例如等待事件的参数如下:
WAIT #5: nam='DFS lock handle' ela= 4117 p1=1128857605 p2=1 p3=5
P1 包含了锁类型 和 锁模式,43 49 就是 CI 的ASCII编码,代表了类型。
p1=1128857605= 0x43490005
^^^^^^^^
CI
P2 id1=1 代表了种类,是预先定义好的,1 代表了: Reuse (checkpoint and invalidate) block range
一下是 CI 锁类型的 id1 代表的含义:
1 reuse (checkpoint and invalidate block range)
2 LGWR Checkpointing and Hot Backup
3 DBWR syncronization of SGA with control file
4 log file add/drop/rename notification
5 miscellaneous CTWR operations
7 Manged standby recovery related
8 alter rollback segment optimal
9 Signal Query Servers/coordinator
10 Create Remote parallel query Server
11 Set Global Partitions
12 Stop Disk Writes
13 Drop Sort Segments
14 Release unused space from Sort Segments
15 instance Recovery for Parallel operation Group
16 Validate parallel slave Lock Value
17 check transaction state objects
18 object reuse request
19 rolling release checks
20 propagate begin backup scn for a file
21 refresh top plan (for db scheduler
22 clear checkpoint progress record
23 drop temp file
24 QUiesce database Restricted
25 Update Dscn Tracking (ktcndt
26 Purge dictionary Object number Cache
27 set Database Force Logging mode
28 invalidate cached file address translations
29 Cursor Unauthorize Mode
30 process waiters after row cache requeue
31 Active Change Directory extent relocation
32 block change tracking state change
33 kgl mulitversion obsolete
34 set previous resetlogs data
35 set recovery destination pointer
36 fast object reuse request
38 ASM diskgroup discovery wait
39 ASM diskgroup release
40 ASM push DB updates
41 ASM add ACD chunk
42 ASM map resize message
43 ASM map lock message
44 ASM map unlock message (phase 1
45 ASM map unlock message (phase 2
46 ASM generate add disk redo marker
d47 ASM check of PST validity
48 ASM offline disk CIC
49 Logical Standby Sync Point SCN
50 update SQL Tuning Base existence bitvector
51 PQ induced Checkpointing
52 ASM F1X0 relocation
53 Scheduler autostart
54 KZS increment grant/revoke counter
55 ASM disk operation message
56 ASM I/O error emulation
57 DB Supp log cursor invalidation
58 Cache global range invalidation
59 Cache global object invalidation
60 ASM Pre-Existing Extent Lock wait
61 Perform a ksk action through DBWR
62 ASM diskgroup refresh wait
63 KCBO object checkpoint
64 KCBO object pq checkpoint
65 global health check event
66 Oracle Label Security refresh
67 thread internal enable
68 cross-instance registration
69 KGL purge unused subheaps
70 clear pin instance flag
71 Rolling operations CIC
以下是 id2 的含义:
1 used to pass in parameters
2 used to invoke the function in backgroud process
3 used to indicate the foreground has not returned
4 mounted excl, use to allocate mechanism
5 used to queue up interested clients
通过 v$lock_type 视图可以找到不同类型的锁参数含义。