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.