There is a newer version of this article here.
The TKPROF program converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. To get the most out of the utility you must enable timed statistics by setting the init.ora parameter or performing the following command.
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
If a suitable plan table is not present one can be created by doing the fooling as the SYS user.
@?/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE; GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;
With this done we can trace a statement.
ALTER SESSION SET SQL_TRACE = TRUE; SELECT COUNT(*) FROM dual; ALTER SESSION SET SQL_TRACE = FALSE;
The resulting trace file will be located in the USER_DUMP_DEST
directory. This can then be interpreted using
TKPROF at the commmand prompt as follows.
TKPROF <trace-file> <output-file> explain=user/password@service table=sys.plan_table
The resulting output file contains the following type of information for all SQL statements processed, including the ALTER SESSION commands.
******************************************************************************** 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 ******************************************************************************** SELECT COUNT(*) FROM dual call count cpu elapsed disk query current rows ------- ----- ----- ------- ------- ------- ------- ------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 1 4 1 ------- ----- ----- ------- ------- ------- ------- ------- total 4 0.02 0.02 0 1 4 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 121 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 1 TABLE ACCESS FULL DUAL
Things to look out for in the trace include:
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/8i/tkprof-and-oracle-trace