RE: Bind values missing from SQL trace

Perhaps there is an app problem, and they aren't sending any values in the bind variables. That would account for the performance problem if they aren't qualifying on anything.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Wednesday, August 15, 2007 2:48 PM To: oracle-l_at_freelists.org
Subject: Bind values missing from SQL trace

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=1845582127156
2
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

--
http://www.freelists.org/webpage/oracle-l



------------------------------------------------------------------------------
NOTICE:  This electronic mail message and any attached files are confidential.  The information is exclusively for the use of the individual or entity intended as the recipient.  If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited.  If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

==============================================================================

--
http://www.freelists.org/webpage/oracle-l