如何从trace文件中查找引起问题的sql-技术开发专区

如何从trace文件中查找引起问题的sql

作者：IT168黄血成编辑：晓熊 2008-09-27 11:46 来源：IT168�

例子：

下面我们举例说明在trace文件中如何提取sql语句。
特别注意：
如果使用字面值替代绑定变量，会导致优化器选择不同的查询路径，从而使得问题可能不会再现。
Example 1:

******************** Cursor Dump ************************
Current cursor: 2, pgadep: 1
Cursor Dump:
----------------------------------------
我们查找cursor 2:
----------------------------------------
Cursor 2 (20139ad0): CURFETCH curiob: 2013bca4
curflg: 7 curpar: 20139ab0
curusr: 0 curses 587a250c
cursor name: select text from view$ where rowid=:1
child pin: 50a5b650, child lock: 50a5a628,
parent lock: 50a5a844
xscflg: 20141466, parent handle: 4f348490, xscfl2: 400
nxt: 2.0x0000006c nxt: 1.0x000001d8
Cursor
frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bhp size: 52/560
bind 0: dty=11 mxl=16(16) mal=00
scl=00 pre=00 oacflg=18 oacfl2=1 size=16
offset=0
bfp=2013e9f4 bln=16 avl=16 flg=05
value=0000138C.0046.0004

当前SQL 语句:
select text from view$ where rowid=:1
绑定变量转换成:
:1 ~ bind 0 - ROWID (dty=11), value = 0000138C.0046.0004
因此我们得到下面的原始sql语句:
select text from view$ where rowid=0000138C.0046.0004

Example 2:

******************** Cursor Dump ************************
Current cursor: 11, pgadep: 1
Cursor Dump:
----------------------------------------
查找cursor 11:
----------------------------------------
Cursor 11 (202cb9f0): CURBOUND curiob: 202f8b04
curflg: dd curpar:
0 curusr: 0 curses 30047c7c
cursor name: SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME =
:b1 FOR UPDATE
child pin:
0, child lock: 300dc9b4, parent lock: 301730b8
xscflg: 1151421, parent handle: 3025b4dc
bind 0: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=01
No bind buffers allocated
----------------------------------------
当前SQL 语句:
SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :b1 FOR UPDATE
绑定变量:b1是VARCHAR2(32) 类型(dty=1, mxl=32), 但是在dump的那个时刻没有给变量赋值("No bind buffers allocated").

Example 3:

******************** Cursor Dump ************************
Current cursor: 2, pgadep: 0
Cursor Dump:
----------------------------------------
...
----------------------------------------
Cursor 2 (20140444): CURNULL curiob: 0
curflg: 44 curpar: 0 curusr: 0 curses 701dc94c
----------------------------------------
在这种情况下，当dump 的时候并没有sql被执行

Example 4:

******************** Cursor Dump ************************
Current cursor: 1, pgadep: 0
pgactx: ccf361c0
ctxcbk: 0 ctxqbc: 0 ctxrws: 0
Cursor Dump:
----------------------------------------
Cursor 1 (400d9478): CURBOUND curiob:
400e43d8
curflg: 4c curpar: 0 curusr: 0 curses d5348f80
cursor name: BEGIN myparser.convert('/tmp','workflow000_2.log',2);
END;
child pin: d14a4d70, child lock: d1589968, parent lock: d14c64a0
xscflg: 100064, parent handle: d083f1c0, xscfl2: 4040408

nxt: 1.0x000000a8
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
----------------------------------------
没有用到绑定变量，所以，sql语句就是PL/SQL块:
BEGIN myparser.convert('/tmp','workflow000_2.log',2); END;

第1页：从trace文件中提取sql语句包括两个步骤第2页：例子

关注我们