Trace a SQL Session : Query Slowdown - System Performance - Oracle Monitoring

Resolving The Problem

Example : You want to trace a session for user 'user01' who is performing the operation 'ASN Entry'.

--Determine the terminal of the user:
tty
/dev/pty/ttyu3

--Request that the user "user01" log in to the system.
--Identify the Yantra process ( WMSYantra invokes wh960.exe ).
-- This will create 2 Unix processes, one for the application exe and second for the Oracle Shadow process ( oracleSID ):
ps -ef | grep user01 | grep -v grep

(For version 6.0 WMSYantra processes, look for wh960.)
A display similar to the following will be shown:
UID PID PPID C STIME TTY TIME COMMAND
user01 1234 1221 0 Feb 25 ttyq4 0:03 wh960.exe

In this case, the PID (Process ID) of the Yantra process is 1234.

IF the Oracle DB is running on the same unix server :
====================
The Oracle connection can be obtained using the command
ps -ef |grep PID (where PID is the process ID obtained in the previous step)
UID PID PPID C STIME TTY TIME COMMAND
user01 1234 1221 0 Feb 25 ttyq4 0:03 wh960.exe
user01 1236 1234 0 Feb 25 ttyq4 0:03 oracle..SID ....
Identify the process for which the Parent PID (PPID) is 1234; in this example '1236'.
====================
ELSE
====================
IF the Oracle DB is running on another remote server, then you will need to find Oracle shadow process from v$session ( using TOAD or a sql query )
====================

In another session, do the following:
* Log into SQL as 'oracle or any DBA user' and enter the following commands
(these commands may be available to use as a sql script called set_trace.sql or a similar name in your $BASE/util or $BASE/util/support_utils) :
i > alter system set timed_statistics = true ;
ii> select sid,serial# from v$session where process = '&Pid' ;
iii> begin
sys.dbms_system.set_sql_trace_in_session( &SID, &Serial, true);
end;
When prompted for PID, enter the PID of the process you want to monitor (1236 in this case).

#####
Request the user 'user01' to start using the application ( do the transaction like scanning or intended transaction to trace ).

This will generate a trace file called ora_1236.trc. (Note: The trace file will have the Oracle session ID for this process.)
(To determine where the trace file will be generated, see Solution 10692.)

Go into the dump directory, and at the Unix prompt, execute the following:

tkprof ora_1236.trc ora_1236.out explain=<DBUSER/DBPASSWD> sort=fchela
(If generating the *.out file in another directory is desired, enter the full path.)

( Note: There are a number of options available for the sort order. They can be displayed by entering "tkprof" at the Unix prompt. In this case, the fchela option sorts by elapsed time fetching data, and is used for finding queries taking the maximum time, sorted in descending order.)

View or edit the file ora_1236.out and find the first query, which is the one taking the most time
(Note: The execution plan will display the index that was used; this can be checked to ensure it is the correct one.)