Bind values missing from SQL trace
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 15 Aug 2007 14:48:18 -0500 (CDT)
Hey all,
In 10.1.0.5.0 on AIX, I'm trying to track down a percieved performance
issue. Based on the info from the analyst, I fire up a logon trigger in our
test DB that starts a level 12 10046 trace for this user. The analyst logs
on to the app, runs the query, logs off, and I have my 6 trace files waiting
for me in user_dump_dest.
Using grep, I quickly locate the longest-running statement, but when I view
the essential binds, I see this:
PARSE #18:c=0,e=1365,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=18455821260913
BINDS #18:
bind 0: dty=1 mxl=128(50) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800010
size=160 offset=0
bfp=11058efc0 bln=128 avl=50 flg=05
value=""
bind 1: dty=1 mxl=32(24) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800010
size=0 offset=128
bfp=11058f040 bln=32 avl=24 flg=01
value=""
EXEC #18:c=10000,e=10586,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=18455821271562
WAIT #18: nam='SQL*Net message to client' ela= 1 p1=675562835 p2=1 p3=0
The rest of the trace contains all the fun WAITs and FETCHs for this cursor.
So where's the values for the two binds that I need? From Chapter 5, Verse
82 of The Bible According to Cary, I see that the bind datatype (dty) is an
NVARCHAR2, the length (avl) is 25 (50 bytes), and there's no value. Of
course, no such rows exist in this table and attempts to duplicate this via
hard-coded SQL do not yield the same results.
Using MeatLink, Tahiti, and Google, I can't find any sort of bug where binds
are missing in 10gR1, other than a reference in the eluded-to Optimizing
Oracle Performance book that values can get truncated when the avl is larger
than the length of the text of the value. The only other WAG I had is that
I used 10046 instead of DBMS_MONITOR, and seeing as how guessing isn't a
good way to solve a problem, I tried this with DBMS_MONITOR, which produced
similar results.
Thoughts anyone???
TIA!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
