I received a question about ALTER SYSTEM in the comments section of another blog post recently.
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:
- Your own session
- All new sessions that will log in will pick up the new parameter value
- 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:
- Your own session
- 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!
Update 1: Note that since Oracle 11g there’s a parameteter “_evt_system_event_propagation” (default TRUE) and it makes Oracle to propagate the “ALTER SYSTEM” events into existing sessions too. So this problem described above applies only up to Oracle 10.2.
Update 2: Oracle 12.2 seems to have a bug that prevents some events (at least one ALTER SYSTEM SET sql_trace example with a SQL_ID qualifier) from being propagated correctly to other database sessions (link)
Update 3: Apparently in Oracle 18.3 the abovementioned bug is fixed.
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.
