Locating SQL in Oracle

sql_id can be found in the following views

v$sql, V$sqlstat, or dba_hist_sqlstat

SET LINESIZE 100
COLUMN trace_file FORMAT A60

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

In 11g

SET LINESIZE 100
COLUMN value FORMAT A60
SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

tkprof
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table “” not found /]

[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:

select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/

SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)

--------------------------------------------------------------------------------------------------------------------
If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:
column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
----------------------------------------------------------------------------------------------------------------------

Note:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples

Note:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Note:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)

Note:1102801.1 How to Get 10046 Trace for Parallel Query
Note:242374.1 Tracing PX session with a 10046 event or sql_trace

Note:258418.1 Getting 10046 Trace for Export and Import

ITRemote

Ready for Action?