Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Histogram Overview Oracle
  • Deleting first line and lastline of a file using sed Linux/Unix
  • Mutating Table Error while using database trigger Oracle
  • Oracle Standby Database Library Index from Metalink Oracle
  • Some OS level threshold for performance. Linux/Unix
  • xargs use Linux/Unix
  • scripts to take listener.log backup Linux/Unix
  • SYSOPER Mystery Oracle
  • How does one overcome the Unix 2 Gig file limit? Linux/Unix
  • Composite Index creation tip from Vivek Oracle
  • DBMS_STATS Metalinks Notes Oracle
  • Standby Database Behavior when a Datafile is Resized on the Primary Database Note:123883.1 Oracle
  • copying/removing directory with all its subdirectory Linux/Unix
  • How to change hostname in Linux Linux/Unix
  • Load testing on Oracle 19C RAC with HammerDB Oracle

Trace a SQL session from another session using ORADEBUG

Posted on 30-Sep-202530-Sep-2025 By Admin No Comments on Trace a SQL session from another session using ORADEBUG

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.

Oracle, SQL scripts

Post navigation

Previous Post: SQL Server Vs Oracle Architecture difference
Next Post: Postgres DB user and OS user.

Related Posts

  • Explain Plan Output 2 Oracle
  • arch_configUOCIOTTO.ora Oracle
  • refre.sql Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • 10g oem configuration Oracle
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Find Command Linux/Unix
  • Is It Recommended To Apply Patch Bundles When PSU Is Available? -ID 743554.1 Oracle
  • eplan.sql Oracle
  • Set Role explaination. Oracle
  • Useful Solaris Commands on 28-SEP-2005 Linux/Unix
  • PHP code to add WordPress posts in bulk programmatically PHP/MYSQL/Wordpress
  • Adding Datafile on Primary Server and Impact on Standby Server Oracle
  • prepfiles.sh for step by step generating pending statistics files Oracle

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme