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

Thread: How to Use and Understand the TKPROF utility

  1. #1
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658
    Hi, 20th May 2001 15:07 hrs chennai

    I am going to explain in detail the various issues involved in using the TKPROF utility.

    What is recursive,Non-Recursive calls,Various options used while using TKPROF utility,Understanding the TKPROF generated output file etc.

    What is the use of TKPROF utility
    --------------------------------------

    The TKPROF is a utility used to convert the Oracle trace file generated during SQL Trace in to a readable format.

    TKPROF is a program that you invoke at the operating system command prompt in order to reformat the trace file into a format that is much easier to comprehend. Each SQL statement is displayed in the report, along with counts of how many times it was parsed, executed, and fetched. CPU time, elapsed time, logical reads, physical reads, and rows processed are also reported, along with information about recursion level and misses in the library cache. TKPROF can also optionally include the execution plan for each SQL statement in the report, along with counts of how many rows were processed at each step of the execution plan.

    The SQL statements can be listed in a TKPROF report in the order of how much resource they used, if desired. Also, recursive SQL statements issued by the SYS user to manage the data dictionary can be included or excluded, and TKPROF can write SQL statements from the traced session into a spool file.

    what Levels the SQL Trace file for TKPROF would have been generated
    ---------------------------------------------------------------------

    Either it would have been done at Instance level by setting SQL_TRACE=TRUE or setting at session level the same.At session level it can be set back to false and at instance level bouncing db back twice At initial and at final after finishing trace.

    Where is SQL Trace file located used in the TKPROF
    -----------------------------------------------------------

    This is Located in the UDUMP directory in oracle database file structure or the path specified in the USER_DUMP_DEST

    What should be take care before Using TKPROF utility
    --------------------------------------------------------------

    Without fail try to close the SQL_TRACE=False before converting the trace file to redable format.Other wise it may not reflect a proper result.

    TIMED_STATISTICS=True
    -------------------------------

    Set this parameter in init.ra or for a particular session level if you require the CPU time calculations for each phase.

    How to find the respective trace file from UDUMP directory
    -------------------------------------------------------------------

    The latest trace file could be found by the latest time of creation of the file.Dont output the old files.Better clear the UDUMP directory before using the SQL Trace.

    How to start of with TKPROF utility
    ---------------------------------------

    Simply type from command prompt TKPROF and the options as mentioned below.

    A general Syntax of TKPROF
    ---------------------------------

    TKPROF trace_file output_file
    [SORT = parameter]
    [PRINT = number]
    [EXPLAIN = username/password]
    [INSERT = stat_file_name]
    [SYS = yes/no]
    [AGGREGATE=[Y|N]]
    [RECORD = sql_file_name]
    [TABLE = schema.table_name]

    A sample syntax for formating a trace file using TKPROF
    -----------------------------------------------------------------

    >tkprof explain=user_name/pwd

    Under Standing various Options used in TKPROF Utility
    --------------------------------------------------------------

    Tracefile==>The name of the Trace output file

    Outputfile==>The name of the formatted file

    Sort=option==>The order in which to sort the statements

    Print=n==>Print the first n statements

    Explain=Username/pwd==>Run the Explain Plan in the specified user name

    Insert=filename==>Generate INSERT statements

    Sys=no==>Ignore recursive SQL statements run as sys user.

    Aggregate=[Y|N]==>If specified as NO then TKPROF does not aggregate multiple users of the same SQL text

    Record=filename==>Record statements found int he trace file

    Table=schema.tablename==>Putting execution plan in to specified table(rather than the default PLAN_TABLE).

    Under Standing trace file Outputs returned by TKPROF Utility
    ---------------------------------------------------------------------

    After all the sql statement you may find the following values

    Call==>PARSE,EXCUTE,FETCH

    COUNT==>How many times the statement was parsed or executed and the no of fetch calls issued for the part stat.

    CPU==>Process time for each phase in seconds. if the stat was found in the shared pool it is 0 for the parse phase.

    ELAPSED==>This is not much useful since other process also affect ellapsed time.

    QUERY==>Logical Buffers retrieved for Consistent read normally for a select stat.

    CURRENT==>Logical buffers retry in current mode.

    ROWS==>Rows processed by the outer stat.Select stat this is shown for the fetch phase and for DML stat it is shown in the execute phase.

    DISK==>Phy data blocks read from the DB files. This stst may be very low if the data was buffered.

    Libr cache misses==>this states that no of times the stat was not found in the Lib Cache in the parse and execute phase.if there was no miss then this statistcis wont appear.

    User==>Id of the last user to parse the statement.

    Execution Plan==> when you specify the EXPLAIN parameter with TKPROF it then works out the access path for each sql statement traced and included in the output.

    Optimizer Hint==>used to indicate the Optim hint which was used during the execution of the stat.If there was no hint it will show optimizer mode was used.

    Recursive SQL==>Sometimes in order to execute a SQL statement issued by a user, Oracle must issue additional
    statements. Such statements are called recursive calls or recursive SQL statements.

    For example, if you insert a row into a table that does not have enough space to hold that row, Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.

    If recursive calls occur while the SQL trace facility is enabled,
    TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file. You can suppress the listing of recursive calls
    in the output file by setting the SYS statement-line parameter to NO. The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for
    the SQL statement that caused the recursive call. So when you are calculating the total resources required to process a SQL statement, you should consider the statistics for that statement as well as those for recursive calls caused by that statement.

    A Question for a Practice
    -----------------------------
    When TKPROFING, I set the argument sys=n so that system sql statement statisticts would not be shown.

    I can still see that some statements were parsed anumber of times=, 9/10 times - do you know why ?

    Solution
    ----------
    http://www.dbasupport.com/forums/sho...threadid=10162

    Note :Subject to constant Upgradation of this thread.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  2. #2
    Join Date
    Jan 2003
    Posts
    1

    restarting an Oracle trace

    Nice explanation of TKProf. This is related, as I need the raw trace files to use with TKProf...

    I have no problem starting the first trace session using: DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, Serial#, TRUE);

    When I stop the trace that is fine as well.

    The problem is if I delete the trace files, then try to re-start the trace, nothing happens. I have to kill the connection, and then re-start the trace. Does anyone know why? This is on Unix. I would expect Oracle to find the files not there and then re-create them.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    But the file is still there, just the inode is gone.
    Jeff Hunter

  4. #4
    Join Date
    Jun 2003
    Location
    India
    Posts
    2

    Re: restarting an Oracle trace

    Originally posted by jwhite
    Nice explanation of TKProf. This is related, as I need the raw trace files to use with TKProf...

    I have no problem starting the first trace session using: DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, Serial#, TRUE);

    When I stop the trace that is fine as well.

    The problem is if I delete the trace files, then try to re-start the trace, nothing happens. I have to kill the connection, and then re-start the trace. Does anyone know why? This is on Unix. I would expect Oracle to find the files not there and then re-create them.
    Hello friends
    Shreyas here. All the steps related are really upto the mark.
    No issues. But some suggestions to add.
    We can also know which is the trace file by joining v$session and
    v$process which will give you the spid. So this spid.trc would be the
    trace file for your session.
    I more thing I would like to mention .
    Do not try explain= with your trace file. Because maybe the explain plan would show you a different execution path rather than it really
    took which you will see in the trace file.
    Just look closely towards the end of the trace file if you are also
    considering the explain= parameter in your output file.
    The difference is noticeable.
    You can also find whether a query is using the BIND variable
    which could be a very important factor related to the performance of
    that query or database.
    Check for the wait events also.
    The XCENTD would also show you some more related statistics
    Well maybe this part could be useful. Correct me if I am wrong.
    Thanks and Regards,
    Shreyas.

  5. #5
    Join Date
    Nov 2005
    Posts
    1
    Yeah, similarly if you move/rename the trc file to a new name (when I tried to back it up), Oracle did not create a new trc file but just used/appended to the existing file (with a new name).

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