Troubleshooting
Problem
Trace a SQL Session : Query Slowdown - System Performance - Oracle Monitoring
Symptom
PART: DCS
DATABASE: Oracle
Following is a procedure for
*
Starting a trace on an active Oracle session
* Troubleshooting
*
Performance tuning SQL/processes/jobs
* Identifying causes for slowdowns
and performance problems
Cause
Resolving The Problem
Query Slowdown - System Performance - Oracle Monitoring
To monitor
system performance, generate the trace file as follows:
(For purposes
of this example, user 'xs016' is performing the operation 'ASN Entry'.)
--Determine the terminal of the user:
tty
/dev/pty/ttyu3
--Request that the user xs016 log in to the system.
--Identify the Yantra process (egl) and the corresponding Oracle Connection
process established (oracleegl):
ps -ef | grep xs016 | grep -v grep
(For version 6.0 WMSYantra processes, grep for wh960.)
A
display similar to the following will be shown:
UID PID PPID C STIME
TTY TIME COMMAND
xs016 1234 1221 0 Feb 25 ttyq4 0:03
eglcrt
In this case, the PID (Process ID) of the Yantra process is
1234.
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
xs016 1234 1221 0 Feb
25 ttyq4 0:03 eglcrt
xs016 1236 1234 0 Feb 25 ttyq4 0:03 oracleeagl
....
--Identify the process for which the Parent PID (PPID) is 1234; in
this example '1236'.
--Request the user start an ASN Entry.
--In
another session, do the following:
--Log into SQL as root/oracle 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).
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.)
Historical Number
PRI48059
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21543611