db: ORACLE 9i -- OLTP -- 150GB
OS: Windows 2003 server
无法通过sqlplus连上db，用户无法访问，资源基本耗尽。
重启后正常，检查原因发现:
一次死锁以后在短时间(1个小时左右)产生了一个很庞大的trace文件ax03_ora_5832.trc，大小为390,899,234 bytes。 里面除了记录着一些deadlock的信息，其他都是乱码。
请问是什么导致这个呢?
*** 2007-05-30 15:45:05.152
*** SESSION ID
69.8406) 2007-05-30 15:45:05.120
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM AAAA WHERE (SUBSTR(NLS_LOWER(BBBB),1,3)=NLS_LOWER(:in1))
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-----------------Waiter(s)---------
Resource Name
process session holds waitsprocess session holds waits
TX-000a0017-0003348b 19569 X
18273 X
TX-00070029-000332a6 18273 X
19569 X
session 69: DID 0001-00C3-00000015
session 73: DID 0001-00B6-00000004
session 73: DID 0001-00B6-00000004
session 69: DID 0001-00C3-00000015
Rows waited on:
Session 73: obj - rowid = 0003497D - AAA0l9AAdAAAS6YAAG
(dictionary objn - 215421, file - 29, block - 77464, slot - 6)
Session 69: obj - rowid = 0003497D - AAA0l9AAdAAAS6YAAK
(dictionary objn - 215421, file - 29, block - 77464, slot - 10)
Information on the OTHER waiting sessions:
Session 73:
pid=182 serial=48689 audsid=60085 user: 97/BMSSAPX8
O/S info: user: axcom, term: SZHSVR045, ospid: 5212:5892, machine: CN\SZHSVR045
program: Ax32Serv.exe
application name: Ax32Serv.exe, hash value=0
Current SQL Statement:
...................................................................
................................................................
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 3A3A5E38, call: 4184E9D0, xact: 3B9CB724, curses: 3AB7D2E0, usrses: 3AB7D2E0
----------------------------------------
SO: 3A3A5E38, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=195, calls cur/top: 4184E9D0/4184E9D0, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 4
last post received-location: kslpsr
last process to post me: 3a377420 1 6
last post sent: 0 0 54
last post sent-location: kcrfwr: wake LGWR after redo copy
last process posted by me: 3a377420 1 6
(latch info) wait_event=0 bits=10
holding 21b0ba8 Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Context saved from call: 0
state=busy
recovery area:
Dump of memory from 0x3AB68DF4 to 0x3AB68DFC
3AB68DF0
00000000 00000000
[........]
Process Group: DEFAULT, pseudo proc: 3A3F05A8
O/S info: user: SYSTEM, term: SZHSVR044, ospid: 5832
OSD pid info: Windows thread id: 5832, image: ORACLE.EXE
----------------------------------------
SO: 3B101D30, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f4bfc incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B1011BC, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f4418 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100D84, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2ef560 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100AFC, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f46b4 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100808, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2ef7fc incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100730, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f1234 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B1006C4, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f2c7c incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100658, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f14e0 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B1005EC, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f3c34 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100580, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f3460 incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B100514, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2f177c incno=0 pending i/o cnt=0
----------------------------------------
SO: 3B1002E8, type: 8, owner: 3A3A5E38, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=3b2ef2b4 incno=4 pending i/o cnt=0
----------------------------------------
SO: 3AB7D2E0, type: 4, owner: 3A3A5E38, flag: INIT/-/-/0x00
(session) trans: 3B9CB724, creator: 3A3A5E38, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0001-00C3-00000015, short-term DID: 0000-0000-00000000
txn branch: 00000000
oct: 7, prv: 0, sql: 4DC02F14, psql: 4DC02F14, user: 97/BMSSAPX8
O/S info: user: CN\xu28hz, term: SZHSVR045, ospid: 5364:5228, machine: CN\SZHSVR045
program: ax32.exe
application name: ax32.exe, hash value=0
last wait for 'enqueue' blocking sess=0x3ab7e5c0 seq=41361 wait_time=2992450
name|mode=54580006, id1=70029, id2=332a6
temporary object counter: 0
----------------------------------------
SO: 3B9CB724, type: 38, owner: 3AB7D2E0, flag: INIT/-/-/0x00
(trans) bsn = 1192338, flg = 0x1e03, flg2 = 0x00, prx = 0x0, ros = 2147483647, spn = 1223691
efd = 5
parent xid: 0x0000.000.00000000
env: (scn: 0x0000.18ed3b80xid: 0x000a.017.0003348buba: 0x00c083b9.6e5f.05statement num=0parent xid: xid: 0x0000.000.00000000scn: 0x0000.18ed3c15 0sch: scn: 0x0000.00000000)
cev: (spc = 7436arsp = 3b52b59cubk tsn: 1 rdba: 0x00c083b9useg tsn: 1 rdba: 0x00800099
hwm uba: 0x00c083b9.6e5f.05col uba: 0x00000000.0000.00
num bl: 15 bk list: 0x3b467b3c)
(enqueue) TX-000A0017-0003348B
DID: 0001-00C3-00000015
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res: 3a4e2ad0, mode: X, prv: 3a4e2ad8, sess: 3ab7d2e0, proc: 3a3a5e38
xga: 0x0, heap: UGA
----------------------------------------
SO: 3B467B3C, type: 37, owner: 3B9CB724, flag: -/-/-/0x00
(List of Blocks) next index = 18
index savepoint itli buffer hint rdba
--------------------------------------------------
0 12213802 0x207efb240x3838e59
1 12216582 0x2bfe2cc80x5c14096
2 12216602 0x27fd65c40x5c14126
3 12216912 0x1d7f7aec0x7412e8f
4 12219912 0x297f75c80x7412e90
5 12221113 0x20ff6a080x7412e91
6 12222082 0x1d7d2d380x5c140f4
7 12222962 0x25ffe79c0x7412e92
8 12226082 0x24ff52880x5c14099
9 12226362 0x24ffe9d00x7412e93
10 12228962 0x1e7e08100x7412e94
11 12230012 0x1cfe90a80x7412e95
12 12231052 0x1fffe3340x5c1411f
13 12232262 0x2a7d6ae80x7412e96
14 12232352 0x2bff8c8c0x5c14132
15 12232582 0x1ffd8b380x5c1409d
16 12233961 0x2bfdb1680x7412e97
17 12236812 0x21ff76840x7412e98
----------------------------------------
SO: 3B488B8C, type: 37, owner: 3B9CB724, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint rdba
--------------------------------------------------
|
