SQL trace facility and TKPROF let you accurately assess the efficiency of the SQL statements an application runs. For best results, use these tools with EXPLAIN PLAN rather
SQL Trace
The SQL trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:
- Parse, execute, and fetch counts
- CPU and elapsed times
- Physical reads and logical reads
- Number of rows processed
- Misses on the library cache
- Username under which each parse occurred
- Each commit and rollback
TKPROF
You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also:
Determine the execution plans of SQL statements.
Create a SQL script that stores the statistics in the database.
1. Set initialization parameters for trace file management.
2. Enable the SQL trace facility for the desired session, and run the application. This step produces a trace file containing statistics for the SQL statements issued by the application.
3. Run TKPROF to translate the trace file created in Step 2 into a readable output file. This step can optionally create a SQL script that can be used to store the statistics in a database.
4. Interpret the output file created in Step 3.
5. Optionally, run the SQL script produced in Step 3 to store the statistics in the database.
Before enabling the SQL trace facility:
1. Set initialization parameters for trace file management.
Check settings of the TIMED_STATISTICS, MAX_DUMP_FILE_SIZE, and USER_DUMP_DEST initialization parameters.
SQL> select name, value from v$parameter
2 where name in
3 ('timed_statistics','max_dump_file_size', 'user_dump_dest')
4 /
timed_statistics
TRUE
user_dump_dest
/u00/oracle/diag/rdbms/dvols08/dvols08/trace
max_dump_file_size
50M
select host_name from v$instance ; /* used to get the server name for the database */
2. Enable the SQL trace facility for the desired session, and run the application. This step produces a trace file containing statistics for the SQL statements issued by the application.
Enable the SQL trace facility for the session by using one of the following:
SQL Plus> The DBMS_SESSION.SET_SQL_TRACE procedure
SQL Plus > ALTER SESSION SET SQL_TRACE = true;
To disable the SQL trace facility for the session, enter:
SQL Plus > ALTER SESSION SET SQL_TRACE = false;
**The SQL trace facility is automatically disabled for the session when the application disconnects from Oracle.
3. Run TKPROF to translate the trace file created in Step 2 into a readable output file.
Formatting tkprof
tkprof <trace_file>.trc </home/my_traces/output_file>.out sys=no sort=exeela, fchela explain=<username/password>
Sample output from TKPROF is as follows:
Code: Select all
SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;
call count cpu elapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Parse 1 0.16 0.29 3 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.26 2 2 4 14
Misses in library cache during parse: 1
Parsing user id: (8) SCOTT
Rows Execution Plan
------- ---------------------------------------------------
14 MERGE JOIN
4 SORT JOIN
4 TABLE ACCESS (FULL) OF 'DEPT'
14 SORT JOIN
14 TABLE ACCESS (FULL) OF 'EMP'
The text of the SQL statement.
The SQL trace statistics in tabular form.
The number of library cache misses for the parsing and execution of the statement.
The user initially parsing the statement.
The execution plan generated by EXPLAIN PLAN.
TKPROF also provides a summary of user level statements and recursive SQL calls for the trace file.
4. Interpret the output file created in Step 3.
Tabular Statistics in TKPROF
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns. Each row corresponds to one of three steps of SQL statement processing. Statistics are identified by the value of the CALL column:
5. Optionally, run the SQL script produced in Step 3 to store the statistics in the database.
You might want to keep a history of the statistics generated by the SQL trace facility for an application, and compare them over time. TKPROF can generate a SQL script that creates a table and inserts rows of statistics into it. This script contains:
A CREATE TABLE statement that creates an output table named TKPROF_TABLE.
INSERT statements that add rows of statistics, one for each traced SQL statement, to the TKPROF_TABLE.
After running TKPROF, you can run this script to store the statistics in the database.