Oracle: Using Oracle Trace from code without “tkprof” – The basics

Update: Although it is still relevant today, this was originally written in Aug 2012.

A lot of times, in your PL/SQL application, you might run into an error but do not know what statement caused the error. The program might lack any form of outputting (to UTL folder, log tables or otherwise).

The simplest way to find the exact problem is to run an Oracle trace on the session. By adding a couple of lines around the entry level procedure, you could easily locate the problem statement and what led to it. Tracing can also help you find a lot of additional information about performance etc.

The process is as below and the comments explain each statement

DECLARE

    v_Count NUMBER;

BEGIN

    v_Count := 0;

    --The string provided here is appended to the trace file name generated by Oracle
    EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''Jana'' '; 

    --The string provided here becomes part of v$session view for easy identification of custom trace sessions
    --  SELECT client_identifier FROM v$session
    DBMS_SESSION.SET_IDENTIFIER (client_id => 'Janas_Traced_Session');

    --Turn on tracing
    DBMS_SESSION.SET_SQL_TRACE (sql_trace => true);

    --Do whatever you need to do that needs to be traced..
    --i.e., your procedure call goes here
    SELECT COUNT(1)
    INTO v_Count
    FROM pods_Dataset;

    --Turn off tracing
    DBMS_SESSION.SET_SQL_TRACE (sql_trace => false);

END;

Once the trace file is generated with a custom name, the DBA’s can easily locate it and send it to you and you can open it in SQL Developer to get a fantastic view loaded with lots of information. You can download and install SQL Developer for free

Once installed, simply launch SQL Developer and open the trace file using File -> Open.

(No more tkprof’ing unless you want to!!)

Here are some sample screen shots:

clip_image002

clip_image002[4]

clip_image002[6]

clip_image002[8]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s