本帖最后由 ziling2011 于 2012-10-19 13:44 编辑
死锁的表是W_ISDN_DATA，一亿条记录
同时有大量进程和session正在进行delete操作
各位大侠给指点小妹一把！
SQL> select sql_text,count(1) from (select distinct sid,sql_text from gv$session s, gv$sql q where sid in (select sid from
2 gv$session where state in ('WAITING') and wait_class != 'Idle')
3 and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id) and s.event='enq: TX - row lock contention')
group by sql_text; 4
SQL_TEXT COUNT(1)
-------------------------------------------------- ----------
delete from W_ISDN_DATA where MSISDN= :1 29
下面是10046事件部分文件内容
PARSING IN CURSOR #1 len=46 dep=0 uid=103 oct=7 lid=103 tim=1350618423743813 hv=2240392737 ad='3af800068' sqlid='9jhs63y2smbj1'
delete from uscdb.W_ISDN_DATA where MSISDN= :1
END OF STMT
PARSE #1:c=0,e=99,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1350618423743813
BINDS #1:
Bind#0
oacdty=96 mxl=128(128) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=128 off=0
kxsbbbfp=2b2130c510a0 bln=128 avl=08 flg=05
value="?`敊櫉
WAIT #1: nam='gc cr block busy' ela= 601 p1=6 p2=43548581 p3=1 obj#=74479 tim=1350618423745198
WAIT #1: nam='gc cr block 2-way' ela= 683 p1=3 p2=28089 p3=147 obj#=0 tim=1350618423745975
WAIT #1: nam='gc cr block 2-way' ela= 250 p1=3 p2=385 p3=127 obj#=0 tim=1350618423746315
WAIT #1: nam='gc cr block 2-way' ela= 251 p1=3 p2=425 p3=131 obj#=0 tim=1350618423746644
WAIT #1: nam='gc cr block 2-way' ela= 260 p1=3 p2=91577 p3=75 obj#=0 tim=1350618423746994
WAIT #1: nam='gc cr block 2-way' ela= 236 p1=3 p2=91433 p3=57 obj#=0 tim=1350618423747331
WAIT #1: nam='gc cr block 2-way' ela= 280 p1=3 p2=913 p3=169 obj#=0 tim=1350618423747691
WAIT #1: nam='gc cr block 2-way' ela= 239 p1=3 p2=297 p3=119 obj#=0 tim=1350618423748009
WAIT #1: nam='gc cr block 2-way' ela= 249 p1=3 p2=473 p3=137 obj#=0 tim=1350618423748350
WAIT #1: nam='gc cr block 2-way' ela= 281 p1=3 p2=249 p3=115 obj#=0 tim=1350618423748707
WAIT #1: nam='gc cr block 2-way' ela= 280 p1=3 p2=201235 p3=138 obj#=0 tim=1350618423749201
WAIT #1: nam='gc current block 2-way' ela= 236 p1=6 p2=45336081 p3=33554433 obj#=74442 tim=1350618423749584
*** 2012-10-19 11:47:14.253
WAIT #1: nam='enq: TX - row lock contention' ela= 10503590 name|mode=1415053318 usn<<16 | slot=3997699 sequence=10201 obj#=74442 tim=1350618434253265
EXEC #1:c=304019,e=10509491,p=0,cr=96,cu=1,mis=1,r=0,dep=0,og=1,plh=3228581750,tim=1350618434253342
ERROR #1:err=60 tim=1350618434253356
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE W_ISDN_DATA (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='PARTITION HASH SINGLE PARTITION: KEY KEY (cr=95 pr=0 pw=0 time=0 us cost=3 size=27 card=1)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=74472 op='INDEX RANGE SCAN XMSIDN_ISDN_DATA PARTITION: KEY KEY (cr=95 pr=0 pw=0 time=0 us cost=3 size=27 card=1)'
=====================
awr报告：
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
USCDB 1932415128 uscdb2 2 18-Oct-12 18:32 11.1.0.7.0 YES
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
rac2 Linux x86 64-bit 12 6 1 23.52
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 811 19-Oct-12 11:00:16 208 1.2
End Snap: 812 19-Oct-12 12:00:18 159 .8
Elapsed: 60.03 (mins)
DB Time: 860.50 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 12,096M 12,096M Std Block Size: 8K
Shared Pool Size: 1,248M 1,248M Log Buffer: 34,292K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 14.3 14.1 0.01 0.01
DB CPU(s): 0.8 0.8 0.00 0.00
Redo size: 124,277.5 122,492.3
Logical reads: 31,453.3 31,001.5
Block changes: 988.5 974.3
Physical reads: 12,704.7 12,522.2
Physical writes: 141.5 139.5
User calls: 2,307.5 2,274.3
Parses: 1,584.2 1,561.5
Hard parses: 0.2 0.2
W/A MB processed: 8,016,882.6 7,901,728.2
Logons: 0.4 0.4
Executes: 1,586.2 1,563.5
Rollbacks: 0.2 0.2
Transactions: 1.0
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.92 Redo NoWait %: 100.00
Buffer Hit %: 98.96 In-memory Sort %: 100.00
Library Hit %: 99.96 Soft Parse %: 99.99
Execute to Parse %: 0.13 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 99.51
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 69.86 71.04
% SQL with executions>1: 88.23 88.34
% Memory for SQL w/exec>1: 82.55 83.78
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: TX - row lock contention 2,651 38,820 14643 75.2 Applicatio
DB CPU 3,004 5.8
direct path read 533,948 2,717 5 5.3 User I/O
gc cr block busy 46,395 2,424 52 4.7 Cluster
db file sequential read 71,693 870 12 1.7 User I/O
^LHost CPU (CPUs: 12 Cores: 6 Sockets: 1)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
5.57 1.76 11.2 3.3 5.1 83.2
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 7.3
% of busy CPU for Instance: 43.3
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 24,085.4 24,085.4
SGA use (MB): 13,184.0 13,184.0
PGA use (MB): 2,237.1 1,781.7
% Host Mem used for SGA+PGA: 64.03 64.03
^LRAC Statistics DB/Inst: USCDB/uscdb2 Snaps: 811-812
Begin End
----- -----
Number of Instances: 2 2
Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 80.94 79.77
Global Cache blocks served: 95.88 94.51
GCS/GES messages received: 274.48 270.54
扫一扫
252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
