Using SQL_Trace and TKPROF

Using SQL_Trace and TKPROF

SQL_TRACE, TIMED_STATISTICS, and TKPROF are some of many tools that Oracle provides to know where the performance issue in a system lies.

In a nutshell, SQL_TRACE enables the logging of all the SQL your application performs, performance statistics regarding the execution of that SQL, and the query plans your SQL actually used.

Check the Environment

Before tracing using SQL_TRACE, the environment must first be configured by performing the following steps:

Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:

ALTER SESSION SET TIMED_STATISTICS = TRUE;
OR
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command. Make sure that enough space exists on the device to support the number of trace files that you expect to generate.

You can ran a query to get your SPID, (server process ID) – this is very useful in identifying the trace file. Finally you can get the trace file in the directory on the database server specified by the USER_DUMP_DEST init.ora parameter.
You can retrieve the value of this parameter online by querying the V$PARAMETER view or by issuing show parameter dump at sql prompt.

The query is as follows:

select a.spid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
/

Turn Tracing On

The next step in the process is to enable tracing. By default, tracing is disabled due to the burden it places on the database.

SQL_TRACE may also be enabled at the system or session level. It generates so much output and is such a performance impact that you will almost always selectively enable it.

You will rarely, if ever, enable it for the system in the init.ora file. SQL_TRACE has two settings as well, TRUE and FALSE. If set to TRUE, it will generate trace files to the directory specified by the init.ora parameter USER_DUMP_DEST, when using dedicated servers to connect to Oracle and BACKGROUND_DUMP_DEST when using a multithreaded server (MTS) connection. I would recommend never attempting to use SQL_TRACE with MTS however, as the output from your sessions queries will be written to many various trace files, as your session migrates from shared server to shared server.
Under MTS, interpreting SQL_TRACE results is nearly impossible. Another important init.ora parameter is MAX_DUMP_FILE_SIZE. This limits the maximum size of a trace file Oracle will generate. If you discover that your trace files are truncated, you will increase this setting. It may be changed via an alter system or session command.
MAX_DUMP_FILE_SIZE may be specified in one of three ways:

1. A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in
operating system blocks.
2. A number followed by a K or M suffix specifies the file size in kilobytes or Megabytes.
3. The string UNLIMITED. This means that there is no upper limit on trace file size.

Thus, dump files can be as large as the operating system permits.
I do not recommend UNLIMITED – it is far too easy to completely fill up a file system in this manner.

What are the various ways to enable SQL_TRACE? There are quite a few, but the following are some of them:

• ALTER SESSION SET SQL_TRACE=TRUE|FALSE – Executing this SQL will enable the default mode of SQL_TRACE in the current session. This is most useful in an interactive environment such as SQL*PLUS or embedded in an application so that the application, may turn SQL_TRACE on and off at will. It is a nice feature in all applications, as it would allow you to turn SQL_TRACE on and off for the application via a command line switch, menu selection, parameter, and so on, easily.
• SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION – This packaged procedure allows us to set SQL_TRACE on and off for any existing session in the database. All we need to do is identify the SID and SERIAL# for the session, this is available in the dynamic performance view V$SESSION.
• ALTER SESSION SET EVENTS – We can set an event to enable tracing with more information than is normally available via ALTER SESSION SET SQL_TRACE=TRUE. The SET EVENTS approach is not documented or supported by Oracle Corporation, however its existence is generally available knowledge (go to http://www.google.com/ and search for alter session set events 10046 to see the many web sites that document this feature). Using this event we can not only get everything that SQL_TRACE tells us but we can also see the values of bind variables used by our SQL as well as the wait events (what slowed us down) for our SQL as well.

alter session set events '10046 trace name context forever, level <N>';
alter session set events '10046 trace name context off';
Where N is one of the following values:

1. N=1 – Enable the standard SQL_TRACE facility. This is no different than setting SQL_TRACE=true.
2. N=4 – Enable standard SQL_TRACE but also capture bind variable values in the trace file.
3. N=8 – Enable standard SQL_TRACE but also capture wait events at the query level into the trace file.
4. N=12 – Enable standard SQL_TRACE and include both bind variables and waits.

If you have an application provided by a third party or you have existing applications that are not SQL_TRACE-enabled, what can you do to trace them? There are two approaches that you can take. One approach, if the application is a client-server application and stays connected to the database, is to have the session you want to trace start up the application and log into the database. Then, by querying V$SESSION, you would determine that session’s SID and SERIAL#. Now you can call SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to enable tracing in that single session. Today however, many applications are web-based and this trick does not work as well. The sessions are very short and they come and go frequently. What we need is an ability to set SQL_TRACE on for a 'user' – whenever this user is in the database; we need to have set SQL_TRACE on for them. Fortunately, we can do that via the LOGON DDL Trigger in the database. For example, a trigger (database event triggers such AFTER LOGON trigger) is:

create or replace trigger logon_trigger
after logon on database
begin
if ( user = 'YOUR_USER_NAME_HERE' ) then
execute immediate 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'''; [Note: This statement is in a single line.]
end if;
end;
/

The trigger can be enabled/disabled as per requirement. You should provide the mechanism of disabling the tracing when the user session disconnects.

ILLUSTRATIVE EXAMPLE

alter session set timed_statistics=true;

show parameter dump

alter session set events '10046 trace name context forever, level <N>';

YOUR_QUERIE(s) HERE …………..

alter session set events '10046 trace name context off';

disconn

exit

--- Go to the user_dump_dest directory or wherever your trace file is. If possible you can read the trace file directly or can use the TKPROF utility to get the trace file output in a human readable format.

Shell_prompt> tkprof input_file_name.trc output_file_name.txt [options]

The sid (Session ID) and serial# can be obtained from the v$session view. Once tracing with Oracle is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific.

• Enable Oracle tracing only on those sessions that are having problems.
• Explain Plan is not as useful when used in conjunction with TKPROF since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.
• When tracing a session, remember that nothing in v$session indicates that a session is being traced. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.
• TKPROF does not control the contents of a trace file, it simply formats them. Oracle provides multiple ways to actually generate the trace file. TKPROF is valuable for detailed trace file analysis. For those scenarios where a simpler tracing mechanism with instant feedback is required, the autotrace utility should be used.