Search This Blog

Wednesday, April 30, 2008

SQL Trace and TKPROF

Understanding SQL Trace
Times when program is performing poorly, creating and examining a trace file is one of the best way to find what is causing the problem for poor performance.
Following is the list of some of the SQL Trace statictics that are generated in the trace file.

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Commits and Rollback

There are several ways by which you can turn on/off SQL Trace

Turn on
DBMS_SESSION.SET_SQL_TRACE(TRUE);
ALTER SESSION SET SQL_TRACE = TRUE;


Turn off
DBMS_SESSION.SET_SQL_TRACE(FALSE);
ALTER SESSION SET SQL_TRACE = FALSE;

Click here to set SQL Trace on a concurrent program.
The trace file is created in the udump directory.

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.

1 Comment:

sap support pack said...

This post describe SQL Trace and TKPROF. In first portion SQL trace is explained and in the later section TKPROF is given which is a program to format the content of the trace file. TKPROF is new for me but easy. Thanks for the post.

Copyright (c) All rights reserved. Presented by Suresh Vaishya