This discussion is archived
9 Replies Latest reply: May 3, 2010 8:19 AM by Oviwan RSS

SQL Tracing

Oviwan Pro
Currently Being Moderated
Hi Folks

Oracle 11.1.0.7

Is it possible to trace the fetched value of a select statement?

e.g. instead to see the count of fetched values (here r=47), I would like to see all of the 47 rows with their values.

FETCH #9:c=1000,e=0,p=0,cr=0,cu=0,mis=0,r=47,dep=0,og=1,plh=1552592935,tim=1271851659528802


Is there a way to do this?

Backgroud: the application man means that the db returns a value which shouldn't be returned, and if I execute the sql manually the specific value is not returned. so I want to trace the application's session.

Thanks

Regards oviwan
  • 1. Re: SQL Tracing
    768575 Newbie
    Currently Being Moderated
    Have u set 10046 trace event 12.???
  • 2. Re: SQL Tracing
    737979 Explorer
    Currently Being Moderated
    I think you are asking to trace the bind variable values used in executing the statement?
    If so, look at DBMS_MONITOR.SESSION_TRACE_ENABLE.
    If your session and serial# are 120 and 23456 then enable trace like this:
    begin
      DBMS_MONITOR.SESSION_TRACE_ENABLE(120,23456,FALSE,TRUE);
    end;
    /
  • 3. Re: SQL Tracing
    Oviwan Pro
    Currently Being Moderated
    no, I'm looking for the data which are transfered from the database to the application. I know the SESSION_TRACE_ENABLE procedure, but there you can't see the values of each record which is fetched as far as I know.

    I'll try this with the "10046 trace event 12" event tomorrow
  • 4. Re: SQL Tracing
    CharlesHooper Expert
    Currently Being Moderated
    Oviwan wrote:
    no, I'm looking for the data which are transfered from the database to the application. I know the SESSION_TRACE_ENABLE procedure, but there you can't see the values of each record which is fetched as far as I know.

    I'll try this with the "10046 trace event 12" event tomorrow
    A 10046 trace at level 12 (or at level 8 or 4) will not give you the information you want to see. Level 4 writes the bind variable definitions and values to a trace file, level 8 writes the wait events, and level 12 writes the bind variables and wait events. A 10046 trace will not write the data returned by the database to a log file.

    One option that might work for you is to use a packet capture utility, such as Wireshark. Wireshark has a "follow stream" option that will re-combine the multiple TCP packets into a conversation that shows the SQL statement submitted by the client and the raw data returned by Oracle. Another option is to enable a client-side SQL*Net trace, see Metalink Doc IDs 779226.1, 219968.1, and 395525.1. If the client computer is running on Windows, take a look at Metalink Doc ID 216912.1 to enable tracing in the various communication stacks.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 5. Re: SQL Tracing
    758358 Pro
    Currently Being Moderated
    Backgroud: the application man means that the db returns a value which shouldn't be returned, and if I execute the sql manually the specific value is not returned. so I want to trace the application's session.
    Rather than trying to trace the data returned would it be easier to look at this from a different angle, I presume the reason you identified the "bad" value is due it causing an error of some sort? Could you look at trapping this error instead somewhere ... maybe with errorstack tracing?

    If it isn't causing an error, what is it about the value that is wrong and how did you identify it? With SQL Trace level 12 / dbms_monitor you get the bind variables used when executing the statement, if you are saying that when running the same SQL with the same bind variables you get a different answer - when the underlying data hasnt changed that sounds like a bug.

    Thanks
    Paul
  • 6. Re: SQL Tracing
    737979 Explorer
    Currently Being Moderated
    packet sniffing...
    Clever - That's what I like about this forum - you get some real clever folks with creative solutions.
    Tracing with Wireshark (AKA ethereal) is a great learning exercise.
    If the SQLNet traffic has been encrypted you won't be able to make sense of the packet traces - but you might be able to turn up sqlnet tracing (level admin / 16) and mine the verbose trace file for the values.
    Good luck.
  • 7. Re: SQL Tracing
    Oviwan Pro
    Currently Being Moderated
    thanks for reply.

    yes wireshark was also in mine mind, but then I thought that oracle would provide any utility, so will try the client side tracing using sql*net.

    it's a batchjob which executes a select and fetches the data, but there is one row which should not be returned, and we don't know where this row come from. when I execute this sql with the same bind values, this specific row is not returned. this batchjob runs on all batchservers correctly except on one (but same setup as all the others, they say :)). so we are also not sure whether it's the batchserver or the database fault...

    I'll update this thread as soon as I know more.
  • 8. Re: SQL Tracing
    CharlesHooper Expert
    Currently Being Moderated
    Oviwan wrote:
    thanks for reply.

    yes wireshark was also in mine mind, but then I thought that oracle would provide any utility, so will try the client side tracing using sql*net.

    it's a batchjob which executes a select and fetches the data, but there is one row which should not be returned, and we don't know where this row come from. when I execute this sql with the same bind values, this specific row is not returned. this batchjob runs on all batchservers correctly except on one (but same setup as all the others, they say :)). so we are also not sure whether it's the batchserver or the database fault...

    I'll update this thread as soon as I know more.
    If that is the case, double-check the bind variable data type definitions. There is a chance that the data type definition used by the batch job is different from what you are using when you execute the SQL statement in isolation. It might be a problem with an implicit data type conversion, possibly related to the NLS settings.

    If you create a 10046 trace file at either level 4 or 12 (so that the bind variable definitions are written to the file) and you have a Windows client PC, you can use a VBS script that I wrote which translates the 10046 trace file into a VBS program script - there is a chance that this might help you determine the one row is being returned. You will find the script here:
    http://hoopercharles.wordpress.com/2010/03/12/from-a-vbs-script-to-a-10046-trace-and-back-into-a-vbs-script/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 9. Re: SQL Tracing
    Oviwan Pro
    Currently Being Moderated
    I traced now the session with the following sqlnet settings on the client machine:
    TRACE_LEVEL_CLIENT = SUPPORT #off 
    TRACE_UNIQUE_CLIENT = on 
    TRACE_DIRECTORY_CLIENT = C:\temp\sqlnet_trace 
    TRACE_FILE_CLIENT = SQLNetTrace 
    TRACE_TIMESTAMP_CLIENT = ON 
    DIAG_ADR_ENABLED=off #11g Client
    Then I moved the trace file (~150MB) on a linux box to filter out the packetdump's which are in hex:
    grep nsbasic_ sqlnettrace_3016.trc | awk '{print $5 $6 $7 $8 $9 $10 $11 $12}' | grep '^[0-9A-F][0-9A-F]\{15\}' | xxd -r -p > /tmp/fetches.out
    then I was able to open the file with a hexeditor and could see which records were fetched. the specific record was not in the batch. There were fetched 48 records which fits on the sql trace r=47 and r=1.

    Thanks for your help

    Regards oviwan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points