The purpose of this blog is to assist DBAs encountering performance issues in locating the problematic query by taking a trace of the session. The steps shared here will help to identify the exact query that causes performance issues in oracle database.
Below are the steps:
Step:1
- Login to E-business suite database and find the particular session causing the performance issue. (Note down the instance id, SID and Serial#).
To check the sid and serial# of the session from v$session
Use the following query:
select username, sid, serial#, to_char(logon_time,’DD-MON-YYYY HH:MI:SS’) from v$session where username=’USERNAME’ order by logon_time;
Step:2
2) Connect as sysdba in the database
conn sys /as sysdba
Step:3
3)Enable the trace for the session using oracle standard package:
exec dbms_monitor.session_trace_enable(320,24175, TRUE, TRUE);
Note: The last 2 “TRUE” refers ‘Binds’ and ‘Waits’ for the session.
Step:4
4) Once the session enabled with trace , replicate the issue in application to collect the trace files.
Step:5
5)To find the name of the trace file use the below query
select spid from v$process where addr=(select paddr from v$session where sid=320);
You can also search for the trace file using below commands in Udump folder:
ls -ltr *spid*trc
Step:6
6) Now the trace file will be available in the udump folder of the database server
To find the udump value, you can run the below commands.
Example:
sql>show parameter dump ———>This shows the udump directory.
For Oracle 12c and higher version:
sql>show parameter diag ———–> check 12c above versions
Step:7
7) Ensure to disable the trace once the issue is replicated.
SQL> exec dbms_monitor.session_trace_disble(320,24175);
“tkprof” is a tool for analyzing the performance of SQL statements executed in an Oracle database. It’s used to analyze and identify the most time-consuming queries and find ways to optimize them for better performance.
Below is an example:
tkprof tracefile_name.trc output.txt sys=no sort='(prsela,exeela,fchela)’ explain=apps/$APPS_PWD
