Why doesn't AlTER SYSTEM SET EVENTS set the events or tracing immediately(二)

?

可以看到sys用户已经存在trace文件了。

然后在rhys用户下查看一下信息：

?

[sql] 
SQL> select * from v$mystat where rownum<3;  
  
       SID STATISTIC#      VALUE  
---------- ---------- ----------  
        41          0          0  
        41          1         19  
  
SQL>   
SQL> select table_name from user_tables;  
  
TABLE_NAME  
------------------------------  
DEPT  
BONUS  
AMY  
A  
B  
C  
T  
SYS_TEMP_FBT  
SALGRADE  
EMP  
  
10 rows selected.  
  
SQL> !  
[oracle@oracle-one ~]$ cd /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/  
[oracle@oracle-one trace]$ ls -ltr *2092*  
-rw-r-----. 1 oracle oinstall   1211 Oct  9 10:02 RHYS_ora_2092.trm  
-rw-r-----. 1 oracle oinstall 164508 Oct  9 10:02 RHYS_ora_2092.trc  
[oracle@oracle-one trace]$   

?

?

?

同样也存在trace文件了。

那么对于新增的用户呢？我们使用Amy账户登录。

?

[sql] 
SQL> show user  
USER is "AMY"  
SQL>  select saddr,sid,s.serial#,s.username,spid from v$session s,v$process p where s.paddr=p.addr and  s.username is not null;  
  
SADDR                   SID    SERIAL# USERNAME                       SPID  
---------------- ---------- ---------- ------------------------------ ------------------------  
000000007CFDE178         38        137 SCOTT                          2176  
000000007CFED5D8         33        103 SYS                            1981  
000000007CFD4ED8         41         65 RHYS                           2092  
000000007CFC2998         47        137 AMY                            5500  
  
SQL> select * from v$mystat where rownum<3;  
  
       SID STATISTIC#      VALUE  
---------- ---------- ----------  
        47          0          0  
        47          1         18  
  
SQL> !  
[oracle@oracle-one ~]$ cd /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/  
[oracle@oracle-one trace]$ ls -l *5500*  
-rw-r-----. 1 oracle oinstall 184626 Oct  9 10:25 RHYS_ora_5500.trc  
-rw-r-----. 1 oracle oinstall   1095 Oct  9 10:25 RHYS_ora_5500.trm  
[oracle@oracle-one trace]$   

?

?

发现也是可以跟踪到trace。

?

可知在11g中，alter system set events也是对已经登录的会话生效的。但是在10G确不是。

note：

如果在不知情的情况下设置了跟踪事件，想想那是多么可怕的事情。可能导致磁盘占满，系统宕机等事故。

如下是转自tanel的一句话：

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  W