联系：手机/微信(+86 17813235971) QQ(107644445)
标题：分析一例 TX Enqueue contention案例
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
应用反馈某个业务比较慢,需要紧急处理
查询等待事件
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> select A.INST_ID,count(*), event
2 from Gv$session_wait a
3 where event not in ('SQL*Net more data to client',
4 'rdbms ipc message',
5 'smon timer',
6 'pmon timer',
7 'SQL*Net message from client',
8 'lock manager wait for remote message',
9 'ges remote message',
10 'gcs remote message',
11 'gcs for action',
12 'client message',
13 'pipe get',
14 'null event',
15 'PX Idle Wait',
16 'single-task message',
17 'PX Deq: Execution Msg',
18 'KXFQ: kxfqdeq - normal deqeue',
19 'listen endpoint status',
20 'slave wait',
21 'wakeup time manager','jobq slave wait')
22 group by INST_ID,event
23 order by 1 desc,2 desc;
INST_ID COUNT(*) EVENT
---------- ---------- --------------------------------------
2 8 enqueue
2 1 async disk IO
2 1 db file sequential read
2 1 SQL*Net message to client
2 1 PX Deq: reap credit
1 2 global cache cr request
1 1 async disk IO
1 1 PX Deq: reap credit
1 1 PX Deq: Execute Reply
9 rows selected.
发现enqueue等待有些多,怀疑是TX enquenue,查询阻塞者
SQL> set linesize 100
SQL> set pagesize 66
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
2 from gv$lock l,gv$session s
3 where l.type like 'TX' and l.REQUEST =6
4 and l.inst_id=s.inst_id and l.sid=s.sid
5 order by id1
6 /
INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST
---------- ---------- --------------- -- ---------- ---------- ---------- ----------
2 295 rtStopMain@zwq_ TX 1441805 2391806 0 6
bill_2 (TNS V1-
V3)
2 992 rtStopMain@zwq_ TX 1441805 2391806 0 6
bill_2 (TNS V1-
V3)
2 1238 rtStopMain@zwq_ TX 6946827 2546365 0 6
bill_2 (TNS V1-
V3)
2 1298 rtStopMain@zwq_ TX 6946827 2546365 0 6
bill_2 (TNS V1-
V3)
2 1684 rtStopMain@zwq_ TX 6946827 2546365 0 6
bill_2 (TNS V1-
V3)
2 1553 rtStopMain@zwq_ TX 6946827 2546365 0 6
bill_2 (TNS V1-
V3)
2 75 rtStopMain@zwq_ TX 12451856 199146 0 6
bill_2 (TNS V1-
V3)
2 1125 rtStopMain@zwq_ TX 14352404 63837 0 6
bill_2 (TNS V1-
V3)
查询持有者
SQL> set linesize 100
SQL> set pagesize 66
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
2 from gv$lock l,gv$session s
3 where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in
4 (select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)
5 and l.inst_id=s.inst_id and l.sid=s.sid
6 order by id1
7 /
INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST
---------- ---------- --------------- -- ---------- ---------- ---------- ----------
2 75 rtStopMain@zwq_ TX 1441805 2391806 6 0
bill_2 (TNS V1-
V3)
2 992 rtStopMain@zwq_ TX 6946827 2546365 6 0
bill_2 (TNS V1-
V3)
2 295 rtStopMain@zwq_ TX 12451856 199146 6 0
bill_2 (TNS V1-
V3)
2 1553 rtStopMain@zwq_ TX 14352404 63837 6 0
bill_2 (TNS V1-
V3)
通过持有者和阻塞者可以得出:
1.持有者和阻塞者都是在2号实例上
2.持有者75阻塞了295/992的会话
3.持有者992阻塞了1238/1298/1684/1553的会话
4.持有者295阻塞了75的会话
5.持有者1553阻塞了1125的会话
6.同时分析发现,所有的持有者sid也在阻塞者中,也就是持有者阻塞了某个sid,而自身又被其他sid给阻塞,形成了多级阻塞或者环.如:75阻塞了295,而295有阻塞了75;992阻塞了1553,而1553阻塞了1125
查询阻塞和持有者对象
SQL> set linesize 110
SQL> col c0 for 999
SQL> col c0 heading "INS"
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,
2 ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
3 from gv$session
4 where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')
5 /
INS SID Program Name OBJECT_NO RFILE_NO BLOCK_NO ROW_NO
---- ---------- --------------- ---------- ---------- ---------- ----------
2 75 rtStopMain@zwq_ 1323132 13 122601 111
bill_2 (TNS V1-
V3)
2 295 rtStopMain@zwq_ 1323132 13 122601 100
bill_2 (TNS V1-
V3)
2 992 rtStopMain@zwq_ 1323132 13 122601 101
bill_2 (TNS V1-
V3)
2 1125 rtStopMain@zwq_ 1323132 84 38445 70
bill_2 (TNS V1-
V3)
2 1238 rtStopMain@zwq_ 1323132 15 255066 41
bill_2 (TNS V1-
V3)
2 1298 rtStopMain@zwq_ 1323132 14 118411 8
bill_2 (TNS V1-
V3)
2 1553 rtStopMain@zwq_ 1323132 15 255066 19
bill_2 (TNS V1-
V3)
2 1684 rtStopMain@zwq_ 1323132 14 118411 21
bill_2 (TNS V1-
V3)
8 rows selected.
SQL> set linesize 100
SQL> set pagesize 100
SQL> col owner for a10
SQL> col object_name for a20
SQL> col object_type for a10
SQL> select owner,object_name,object_id,object_type
2 from dba_objects
3 where
4 object_id in (select ROW_WAIT_OBJ# from gv$session
5 where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1='1415053318'))
6 /
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYP
---------- -------------------- ---------- ----------
DBACCADM DCUSTCREDITBALANCE 1323132 TABLE
通过查询的出来,所有操作的聚焦点都是在DBACCADM.DCUSTCREDITBALANCE表上面
查询相关sql语句
SQL> SQL> set linesize 120
SQL> set pagesize 66
SQL> col c0 for 999
SQL> col c0 heading "INS"
SQL> col c1 for a9
SQL> col c1 heading "OS User"
SQL> col c2 for a9
SQL> col c2 heading "Oracle User"
SQL> col c3 for a15
SQL> col c3 heading "Program Name"
SQL> col b1 for a9
SQL> col b1 heading "Unix PID"
SQL> col b2 for 9999 justify left
SQL> col b2 heading "ORA SID"
SQL> col b3 for 999999 justify left
SQL> col b3 heading "SERIAL#"
SQL> col sql_text for a45
SQL> set space 1
SQL> break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2
SQL> select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text
2 from gv$sql a, gv$session b, gv$process c
3 where
4 a.address = b.sql_address
5 and b.paddr = c.addr
6 and a.hash_value = b.sql_hash_value
7 and a.inst_id=b.inst_id and a.inst_id=c.inst_id
8 and a.inst_id like '&inst_id' and b.sid like '&sid'
9 order by c.spid,a.hash_value
10 /
Enter value for inst_id: 2
Enter value for sid: 75
old 8: and a.inst_id like '&inst_id' and b.sid like '&sid'
new 8: and a.inst_id like '2' and b.sid like '75'
INS ORA SID Unix PID Program Name Oracle Us SERIAL# SQL_TEXT
---- ------- --------- --------------- --------- ------- ---------------------------------------------
2 75 1167392 rtStopMain@zwq_ DBCUSTOPR 42815 update dcustcreditbalance set limit_owe=:b0,
bill_2 (TNS V1- unbill_fee=:b1,prepay_fee=:b2,owe_fee=:b3,op_
V3) time=sysdate where id_no=:b4
SQL> /
Enter value for inst_id: 2
Enter value for sid: 992
old 8: and a.inst_id like '&inst_id' and b.sid like '&sid'
new 8: and a.inst_id like '2' and b.sid like '992'
INS ORA SID Unix PID Program Name Oracle Us SERIAL# SQL_TEXT
---- ------- --------- --------------- --------- ------- ---------------------------------------------
2 992 2760870 rtStopMain@zwq_ DBCUSTOPR 56282 update dcustcreditbalance set limit_owe=:b0,
bill_2 (TNS V1- unbill_fee=:b1,prepay_fee=:b2,owe_fee=:b3,op_
V3) time=sysdate where id_no=:b4
SQL> /
Enter value for inst_id: 2
Enter value for sid: 295
old 8: and a.inst_id like '&inst_id' and b.sid like '&sid'
new 8: and a.inst_id like '2' and b.sid like '295'
INS ORA SID Unix PID Program Name Oracle Us SERIAL# SQL_TEXT
---- ------- --------- --------------- --------- ------- ---------------------------------------------
2 295 1639008 rtStopMain@zwq_ DBCUSTOPR 35740 update dcustcreditbalance set limit_owe=:b0,
bill_2 (TNS V1- unbill_fee=:b1,prepay_fee=:b2,owe_fee=:b3,op_
V3) time=sysdate where id_no=:b4
其他阻塞者和持有者执行sql语句均和该语句相同,省略其他查询.通过这些查询可以确定是因为对dcustcreditbalance表的更新操作导致了这样的现象发生.
处理方案
1.临时处理方案:kill掉持有者
2.永久处理方案:修改这部分程序业务逻辑