Oracle SQL Trace and TKPROF


SQL Trace and TKPROF can be used in addition to EXPLAIN PLAN in order to assess the efficiency of SQL statements. SQL Trace generates statistics for parse, execute and fetching counts, CPU usage, physical and logical reads, rows processed, library cache misses, commits and rollbacks plus user name SQL statement executed under. All these statisitics are placed into a trace output file, either by session or for the entire instance. TKPROF is a tool used to format the output trace files into a desired and more easily interpretable format than that of the trace files.

SQL Trace

Three parameters affect tracing of SQL statements, TIMED_STATISTICS (elapsed times for specific functions such as CPU usage), MAX_DUMP_FILE_SIZE (maximum size of trace file in bytes) and USER_DUMP_DEST (path where trace files are written to).

SQL Trace can be enabled at the instance by setting the SQL_TRACE parameter to TRUE and since it is a modifiable parameter it can be set at the session level using the ALTER SESSION SET SQL_TRACE=TRUE command.

See How to Find Problem Queries.

TKPROF

TKPROF will allow the formatting of trace files into a readable format. TKPROF will accept an input trace file and output a formatted output file. Execution plans can also be generated using SQL Trace files. Note the TKPROF will not contain commit and rollback command execution records.

TKPROF input filename output filename 
	[SORT = [sort option | (sort option, ...)]]
	[PRINT = n]
	[AGGREGATE = [YES | NO]]
	[INSERT = trace file statisitics creation script filename]
	[SYS = [YES | NO]]
	[[TABLE = schema.table] EXPLAIN = username/password]
	[RECORD = SQL code for events replay filename]