Oracle辅导:一个enqueue等待事件的分析
数据库:Oracle 9.2.0.8 平台:HP-UX 11.11在对客户的数据库进行健康搜检时发现:
TOP 5期待事务中最高的是enqueue,其次是PX Deq: Execute Reply,这两个期待事务占了所有事务的65%以上。
期待事务“enqueue”所占比例最高,一共期待了14,487s的时刻,但年夜后面的enqueue的统计信息来看,所统计的SQ enqueue和TX enqueue的期待时刻总和只有49s,其他统计的enqueue期待时刻根基为0s,所以还有其他未统计进statspack的enqueue存在,并占有了年夜高摒弃待时刻。
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 6,339 14,487 44.02
PX Deq: Execute Reply 2,773 7,222 21.94
CPU time 5,314 16.15
db file sequential read 982,418 3,574 10.86
row cache lock 80,167 691 2.10
-------------------------------------------------------------
Enqueue activity for DB: IBSSInstance: ibss2Snaps: 311 -312
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
SQ 5,589 5,589 0 206 181.50 37
TX 189,789 189,398 391 163 76.10 12
HW 1,601 1,599 2 423 .55 0
US 1,949 1,949 0 339 .67 0
TM 912,315 912,254 56 155 .36 0
TT 638 638 0 48 .92 0
FB 39 39 0 26 .31 0
TD 16 16 0 16 .50 0
IR 10 10 0 8 .63 0
WL 8 6 2 6 .67 0
DR 4 4 0 4 .50 0
RO 3 3 0 3 .67 0
TA 26 26 0 2 .50 0
DL 123 123 0 1 1.00 0
TS 6 6 0 5 .00 0
CF 12,113 12,113 0 1 .00 0
-------------------------------------------------------------
Oracle辅导:一个enqueue等待事件的分析
</p> 采样发芽期待事务为enqueue的session:SQL> select sid,status,program,sql_hash_value from v$session where sid in (select sid from v$session_wait where event='enqueue');
SID STATUS PROGRAM SQL_HASH_VALUE
---------- -------- ------------------------- --------------
194 ACTIVE ORACLE.EXE 4166735688
311 ACTIVE oracle@IBSSDB2 (P000) 1611783128
在多个时刻点采样发现,enqueue期待事务始终只有两个这session和这两个sql_hash_value。
查找sid=194的sql:
SQL> select sql_text from v$sql where hash_value=http://www.qnr.cn/pc/ora/study/201001/4166735688;
no rows selected
sid:194找不到响应的sql。
查找sid=311的sql:
SQL> select sql_text from v$sql where hash_value=http://www.qnr.cn/pc/ora/study/201001/1611783128;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ Q2447000 */ A1."INST_ID",A1."FORMATID",A1."GLOBALID",A1."BRANCHID",A1
."REFCOUNT" FROM "SYS"."GV$GLOBAL_TRANSACTION" A1 WHERE A1."FORMATID"=:B1 AND (R
AWTOHEX(A1."GLOBALID")=:B2 OR A1."GLOBALID"=:B3) AND (RAWTOHEX(A1."BRANCHID")=:B
4 OR A1."BRANCHID"=:B5)
经由过程V$LOCK剖析锁:
SESS ID1 ID2 LMODE REQUEST TY
---------------- ---------- ---------- ---------- ---------- --
Holder: 217 18 0 4 0 DX
Waiter: 311 18 0 0 4 DX
Waiter: 194 18 0 0 6 DX
在锁的期待中,持有者是217,期待者是311和192,期待的锁为DX锁。
DX锁:Distributed transaction entry
经由过程用oradebug剖析,同样发现session 311被session 194梗阻,而session 194被session 217梗阻。
Open chains found:
Chain 1 ::
----
页:
[1]