What is tkprof
tkprof is one of the most helpful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.
· Compare the number of parses to number of executions.
· Search for SQL statements that do not use bind variables
· Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption.
1. Find the User Dump Directory
SQL> select value from v$parameter where name = 'user_dump_dest';
2. Get the SID and SERIAL# for the process you want to trace.
SQL> select sid, serial# from sys.v_$session
3. Enable tracing for your selected process:
SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, true);
4. Ask user to run just the necessary to demonstrate his problem.
5. Disable tracing for your selected process:
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);
SQL> ALTER SYSTEM SET TIMED_STATISTICS = FALSE;
6. Look for trace file in USER_DUMP_DEST
$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc
7. Run TKPROF to analyse trace output
$ tkprof ora_9294.trc OUTPUT=ora_9294.lst EXPLAIN=SCHEMA_NAME/PASSWD
8. View/print output