Tracing your Oracle session


How to trace Oracle session using SQL and PL/SQL tools?

Many times developers and/or DBA’s want to trace their own and/or other session’s  SQL and PL/SQL activities.
There are many possibilities to solve this problem especially, if you  want to trace your  session.
Very easy to identify which is your session:

   

Let us suppose that  your session is identified by SID=403 and SERIAL#=1242.
Your Operating system process identifier (SPID) is 8564
You can find the directory of the trace file which will be created later:

Modify your session:
ALTER SESSION SET SQL_TRACE=TRUE;

ALTER SESSION SET  STATISTICS_LEVEL=ALL;

Execute the following query:

Under the trace directory You will find the following file: orcl_ora_8564.trc where 8564 is your SPID.
This file contains very detailed information.
If You need a more readable text file, please run the tkprof utility in  command  line
tkprof orcl_ora_8564.trc hr_sqlplus.txt

Detail from the text file:

Executing  a suitable PL/SQL stored procedure, called  SQLID:

You want to find your SELECT statement’s execution plan with plan statistics (due to statistics level is ALL!)
EXEC SQLID(‘MAX(SUM(salary))’)

About these ads

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


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: