Trace Session

Trace My session ID

select sys_context(’USERENV’,'SID’) from dual;
Oracle Session information user
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
order by spid;

SQL statement executed by a session

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null;

Find Objects locked by a session and the sql locking it

select c.owner,c.object_name,c.object_type,
b.sid,b.serial#,b.status,b.osuser,b.machine,s.sql_text from v$locked_object a ,v$session b,dba_objects c,v$sqltext_with_newlines s
where b.sid = a.session_id
and a.object_id = c.object_id and b.sql_address=s.address and b.sql_hash_value=s.hash_value;

Which session/sql is blocking what session/sql

select s1.sid||’,'||s1.serial# Blocker ,sql1.sql_text Blocking_sql,
s2.sid||’,'||s2.serial# Waiting ,sql2.sql_text Waiting_sql
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$sqltext_with_newlines sql1,v$sqltext_with_newlines sql2
where s1.sid=l1.sid and s2.sid=l2.sid
and sql1.hash_value=s1.sql_hash_value
and sql1.address=s1.sql_address
and sql2.hash_value=s2.sql_hash_value
and sql2.address=s2.sql_address
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

Enable sql trace with bind before executing a sql

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 4′;

Enable sql trace with binds for a running session

alter session set TRACEFILE_IDENTIFIER = ‘APPS_MYTAG’;

EXEC dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE);

Other options

 exec dbms_system.set_int_param_in_session(&&sid,&&serial,’MAX_DUMP_FILE_SIZE’,2147483647);

exec sys.dbms_system.set_BOOL_param_in_session(94,239,’sql_trace’,true);
exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

To disable trace

exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

10 G onwards

SELECT   b.name, b.value_string, sq.sql_text
FROM     v$sql_bind_capture b, v$sql sq, v$session s
WHERE    sq.sql_id = b.sql_id
AND      sq.address = b.address
AND      sq.child_address = b.child_address
AND      sq.parsing_user_id = s.user#
AND      s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;

Run tkprof on raw trace file

tkprof output.txt insert=insert.sql record=record.sql
tkprof Command Line Options

tkprof provides many useful command line options that provide additional functionality for the DBA.

· print – Lists only the first n SQL statements in the output file. If nothing is specified, all statements will be listed. Use this option when the list needs to be limited to the “Top n” statements. This is useful when combined with a sorting option to enable the top n statements by CPU, or disk reads, or parses, etc.

· aggregate – When “Yes”, tkprof will combine the statistics from multiple user executions of the same SQL statement. When “No”, the statistics will be listed each time the statement is executed.

· insert – Creates a file that will load the statistics into a table in the database for further processing. Choose this option if you want to perform any advanced analysis of the tkprof output.

· sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements. The default is to enable.

· table – Used in the Explain Plan command (if specified) for Oracle to load data temporarily into an Oracle table. The user must specify the schema and table name for the plan table. If the table exists all rows will be deleted otherwise tkprof will create the table and use it.

· record - creates a SQL script with the specified filename that contains all non-recursive SQL statements from the trace file. For DBAs wanting to log the SQL statements in a separate file, this is the option to use.

alter session set sql_trace=true ;

alter session set sql_trace=false ;

· explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan is less useful when used in conjunction with tkprof than it is when used alone. Explain Plan provides the predicted optimizer execution path without actually executing the statement. tkprof shows you the actual execution path and statistics after the statement is executed. In addition, running Explain Plan against SQL statements that were captured and saved is always problematic given dependencies and changes in the database environment.

· sort – Sorts the SQL statements in the trace file by the criteria deemed most important by the DBA. This option allows the DBA to view the SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers. The following are the data elements available for sorting:

· prscnt – The number of times the SQL was parsed.

· prscpu – The CPU time spent parsing.

· prsela – The elapsed time spent parsing the SQL.

· prsdsk – The number of physical reads required for the parse.

· prsmis – The number of consistent block reads required for the parse.

· prscu - The number of current block reads required for the parse.

· execnt – The number of times the SQL statement was executed.

· execpu – The CPU time spent executing the SQL.

· exeela – The elapsed time spent executing the SQL.

· exedsk – The number of physical reads during execution.

· exeqry – The number of consistent block reads during execution.

· execu – The number of current block reads during execution.

· exerow – The number of rows processed during execution.

· exemis – The number of library cache misses during execution.

· fchcnt – The number of fetches performed.

· fchcpu – The CPU time spent fetching rows.

· fchela – The elapsed time spent fetching rows.

· fchdsk – The number of physical disk reads during the fetch.

· fchqry – The number of consistent block reads during the fetch.

· fchcu – The number of current block reads during the fetch.

· fchrow – The number of rows fetched for the query.