Total number of rows processed by the SQL statement. There are couple of ways you can do this:. Number of current mode block reads during fetch. The execution plan is determined by connecting to the database using the userid, password, and net service name provided. This indicates the level of recursion at which the SQL statement was issued. TKPROF on the other hand takes that trace file and aggregates all of the low level details in an easy to read report.

Uploader: Yonris
Date Added: 5 December 2013
File Size: 67.84 Mb
Operating Systems: Windows NT/2000/XP/2003/2003/7/8/10 MacOS 10/X
Downloads: 48552
Price: Free* [*Free Regsitration Required]

Oracle provides multiple ways to actually generate the trace file. Number of physical reads from disk during parse.

Oracle Utilities Pocket Reference by Sanjay Mishra

Therefore, they include any resources tkpfof within a trigger, along with the resources used by any other recursive SQL such as that used in space allocation. There could be multiple poorly performing SQL statements that are tkprof back and forth making it difficult to tell which one is the culprit.

This information lets you easily locate those statements that are using the greatest resource. You might want to keep a history of the statistics generated by the SQL Trace facility for an application, and compare them over time.

In particular, be careful when interpreting the results from simple queries that execute very quickly. I always like to tkptof on the actual database node and check how much damage this sessions is really doing. Specifies that the execution tkprfo for each SQL statement in the tracefile be written to tkprof output file. You can run the TKPROF program to format the contents of the trace file and place tkprof output into a readable output file. Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file.


Total number tjprof tkprof blocks physically read from the tkprof on disk for all parse, execute, or fetch calls. Portions tkpeof been edited out for the sake of brevity.

Use tkprof to format the tracefile generated by SQL Trace.

Using SQL Trace and TKPROF

This is from the book Oracle Utilities: Parse, execute, and fetch counts Tkprof and elapsed times Physical reads and logical reads Number tkprof rows processed Misses on the library cache Username under which each parse occurred Each commit and rollback You tkprof enable the SQL Trace facility for a session or for an instance. Which one is commonly used sorting parameter? Oracle uses this column value to keep track of the cursor to which each SQL tkprof was assigned.

The user dump destination can also be specified for a single session using the alter session command. The command to enable timed statistics is: If you find that the trace output is truncated, then increase the value of this parameter before generating another trace file. All legitimate Oracle experts publish their Oracle qualifications.

After the instance has been restarted with the updated initialization parameter file, SQL Trace tkprof enabled for the instance and statistics are collected for all sessions. Lists only the first integer sorted SQL statements from the output tkpfof.

oracle – How to use TKPROF utility along with syntax and parameters? – Stack Overflow

These statements should be modified to use bind variables. It might be difficult to match trace files back to the tkprof or process that created them.


tkprof Statistics are identified by the value of the CALL column. The resources reported for a statement include those for all of the SQL issued while the statement was being processed. Post Your Answer Discard By clicking “Post Your Answer”, you acknowledge that you have read our updated terms of servicetkprof policy and cookie policyand that your continued use of the website is subject to these policies. You can then trace each file back to the process that tkprof it.

Anyone considering ttkprof the services of an Oracle support expert should tkprof investigate their credentials and experience, and not tkprof on advertisements and self-proclaimed expertise. After the SQL Trace is turned on — Oracle will write out all the waits and calls this session is doing to a trace file on the database server. These anomalies arise because the clock tick of 10 milliseconds is too long relative to the time taken to execute and fetch the data.

Each row corresponds to one of three steps of Tjprof statement processing. This value is not exactly the same as the time tkprof statistics were collected by the SQL Trace facility.