Why doesn’t ALTER SYSTEM SET EVENTS set the events or tracing immediately?

I received a question about ALTER SYSTEM in the comments section of another blog post recently.

Basically the question was that while ALTER SESSION SET EVENTS ’10046 … ‘ enabled the SQL Trace for the current session immediately, ALTER SYSTEM on the other hand didn’t seem to do anything at all for other sessions in the instance.

There’s an important difference in the behavior of ALTER SYSTEM when changing paramters vs. setting events.

For example, ALTER SYSTEM SET optimizer_mode = CHOOSE would change the value of this parameter immediately, for:

  1. Your own session
  2. All new sessions that will log in will pick up the new parameter value
  3. All other existing sessions

However, when you issue an ALTER SYSTEM SET EVENTS ’10046 TRACE NAME CONTEXT FOREVER, LEVEL 12′, the event changes in only #1 and #2 will happen:

  1. Your own session
  2. All new sessions that will log in will pick up the new event settings

This means that the existing, already logged in sessions, will not pick up any of the events set via ALTER SYSTEM!

This hopefully explains why sometimes the debug events don’t seem to work. But more importantly, this also means that when you disable an event (by setting it to “OFF” or to level 0) with ALTER SYSTEM, it does not affect the existing sessions who have this event enabled! So, you think you’re turning the tracing off for all sessions and go home, but really some sessions keep on tracing – until the filesystem is full (and you’ll get a phone call at 3am).

So, to be safe, you should use DBMS_MONITOR for your SQL Tracing needs, it doesn’t have the abovementioned problems. For other events you should use DBMS_SYSTEM.SET_EV/READ_EV (or ORADEBUG EVENT/SESSION_EVENT & ¬†EVENTS/EVENTDUMP) together with ALTER SYSTEM for making sure you actually do enable/disable the events for all existing sessions too. Or better yet, stay away from undocumented events ;-)

If you wonder what/where is the “system event array”, it’s just a memory location in shared pool. It doesn’t seem to be explicitly visible in V$SGASTAT in Oracle 10g, but in 11.2.0.3 you get this:

No system-wide events set:

SQL> @sgastat event

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  DBWR event stats array            216
shared pool  KSQ event description            8460
shared pool  Wait event pointers               192
shared pool  dbgdInitEventGrp: eventGr         136
shared pool  event classes                    1552
shared pool  event descriptor table          32360
shared pool  event list array to post           36
shared pool  event list to post commit         108
shared pool  event statistics per sess     2840096
shared pool  event statistics ptr arra         992
shared pool  event-class map                  4608
shared pool  ksws service events             57260
shared pool  latch wait-event table           2212
shared pool  standby event stats              1216
shared pool  sys event stats                539136
shared pool  sys event stats for Other       32256
shared pool  trace events array              72000

17 rows selected.

Let’s set a system-wide event:

SQL> ALTER SYSTEM SET events = '942 TRACE NAME ERRORSTACK LEVEL 3'; 

System altered.

And check V$SGASTAT again:

SQL> @sgastat event

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  DBWR event stats array            216
shared pool  KSQ event description            8460
shared pool  Wait event pointers               192
shared pool  dbgdInitEventG                   4740
shared pool  dbgdInitEventGrp: eventGr         340
shared pool  dbgdInitEventGrp: subHeap          80
shared pool  event classes                    1552
shared pool  event descriptor table          32360
shared pool  event list array to post           36
shared pool  event list to post commit         108
shared pool  event statistics per sess     2840096
shared pool  event statistics ptr arra         992
shared pool  event-class map                  4608
shared pool  ksws service events             57260
shared pool  latch wait-event table           2212
shared pool  standby event stats              1216
shared pool  sys event stats                539136
shared pool  sys event stats for Other       32256
shared pool  trace events array              72000

19 rows selected.

So, the “system event array” lives in shared pool, as a few memory allocations with name like “dbgdInitEventG%”. Note that this naming was different in 10g, as the dbgd module showed up in Oracle 11g, when Oracle guys re-engineered the whole diagnostics event infrastructure, making it much more powerful, for example allowing you to enable dumps and traces only for a specific SQL_ID.

Ok, that’s enough for today – I’ll just remind you that this year’s last Advanced Oracle Troubleshooting online seminar starts in 2 weeks (and I will be talking more about event setting there ;-)

This entry was posted in Oracle. Bookmark the permalink.

6 Responses to Why doesn’t ALTER SYSTEM SET EVENTS set the events or tracing immediately?

  1. Been there, done that :-)
    Google don’t tell you this about tracing on system level ;-)

    Well it has actually been many years since I traced on system level. In what occations do you trace on system level Tanel?

    • Tanel Poder says:

      Hi Lasse, I don’t sql*trace at system level, DBMS_MONITOR is much more flexible for tracing what you want. I wrote this post as someone asked about ALTER SYSTEM tracing behavior from me. I have used ALTER SYSTEM for setting an extra dump on an occasion of an event though (unexplained ORA-00001s or ORA-4030).

      • olivier bernhard says:

        one should also remember the difference between :
        “alter system set events” => scope is memory
        and
        “alter system set event” => scope is spfile
        (not specific to 10046)
        :-)

        • Tanel Poder says:

          Indeed! For the readers who are wondering what’s the difference – the SET EVENTS will not change any parameters, it just changes the system event array as I explained, while SET EVENT will change a parameter called “event” in SPFILE and the event settings are picked up after a restart.

  2. Saad says:

    Hi, we have a situation where we turned on event 20011 and 29913 and then turned it off. Users complained of performance issues afterwhich DB was recycled. As soon as its done, a ton of trace files started getting initiated for various background/foreground processes. One of them at the alert file loc with name _j000_179184_1.trc is getting humungous and current size exceeding 4G. Its not letting us open/delete/move either due to being in use. Now as per your blog when I checked sgastat it shows following:
    SQL> select * from v$sgastat where name like ‘%Event%’;

    POOL NAME BYTES
    ———— ————————– ———-
    shared pool dbgdInitEventGrp: eventGr 216

    Can we know which event is this and how is it triggered while oardebug shows none? Thanks for help

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>