TRCSESS – Trace Information for Various Oracle Sessions or Modules – Quick & Dirty Tip

Database Sessions view from Oracle SQL Developer
Database Sessions view from Oracle SQL Developer

— Find your session

SQL> /* a Dell TOAD type query */

SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
RAWTOHEX (SADDR) AS saddr,
s.SID,
s.SERIAL#,
s.AUDSID,
RAWTOHEX (PADDR) AS paddr,
s.USER#,
s.USERNAME,
s.COMMAND,
s.OWNERID,
s.TADDR,
s.LOCKWAIT,
s.STATUS,
s.SERVER,
s.SCHEMA#,
s.SCHEMANAME,
s.OSUSER,
s.PROCESS,
s.MACHINE,
s.PORT,
s.TERMINAL,
UPPER (s.PROGRAM) PROGRAM,
s.TYPE,
s.SQL_ADDRESS,
s.SQL_HASH_VALUE,
s.SQL_ID,
s.SQL_CHILD_NUMBER,
s.SQL_EXEC_START,
s.SQL_EXEC_ID,
s.PREV_SQL_ADDR,
s.PREV_HASH_VALUE,
s.PREV_SQL_ID,
s.PREV_CHILD_NUMBER,
s.PREV_EXEC_START,
s.PREV_EXEC_ID,
s.PLSQL_ENTRY_OBJECT_ID,
s.PLSQL_ENTRY_SUBPROGRAM_ID,
s.PLSQL_OBJECT_ID,
s.PLSQL_SUBPROGRAM_ID,
s.MODULE,
s.MODULE_HASH,
s.ACTION,
s.ACTION_HASH,
s.CLIENT_INFO,
s.FIXED_TABLE_SEQUENCE,
s.ROW_WAIT_OBJ#,
s.ROW_WAIT_FILE#,
s.ROW_WAIT_BLOCK#,
s.ROW_WAIT_ROW#,
s.TOP_LEVEL_CALL#,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.PDML_ENABLED,
s.FAILOVER_TYPE,
s.FAILOVER_METHOD,
s.FAILED_OVER,
s.RESOURCE_CONSUMER_GROUP,
s.PDML_STATUS,
s.PDDL_STATUS,
s.PQ_STATUS,
s.CURRENT_QUEUE_DURATION,
s.CLIENT_IDENTIFIER,
s.BLOCKING_SESSION_STATUS,
s.BLOCKING_INSTANCE,
s.BLOCKING_SESSION,
s.FINAL_BLOCKING_SESSION_STATUS,
s.FINAL_BLOCKING_INSTANCE,
s.FINAL_BLOCKING_SESSION,
s.SEQ#,
s.EVENT#,
s.EVENT,
s.P1TEXT,
s.P1,
s.P1RAW,
s.P2TEXT,
s.P2,
s.P2RAW,
s.P3TEXT,
s.P3,
s.P3RAW,
s.WAIT_CLASS_ID,
s.WAIT_CLASS#,
s.WAIT_CLASS,
s.WAIT_TIME,
s.SECONDS_IN_WAIT,
s.STATE,
s.WAIT_TIME_MICRO,
s.TIME_REMAINING_MICRO,
s.TIME_SINCE_LAST_WAIT_MICRO,
s.SERVICE_NAME,
s.SQL_TRACE,
s.SQL_TRACE_WAITS,
s.SQL_TRACE_BINDS,
s.SQL_TRACE_PLAN_STATS,
s.SESSION_EDITION_ID,
s.CREATOR_ADDR,
s.CREATOR_SERIAL#,
s.ECID,
stat.cpu – stat.CPU_this_call_start cpu_this_call,
stat.CPU,
stat.UGA_memory,
stat.PGA_memory,
stat.Commits,
stat.Rollbacks,
si.Block_Gets,
si.Consistent_Gets,
si.Physical_Reads,
si.Block_Changes,
si.Consistent_Changes,
p.program “OSProgram”,
p.spid,
p.pid
FROM V$SESSION S,
V$SESS_IO si,
V$PROCESS p,
(  SELECT ss.sid stat_sid,
SUM (
DECODE (sn.name, ‘CPU used when call started’, ss.VALUE, 0))
CPU_this_call_start,
SUM (
DECODE (sn.name, ‘CPU used by this session’, ss.VALUE, 0))
CPU,
SUM (DECODE (sn.name, ‘session uga memory’, ss.VALUE, 0))
uga_memory,
SUM (DECODE (sn.name, ‘session pga memory’, ss.VALUE, 0))
pga_memory,
SUM (DECODE (sn.name, ‘user commits’, ss.VALUE, 0)) commits,
SUM (DECODE (sn.name, ‘user rollbacks’, ss.VALUE, 0))
rollbacks
FROM v$sesstat ss, v$statname sn
WHERE     ss.STATISTIC# = sn.STATISTIC#
AND (   sn.name = ‘CPU used when call started’
OR sn.name = ‘CPU used by this session’
OR sn.name = ‘session uga memory’
OR sn.name = ‘session pga memory’
OR sn.name = ‘user commits’
OR sn.name = ‘user rollbacks’)
GROUP BY ss.sid) stat
WHERE     (    (s.USERNAME IS NOT NULL)
AND (NVL (s.osuser, ‘x’) <> ‘SYSTEM’)
AND (s.TYPE <> ‘BACKGROUND’))
AND si.sid(+) = s.sid
AND p.addr(+) = s.paddr
AND stat.stat_sid = s.sid

— Setup a Client Trace:

SQL> exec dbms_monitor.client_id_trace_enable (client_id=>'<terminal or client string>’);
PL/SQL procedure successfully completed.

— Setup a trace for all matching session threads for a module:
SQL> exec dbms_monitor.serv_mod_act_trace_enable (service_name=>'<ORACLE_SID or SERVICE_NAME>’, module_name=>'<MODULE from V$SESSION>’);
PL/SQL procedure successfully completed.

Visit your trace directory (in 11gR2 and up, you can consolidate the various dump locations using the init.ora parameter diagnostic_dest='<filepath>’)

# *.trc looks through every trace file in the directory for the matching module string
# Can also use any of the other options to search for specific sessions:
# [action=action_name]
# [clientid=client_id]
# [session=session_id]

oracle > trcsess output=mytrace.trc module=”<MODULE from V$SESSION>” *.trc

# Then just execute your normal tkprof command to format the consolidated output

oracle > tkprof mytrace.trc myformattedtrace.txt sys=no sort=exeela explain=<user/pwd>

# Voila!  All the traces for a specific session, module, or action at a specific time.

— Don’t forget to disable your tracing afterwards!

SQL> exec dbms_monitor.client_id_trace_disable (client_id=>'<terminal or client string>’);
PL/SQL procedure successfully completed.

— Setup a trace for all matching session threads for a module:
SQL> exec dbms_monitor.serv_mod_act_trace_disable (service_name=>'<ORACLE_SID or SERVICE_NAME>’, module_name=>'<MODULE from V$SESSION>’);
PL/SQL procedure successfully completed.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s