Oracle SYS.AUDSES$ 序列 说明

一.S YS.AUDSES$ 序列说明

1.1 MOS 说明

MOS 文档：How Sessions get Their AUDSID Identifier [ID 122230.1] Sessions connecting to an Oracle database have a number of identifiersassigned to them which can be found in the V$SESSION dynamic view.

--当session 连接到Oracle 数据库时，会分配一个标识号，这个标识号可以从v$session这个视图里查询。

One of these identifiers is V$SESSION.AUDSID. This identifier is particularlyuseful because it can also be obtained by a session using the SQL functionUSERENV('SESSIONID'). In this way, a session can find its own session-related information.

--其中一个标识符就是v$session.audsid. 这个标识符非常有用，因为它也可以用SQL 函数USERENV(‘SESSIONID’) 来获取，这样，session 就可以发现其关联的信息。

1.2 SESSION 获取v$session.audsid说明

A session connected to an Oracle database may obtain one of its sessionidentifiers, the Auditing Session ID, by use of the built-in USERENV SQLfunction.

For example, in SQL*Plus, a query like the following can be used:

SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')

--------------------

13591

--查看当前的Auditing session id

This value may be used to find out more information about the current sessionas it is also recorded in the column AUDSID of the V$SESSION dynamic view.

--通过这个audid，然后和v$session结合，就可以查看session 的更多信息。Continuing the previous example:

SQL> select sid,serial#,audsid,program from v$session where audsid=13591;

SID SERIAL# AUDSID PROGRAM

---------- ---------- ---------- ------------------------------------------

13 2904 13591 sqlplus@sunclient1 (TNS V1-V2)

This Auditing Session ID is determined when the user connects to the databaseand is a constant value for the duration of the session.

--AUDID 决定用户是什么时候连接到数据库的，在session 活动期间，该值是不变的。

The value that is assigned to AUDSID is taken from a sequence in the OracleData Dictionary, the sequence SYS.AUDSES$. When AUDSID is assigned to a newsession the SYS.AUDSES$ sequence is incremented using NEXTVAL and the newvalue is assigned to the session.

--那么session 分配的AUDSID 值是从SYS.AUDSES$序列中获取的，当分配时，SYS.AUDSES$会使用nextval增加，然后赋值给session。

However, not all sessions get an AUDSID: Sessions connecting as 'internal'do not increment the SYS.AUDSES$ sequence and the value of their AUDSID is 0.

--但是，不是所有的session 都可以获取到AUDSID值。用internal 连接时，就不会增加SYS.AUDSES$序列，其对应session 的AUDSID 值是0.

For example, see what happens when we connect internal in svrmgrl:

SVRMGR> connect internal

Connected.

SVRMGR> select userenv('SESSIONID') from dual;

USERENV('S

----------

1 row selected.

Connect internal gets an AUDSID of 0 irrespective of which tool was used.

--不管使用什么工具连接，只要用的是internal用户，那么AUDSID 就是0

For example, connecting internal from SQL*Plus:

SQL> connect internal/oracle

Connected.

SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')

--------------------

Background processes also have 0 as their AUDSID value。

--后台进程对应的AUDSID 值也是0.

Continuing from the last SQL*Plus session, where we are connected internal: SQL> select sid,serial#,audsid,program from v$session;

SID SERIAL# AUDSID PROGRAM

---------- ---------- ---------- ------------------------------

1 1 0 oracle@sunclient1 (PMON)

2 1 0 oracle@sunclient1 (DBWR)

3 1 0 oracle@sunclient1 (ARCH)

4 1 0 oracle@sunclient1 (LGWR)

5 1 0 oracle@sunclient1 (CKPT)

6 1 0 oracle@sunclient1 (SMON)

7 1 0 oracle@sunclient1 (RECO)

8 17125 0 sqlplus@sunclient1 (TNS V1-V2)

10 rows selected.

Of course 'connect internal' is equivalent to connecting 'AS SYSOPER'or 'AS SYSDBA' and in both of these cases AUDSID is again 0.

--internal 进行连接，和’as sysoper’,’as sysdba’连接是等价的。其对应的AUDSID 都是0.

In 10g we make a further distinction for SYS sessions:

If AUDSID=0, then it is an internally generated SYS session.

If AUDSID=UB4MAX(4294967295), then it is a direct SYS session.

--在Oracle 10g，如果AUDSID 为0. 则表示的使用内部生成的SYS session，如果AUDSID 为UB4MAX(4294967295),那么表示直接使用SYS 用户登陆的。SYS@anqing1(rac1)> conn / as sysdba;

Connected.

SYS@anqing1(rac1)> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')

--------------------

4294967295

小结：

当session连接数据库时，会分配一个Auditing Session ID，可以使用userenv('SESSIONID')查看当前AUDID 的值，其值和v$session视图里的audid 等值，所以结合这个，就可以查看当前session 的更多信息。

Auditing session ID的值是从SYS.AUDSES$序列中获取，每次取时会自动增

加，然后赋给session。

对于internal用户（’/as sysoper’和‘/as sysdba’）和后台进程，其对应的AUDID 为0.

在Oracle 10g中，如果AUDID的值为0，表明是internal 用户，如果AUDID 值是4294967295，那么就表明是用SYS 用户直接连接的。

二.S YS.AUDSES$的优化

2.1 Sequence 说明

之前整理了一篇有关sequence 优化的Blog：

Oracle Sequence Cache 参数说明

https://www.360docs.net/doc/2117684091.html,/tianlesoftware/article/details/5995051

sequence 的cache 对高DML的应用来说，在性能上的提高有很大帮助，而且对于sequence，我们可以使用alter修改除start 之外的所有属性，所以修改sequence 也很方便。

Sequence cache 的默认是20，包括这里的SYS.AUDSES$，这个值一般来说都是不合适的，尤其是高并发的系统，需要更大的cache。所以在应用上线之前，我们会检查应用的中sequence 的cache值，一般都会改成1000.

Oracle为了管理sequence使用了以下三中锁

（1）row cache lock

在调用sequence.nextval过程中，将数据字典信息进行物理修改时获取，赋予了nocache属性的sequence上发生。

（2）SQ锁 -- enq: SQ

在内存上缓存(cache)范围内，调用sequence.nextval期间拥有此锁，赋予了cache+noorder 属性的sequence上发生。

（3）SV锁 -- DFS lock handle

RAC上节点之间顺序得到保障的情况下，调用sequence.nextval期间获得，赋予了cache+order属性的sequence上发生。

赋予了CACHE属性的sequence调用nextval期间，应该以SSX模式获得SQ 锁，许多会话同时为了获取SQ锁而发生争用过程中，若发生争用，则等待enq:SQ-contention.

创建Sequence赋予的CACHE值较小时，有enq:SQ-contention等待增加的趋势，CACHE值较小，内存上事先CACHE的值很快被耗尽，这时需要将数据字典信息物理修改，再次执行CACHE的工作，在此期间，因为一直要拥有SQ锁，相应的Enq:SQ-contention事件的等待时间也会延长，很不幸的是，在创建Sequence时，将CACHE值的缺省值设定为较小20, 因此创建使用量最多的Sequence时，CACHE值应该取1000以上的较大值。

更多测试参考我的blog：

enq:SQ contention / row cache lock / DFS lock handle(SV) 等待事件说明

https://www.360docs.net/doc/2117684091.html,/tianlesoftware/article/details/6534886

所以我们可以通过查看DB的等待事件来确认sequence 的cache 时候需要调整. 这些等待事件可以直接通过AWR 报告来查看，也可以通过相关的视图。

1.对于单实例：

可以查看v$system_event中row cache locks。

2. 对于RAC,可以查看gv$enqueue_stat中enq: SQ – contention等待事件。一般来说对于RAC，都是加大sequence的cache，并使用noorder选项，同时要修改sys.audses$的cache。

前面已经提到，需要修改SYS.AUDSES$, 在第一部分讲了每个session 连接时都会分配一个AUDID,其是从SYS.AUDSES$序列中获取，那么如果cache 过小，且在某一个时刻有多个session 同时创建，那么就可能遇到enq:SQ-contention 等待事件。

所以在优化的时候关注2个：一个是enq:SQ-contention等待事件，还有一个是logons 数。

在RAC环境下，如果logon较为频繁的话，这个sequence是必须调整的。logon 高，且cache 过小，可能导致整个数据库hang住。

这2个数据都可以从AWR中直接获取。

修改cache：

SQL> alter sequence sys.audses$ cache 1000;

Sequence altered.

根据具体的业务情况，这个cache 值还可以在增大。

2.2 RAC Hangs due to small cache size on SYS.AUDSES$ [ID 395314.1] PURPOSE

-------

Prevent hangs in RAC due to high login rate and low cache setting of AUDSES$ sequence.

Default of 20 is not adequate in many cases.

PROBLEM:

--------

The default setting for the SYS.AUDSES$ sequence is 20, this is too low for a RAC systemwhere logins can occur concurrently from multiple nodes. During high login ratesuch low value can cause slowness and even hangs. Some of the symptoms are:

--默认的SYS.AUDSES$是20，这个值对于高并发的RAC来说过低，在高login和低cache的情况下，能导致系统变慢甚至hang住。具体的表现如下：

- Checkpoint not completing on all RAC nodes

- Waits expire on row cache enqueue lock dc_sequences

- RAC hangs due to QMON deadlocking

During those hangs session login is not possible (or *extremely* slow) due to extremely high contention on the above sequence.

SOLUTION:

---------

Manually increase that sequence cache on each affected database:

--解决方法是手工的修改sequence 的cache：

SQL>alter sequence sys.audses$ cache 10000;

This is fixed in 10.2.0.3 patchset. (Affected releases 9i to 10.2.0.2)

--这个问题在10.2.0.3 中修复，其影响范围从9i 到10.2.0.2.

REFERENCES

----------

bug:4390868

bug:2855838

Bug 4390868 - Contention on DC_SEGMENTS due to small cache size on SYS.AUDSES$ [ID 4390868.8]

2.3 How To Reset AUDSES$ Sequence [ID 443618.1]

The goal of that document is how to reset AUDSES$ sequence ,and any other sequence,to make it start again with the start with value.

Y ou cannot directly change the start with value of a sequence using the following command:

Alter sequence AUDSES$ start with 1;

This statement will generate the following error:

ORA-02283: cannot alter starting sequence number

Solution

Here is the steps to reset AUDSES$ sequence :

please note that requires the sequence to be CYCLE_FALG to be Y which is the default for that sequence:

1-It is recommended to take a backup from the database.

2- Connect to the database using sys account:

SQL>select AUDSES$.nextval from dual;

That will return a value X

SQL>alter sequence AUDSES$ maxvalue X;

Please note that maxvalue above should be greater than the value of CACHE_SIZE ----->that valuecan be obtained by :

SQL>select *

from dba_sequences

where sequence_name='AUDSES$';

--maxvalue需要大于CACHE_SIZE，其值可以从dba_sequences中查询。SQL>select AUDSES$.nextval from dual;

It should start again from the beginning.

Please note the maximum value for that sequence has been changed to X you can re change to any othervalue wanted.

In 11g, you may encounter an error when attempting the above, such as:

ORA-04009: MAXVALUE cannot be made to be less than the current value

--在Oracle 11g，限制的更严格一点，MAXV ALUE 值不能小于当前值。

To work around this you may do the following:

sql> connect / as sysdba

shutdown immediate

startup restrict

set lines 80

select * from dba_sequences where sequence_name='AUDSES$';

-- determine increment number:

select (max_value - last_number - 5) from dba_sequences where sequence_name=

'AUDSES$';

-- modify number in next statement from outcome of previous statement:

alter sequence audses$ increment by 10 cache 2;

select audses$.nextval from dual;

-- change increment value back to 1:

alter sequence audses$ increment by 1 cache 20;

------------------------------------------------------------------------------------------------------- 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任! Email: tianlesoftware@https://www.360docs.net/doc/2117684091.html,

Skype: tianlesoftware

Blog: https://www.360docs.net/doc/2117684091.html,

Weibo: https://www.360docs.net/doc/2117684091.html,/tianlesoftware

Twitter: https://www.360docs.net/doc/2117684091.html,/tianlesoftware

Facebook: https://www.360docs.net/doc/2117684091.html,/tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请---- DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满) DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群：142216823 DBA6 群：158654907 DBA7群：172855474DBA总群：104207940