DBMS_MONITOR

[Mladen Gogala]

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@medo (TNS V1-V3)
SQL> oradebug eventdump session
sql_trace level=12
SQL>


Question: what mechanism does DBMS_MONITOR use for tracing sessions?
--
http://mgogala.byethost5.com

[Charles Hooper]

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.

[Mladen Gogala]

On Fri, 23 Dec 2011 10:57:43 -0800, Charles Hooper wrote:

> 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.

Confirmed. Charles you're great!



--
http://mgogala.byethost5.com

[Mladen Gogala]

On Fri, 23 Dec 2011 10:57:43 -0800, Charles Hooper wrote:

> 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.

I figured out what's happening. I was convinced that DBMS_MONITOR sets a
flag in the UGA, but it doesn't do that. It sends a message to the
affected process, which will set the flag itself, the next time it is
activated . This is an example of what Jonathan calls "AST". DBMS_MONITOR
uses the same mechanism as Unix signal delivery: it queues the message,
probably incurring the famous "rdbms ipc messsage" wait, and the process
will set its own flags on the next activation. The next activation will
come when a SQL statement is submitted. OK, now I understand the
mechanism.




--
http://mgogala.byethost5.com

[Mark D Powell]

Yes, good job Charles.

I have seen problem posts related to dbms_monitor not tracing in the
past but when replies were "it works for me" type replies. Next time
I see such a post I will have to keep this behavior in mind.

-- Mark D Powlel --

[joel garry]

I thought oradebug had the same behavior (of not generating a trace
file until something happens)? Perhaps I'm misunderstanding/
misremembering since I haven't used it for a while, but that was my
thought at the first post - I was thinking it was ironic, because I
thought I saw that while following some post Mladen had made a while
back about how to use oradebug. But my memory is capricious at the
best of times.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2011/dec/27/tiny-tower-game-gets-huge-apple-recognition/