Find the SPID from Oracle’s SID using below SQL.
REM **** This is used to get SPID from SID.
col username format a30
col machine format a20
col program format a40
accept _sid prompt 'Enter Oracle Session ID ->'
select a.sid, b.pid, b.spid, a.username,
a.program,a.machine
from v$session a,V$process b
where a.paddr = b.addr
and a.sid = &_sid
/
Use ORADEBUG command as below.
oradebug setospid <SPID_FROM_QRY>
oradebug unlilimit
oradebug tracefile_name
oradebug event 10046 trace name context forever, level 12
—- Run some queries for which you want traces.
oradebug event 10046 trace name context off
Now, we have tracefile_name available. Use TKPROF utility to analyze the tracefile contents generated via 10046 trace as below:
🔹 What is tkprof?
tkprof is a utility that formats Oracle SQL trace files into human-readable reports.
Suppose you have a trace file: ora_1234.trc
Run:
tkprof ora_1234.trc report.prf sys=no sort=exeela,fchela
sys=no→ exclude SYS schema SQL (noise)sort=exeela,fchela→ sort by execution elapsed time and fetch elapsed time
Now open report.prf to analyze performance.
🔹 Common tkprof Options
| Option | Purpose |
|---|---|
sys=no | Excludes recursive SQL from SYS |
sort=option | Sorts SQLs by performance metric |
explain=user/password | Adds explain plan info |
aggregate=yes/no | Whether to group identical SQLs |
insert=filename.sql | Creates a SQL script of all statements |
🔹 Cheat Sheet – Most Useful sort Options
| Sort Option | Meaning |
|---|---|
prsela | Parse elapsed time |
prscpu | Parse CPU time |
exeela | Execution elapsed time |
execpu | Execution CPU time |
fchela | Fetch elapsed time |
fetchpu | Fetch CPU time |
disk | Disk reads |
query | Consistent gets |
current | Current (buffer) gets |
rows | Rows processed |
👉 Example:
tkprof ora_1234.trc top_sql.prf sys=no sort=exeela,disk,rows
This shows the most expensive SQLs by elapsed time, disk reads, and rows processed.