Oracle SYS.AUDSES$ 序列 说明

Oracle SYS.AUDSES$ 序列 说明

分类： Oracle 性能调优 2012-02-07 16:33 100人阅读 评论(0) 举报

 

一.SYS.AUDSES$序列说明 1.1 MOS 说明

MOS 文档：How Sessions get Their AUDSID Identifier [ID 122230.1]

 

Sessionsconnecting to an Oracle database have a number of identifiers assigned to themwhich can be found in the V$SESSION dynamic view.

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

 

One of theseidentifiers is V$SESSION.AUDSID.  Thisidentifier is particularly useful because it can also be obtained by a sessionusing the SQL function USERENV('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 session identifiers, the Auditing Session ID, by use of the built-in USERENV SQL function.

 

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

 

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

USERENV('SESSIONID')

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

               13591

--查看当前的Auditingsession id

 

This value maybe used to find out more information about the current session as it is alsorecorded in the column AUDSID of the V$SESSION dynamic view.

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

 

Continuing the previous example:

 

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

 

      SID    SERIAL#     AUDSID PROGRAM

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

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

 

This AuditingSession ID is determined when the user connects to the database and is aconstant value for the duration of the session.

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

 

The value thatis assigned to AUDSID is taken from a sequence in the Oracle Data Dictionary,the sequence SYS.AUDSES$.  When AUDSID isassigned to a new session the SYS.AUDSES$ sequence is incremented using NEXTVALand the new value is assigned to the session.

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

 

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

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

 

For example, see what happens when weconnect internal in svrmgrl:

 

SVRMGR> connect internal

Connected.

 

SVRMGR> select userenv('SESSIONID') fromdual;

USERENV('S

----------

        0

1 row selected.

 

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

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

 

For example, connecting internal fromSQL*Plus:

 

SQL> connect internal/oracle

Connected.

SQL> select userenv('SESSIONID') fromdual;

 

USERENV('SESSIONID')

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

                   0

 

Background processes also have 0 as theirAUDSID value。

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

 

Continuing from the last SQL*Plus session,where we are connected internal:

 

SQL> select sid,serial#,audsid,programfrom 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' andin both of these cases AUDSID is again 0.

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

 

In 10g we make a further distinction forSYS sessions: 

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

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

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

 

SYS@anqing1(rac1)> conn / as sysdba;

Connected.

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

 

USERENV('SESSIONID')

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

         4294967295

 

小结：

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

       Auditingsession ID的值是从SYS.AUDSES$序列中获取，每次取时会自动增加，然后赋给session。

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

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

 

 

二.SYS.AUDSES$的优化

 

2.1 Sequence 说明

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

OracleSequence Cache 参数说明

http://blog.csdn.net/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:SQcontention / row cache lock / DFS lock handle(SV) 等待事件 说明

http://blog.csdn.net/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$ cache1000;

Sequence altered.

 

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

 

2.2 RAC Hangs due tosmall cache size on SYS.AUDSES$ [ID 395314.1]

 

PURPOSE

-------

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

Default of 20 isnot adequate in many cases.

 

 

PROBLEM:

--------

The defaultsetting for the SYS.AUDSES$ sequence is 20, this is too low for a RAC system wherelogins can occur concurrently from multiple nodes.  During high login rate such low value cancause 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

 - RAChangs due to QMON deadlocking

 

During thosehangs session login is not possible (or *extremely* slow) due to extremely highcontention on the above sequence.

 

SOLUTION:

---------

Manually increase that sequence cache oneach affected database:

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

SQL>alter sequence sys.audses$ cache10000;

 

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 - Contentionon 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 thatdocument is how to reset AUDSES$ sequence ,and any other sequence,to make itstart again with the start with value.

 

You  cannotdirectly  change  the start with value of a sequence using thefollowing command:

Alter sequenceAUDSES$ start with 1;

 

This statement will generate the following error:

ORA-02283: cannot alter starting sequencenumber

 

 

Solution

Here is the steps to reset AUDSES$ sequence :

 

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

 

1-It is recommended to take a backup fromthe 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 value can be obtained by :
SQL>select *
          from dba_sequences
          wheresequence_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 canre change to any other value wanted.

In 11g, you may encounteran error when attempting the above, such as:
ORA-04009: MAXVALUE cannot be made to be less than the current value

--在Oracle11g，限制的更严格一点，MAXVALUE 值不能小于当前值。

 

To work around this you may do thefollowing:

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 ofprevious 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;