Why doesn't AlTER SYSTEM SET EVENTS set the events or tracing immediately?

最新推荐文章于 2020-10-20 12:00:23 发布

Rhys-Oracle

最新推荐文章于 2020-10-20 12:00:23 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE 之性能优化 ORACLE之基本术语知识

本文链接：https://blog.youkuaiyun.com/rhys_oracle/article/details/12496349

ORACLE 之性能优化同时被 2 个专栏收录

30 篇文章

订阅专栏

ORACLE之基本术语知识

25 篇文章

订阅专栏

本文探讨了在Oracle数据库中使用ALTER SYSTEM设置events与parameters的区别，重点解释了为什么ALTER SYSTEM SET EVENTS不会立即对所有会话生效，并提供了详细的验证步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Why doesn't AlTER SYSTEM SET EVENTS set the events or tracing immediately?

今天看到Tanel的blog，说明了此问题：
我们通过alter system 设置events和parameter是不同的，
对于设置parameter使用alter system的影响如下：
1）对当前的session起作用
2）对新用户登录数据库的用户起作用
3）对已经登录到数据库的其他session起作用
对于使用alter system 设置events影响如下：
1）对当前session起作用
2）对新登录到系统中的用户起作用，但不对其他的session起作用。
1、对于alter system set parameters验证如下：
eg：
目前系统只有三个session登录：

SQL> select saddr,sid,serial#,username from v$session where username is not null;

SADDR                   SID    SERIAL# USERNAME
---------------- ---------- ---------- ------------------------------
000000007CFED5D8         33        103 SYS
000000007CFDE178         38        129 SCOTT
000000007CFD4ED8         41         65 RHYS

SQL>

首先修改log_checkpoints_to_alert参数为true，当前用户为sys；

SQL> show parameter log_checkpoint

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
log_checkpoints_to_alert             boolean     FALSE
SQL> alter system set log_checkpoints_to_alert=true;

System altered.

SQL> 
SQL> show user
USER is "SYS"
SQL>

在rhys账户查看该parameter值：

SQL> show user
USER is "RHYS"
SQL> select * from v$mystat where rownum<3;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        41          0          0
        41          1         12

SQL> show parameter log_checkpoints

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     TRUE
SQL>

在scott账户查看该parameter值：

SQL> show user
USER is "SCOTT"
SQL> select * from v$mystat where rownum<3;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        38          0          0
        38          1         12

SQL> show parameter log_checkpoints

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     TRUE
SQL>

新登录一个用户Amy；

SQL> create user amy identified by rhys default tablespace rhys;

User created.

SQL> grant dba to amy;

Grant succeeded.

SQL> select saddr,sid,serial#,username from v$session where username is not null;

SADDR                   SID    SERIAL# USERNAME
---------------- ---------- ---------- ------------------------------
000000007C8836E0          1         83 AMY
000000007CFED5D8         33        103 SYS
000000007CFDE178         38        137 SCOTT
000000007CFD4ED8         41         65 RHYS

SQL> 
SQL> show user 
USER is "AMY"
SQL> select * from v$mystat where rownum<3;

       SID STATISTIC#      VALUE
---------- ---------- ----------
         1          0          0
         1          1         12

SQL> show parameter log_checkpoints_to_alert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     TRUE
SQL>

2、对于对于alter system set events验证如下：

SQL> select saddr,sid,serial#,username from v$session where username is not null;

SADDR                   SID    SERIAL# USERNAME
---------------- ---------- ---------- ------------------------------
000000007CFED5D8         33        103 SYS
000000007CFDE178         38        137 SCOTT
000000007CFD4ED8         41         65 RHYS

SQL> show user
USER is "SYS"
SQL> alter system set events '10046 trace name context forever,level 12';

System altered.

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

SQL> 
SQL> !    
[oracle@oracle-one ~]$ cd /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/
[oracle@oracle-one trace]$ ls -l *1981*
-rw-r-----. 1 oracle oinstall 23897 Oct  9 09:58 RHYS_ora_1981.trc
-rw-r-----. 1 oracle oinstall   390 Oct  9 09:58 RHYS_ora_1981.trm
[oracle@oracle-one trace]$

可以看到sys用户已经存在trace文件了。
然后在rhys用户下查看一下信息：

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