Tag Archives: session

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

The Oracle Database 12c “Formerly-known-as-Next-Generation” at Collaborate 13

#C13DEN Session 671 – Monday 4/8 9:45A Mineral Hall Ballroom 3B (the room right next to my Introduction to Fusion Middleware- same Monday 9:45A, but MHB 3C) – These rooms are on the First Floor down in front of the main theatre.

Rich Niemiec speaks on Database 12c Features and Functionality in the way only Rich can do it (meaning 200+ pages of slide deck).

Oracle Magazine March 2013 - Database 12c Printed Publically
Oracle Magazine March 2013 – Database 12c Printed Publically

Here’s why we can call it “12c” – Oracle printed it for us to share in last month’s Oracle Magazine: