TKprof Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: TKprof Question

  1. #1
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110

    TKprof Question

    Hi all,
    Sometimes I'm able to generate the text file with the sql and stats but sometimes it produces nothing. What I'm I doing wrong here.

    Thanks

    -I set the parameters here as DBA

    SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,true);

    PL/SQL procedure successfully completed.

    ----I executed a sql from a client session
    ----I turn off tracing
    SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,false);

    PL/SQL procedure successfully completed.


    Now Tkprofing...


    tkprof tttt09_ora_7412.trc trace.txt explain=system/xxxxx table=sys.plan_table sys=no waits=yes

    open output file

    cat trace.txt

    ----results below. Why is the sql qnd stats not captured?

    TKPROF: Release 9.2.0.5.0 - Production on Wed Mar 25 12:49:25 2009

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Trace file: tttt09_ora_7412.trc
    Sort options: default

    ********************************************************************************
    count = number of times OCI procedure was executed
    cpu = cpu time in seconds executing
    elapsed = elapsed time in seconds executing
    disk = number of physical reads of buffers from disk
    query = number of buffers gotten for consistent read
    current = number of buffers gotten in current mode (usually for update)
    rows = number of rows processed by the fetch or execute call
    0 statements EXPLAINed in this session.
    ********************************************************************************
    Trace file: tttt09_ora_7412.trc
    Trace file compatibility: 9.02.00
    Sort options: default

    1 session in tracefile.
    0 user SQL statements in trace file.
    0 internal SQL statements in trace file.
    0 SQL statements in trace file.
    0 unique SQL statements in trace file.
    511 lines in trace file.
    Looking for the greatest evil in the world? Look in the mirror.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool Invisible client?

    Are you sure the query you want to trace is being executed in the session you are tracing? Does not seem it is.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    no sql is executed...
    include one parameter in tkprof i.e record=filename to see non-recursive sql executed by the session..
    lucky

  4. #4
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    Quote Originally Posted by LKBrwn_DBA View Post
    Are you sure the query you want to trace is being executed in the session you are tracing? Does not seem it is.
    It should......

    this is what I do

    get the sid /serial# of the connected user from v$session

    as DBA set parameters

    SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,true); ---params from above

    PL/SQL procedure successfully completed.

    executed a sql from the session/sid/serial# above

    then turn off tracing

    SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,false);

    PL/SQL procedure successfully completed.

    Is there something missing? when I then do the TKprof?
    Looking for the greatest evil in the world? Look in the mirror.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool Session/Trace file?

    Are you sure it's the only session for that user?

    Are you selecting the correct trace file?

    From what utility/application is the user executing his queries?

    Make user logoff/logon before/after trace.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Aug 2002
    Posts
    56
    Is this session coming to the database through a job? I have noticed that sometimes when this happened to me, it turned out to be the j001 process. Check the OS process by joining v$session to v$process view.
    When in doubt ...go to the basics!!

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