Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Bind values missing from SQL trace

Bind values missing from SQL trace

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Wed, 15 Aug 2007 14:48:18 -0500 (CDT)
Message-ID: <41364.12.17.117.251.1187207298.squirrel@12.17.117.251>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 15 2007 - 14:48:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US