Oracle SYS.AUDSES$ 序列 說明

 

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

 

 

 

 

 

 

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

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook:http://www.facebook.com/tianlesoftware

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940