Re: DBMS_MONITOR

From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Fri, 23 Dec 2011 10:57:43 -0800 (PST)
Message-ID: <85582d38-62e8-4df0-9317-8cb0ea8da18a_at_l29g2000yqf.googlegroups.com>

On Dec 22, 5:02 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> It seems that DBMS_MONITOR doesn't operate using the standard event
> mechanism. When I enabled trace in a session using DBMS_MONITOR, the
> eventdump didn't show anything. When an explicit "alter session set
> events" statement was issued, eventdump detected it without problem:
>
> SQL> oradebug setospid 4645
> Oracle pid: 30, Unix process pid: 4645, image: oracle_at_medo (TNS V1-V3)
> SQL> oradebug eventdump session
> sql_trace level=12
> SQL>
>
> Question: what mechanism does DBMS_MONITOR use for tracing sessions?

I believe that the session with the trace enabled through DBMS_MONITOR must execute at least one SQL statement after tracing is enabled for the session, before ORADEBUG will report that the trace is enabled.

An example with 2 sessions (Session 2 connected as SYS): In Session 1, execute the following SQL statement to pick up the SID, SERIAL# and PID for Session 1, along with the settings that indicate whether or not a 10046 trace at level 1, 4, 8, or 12 is enabled: SELECT

  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS

FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)   AND S.PADDR=P.ADDR; SID SERIAL# PID SQL_TRAC SQL_T SQL_T --- ---------- ---------- -------- ----- ----- 130 3 20 DISABLED FALSE FALSE

--

In Session 2, enable a 10046 trace foe the session at level 12, and
dump the events for Session 1:
EXEC
DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>130,SERIAL_NUM=>3,WAITS=>TRUE,BINDS=>TRUE)

ORADEBUG SETORAPID 20

Oracle pid: 20, Windows thread id: 13756, image: ORACLE.EXE (SHAD)

ORADEBUG EVENTDUMP session
Statement processed.

Notice in the above that nothing was output by the ORADEBUG EVENTDUMP
session command.

In Session 1, let's execute the same SQL statement as was executed
earlier:
SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
130          3         20 ENABLED  TRUE  TRUE

Notice in the above output that a 10046 trace at level 12 is enabled
for the session.

In Session 2, let's check again which events are enabled for Session
1:
ORADEBUG EVENTDUMP session
sql_trace level=12

Notice in the above output that ORADEBUG now indicates that a 10046
trace at level 12 is enabled for Session 1, because a SQL statement
was executed in Session 1 after the trace was enabled.

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Received on Fri Dec 23 2011 - 12:57:43 CST