9 Replies Latest reply: May 3, 2010 10:19 AM by Oviwan RSS

    SQL Tracing

    Oviwan
      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
          user7983928
          Have u set 10046 trace event 12.???
          • 2. Re: SQL Tracing
            737979
            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
              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
                Charles Hooper
                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
                  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
                    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
                      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
                        Charles Hooper
                        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
                          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