DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Turning off Tracing... How?

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    Turning off Tracing... How?

    I'm trying to get the values of bind variables. So I found that by tracing the session using the following syntax, I can produce a trace that will also tell me the values of the bind variables in the sql:

    EXECUTE sys.dbms_system.set_ev ('||SID||','||SERIAL#||',10046,4,'''')

    (Source: http://www.dba-oracle.com/t_trace_bi...ble_values.htm)

    My question is, how do I turn off the tracing when I'm done? I'm tracing a session for the application. I'd prefer not to have to restart the application/session.

  2. #2
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Nevermind...
    I found my answer...
    To turn it off, just set the level back to 0 ("zero"). So for the same example:

    EXECUTE sys.dbms_system.set_ev ('||SID||','||SERIAL#||',10046,0,'''')

    (Changing the "4" to "0")

  3. #3
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Ok.. next question...
    I've been able to trace the session using the above method.
    I run the trace file through tkprof.
    When I look through the out of tkprof from the trace file, I see some SQL text "where" clause show actuallyt values instead of bind variables. But I still other sql text displaying the bind variable instead of the values.

    Can someone explain?

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    You need to read the raw trace file to get the bind variable values. Search for you SQL, the bind variable values ought to be just below it (check you are looking at the right cursor number).
    Assistance is Futile...

  5. #5
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    An consider buying "Optimizing Oracle for Performance" by Cary Millsap (O'Reilly).
    Assistance is Futile...

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    if they are showing values then the app isnt using bind variables

  7. #7
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    OH.. are so saying that the output of TKPROF will always show only bind variables, if bind variables were used. Any portion of the out from TKPROK that show actual values, then no bind variables were ever used for those sql statements.

    And as said before, the only place you will find the values of bind variables used in a sql statement is in the raw trace output.

    Please confirm?

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    yes, correct

  9. #9
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Thanks Dave!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width