[ORACLE 11G]ROW CACHE LOCK 等待

renjixinchina發表於2013-03-11
Oracle
資料庫版本11.2.0.1
使用者反映修改密碼後程式明顯變慢:
檢視AWR發現:

Top 5 Timed Foreground Events

    Event Waits Time(s) Avg wait (ms) % DB time Wait Class
    row cache lock 24,378 46,231 1896 87.83 Concurrency
    DB CPU   3,302   6.27  
    enq: TX - row lock contention 317 36 115 0.07 Application
    SQL*Net more data to client 986,587 31 0 0.06 Network
    direct path read 14,789 19 1 0.04 User I/O

    Time Model Statistics

    • Total time in database user-calls (DB Time): 52635.9s
    • Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
    • Ordered by % or DB time desc, Statistic name
    Statistic Name Time (s) % of DB Time
    connection management call elapsed time 21,797.09 41.41
    sql execute elapsed time 21,419.44 40.69
    parse time elapsed 9,357.75 17.78
    hard parse elapsed time 9,307.83 17.68
    DB CPU 3,302.18 6.27
    hard parse (sharing criteria) elapsed time 391.35 0.74
    failed parse elapsed time 7.07 0.01
    PL/SQL execution elapsed time 2.62 0.00
    repeated bind elapsed time 0.10 0.00
    PL/SQL compilation elapsed time 0.01 0.00
    sequence load elapsed time 0.00 0.00
    DB time 52,635.93  
    background elapsed time 47.09  
    background cpu time 24.08  
     

    由以上資訊基本判定是由使用者的登入引起:
    進一步驗證:
    SQL>
    select event,p1 from v$session a where a.username is not null and a.status='ACTIVE';SQL>

    EVENT                                                                    P1
    ---------------------------------------------------------------- ----------
    row cache lock                                                            7
    row cache lock                                                            7
    row cache lock                                                            7
    row cache lock                                                            7
    SQL*Net message to client                                        1650815232
    查詢v$rowcache
    SQL> select parameter from v$rowcache where cache#=7;

    PARAMETER
    --------------------------------
    dc_users
    dc_users
    dc_users


    以上資訊可確定是由於使用者的登入導致的大量的row cache lock等待
    懷疑是11g的delayed failed logins特性引起

    查詢使用者的profile 發現 failed_login_attempts 設定為 unlimited

    之後修改failed_login_attempts 為10
    半小時後使用者使用的使用者被鎖 可以判斷使用者改密碼還有應用繼續使用原來的密碼登入 登入失敗後反覆嘗試所致
    failed_login_attempts 特性參考
    http://space.itpub.net/?uid-15747463-action-viewspace-itemid-755776

    ----透過審計功能分析是哪臺機器哪個使用者的頻繁登陸失敗
    SQL> select * from
    (
    select os_username,userhost,terminal,username,count(*) a
        from dba_audit_trail
       where returncode = 1017 and timestamp>= date '2013-3-11'
       group by os_username,userhost,username,terminal
       order by 5 desc )
       where rownum<10;  2    3    4    5    6    7    8

    OS_USERNAME          USERHOST                                           TERMINAL             USERNAME                  A
    -------------------- -------------------------------------------------- -------------------- -------------------- ------
    user                 WORKGROUP\EKMZ-041                                 EKMZ-041             BSUSER                13093
    Administrator        WORKGROUP\LIBO                                     LIBO                 BSUSER                12012
    Administrator        MSHOME\FCK-MB071                                   FCK-MB071            BSUSER                10953
    user                 WORKGROUP\NKMZ-FZ01                                NKMZ-FZ01            BSUSER                 7822
    Administrator                                                           ZZYX-MC187           BSUSER                   17
    user                 MSHOME\JIANHU-LJ1020                               JIANHU-LJ1020        BSUSER                   14
    Administrator        WORKGROUP\YBB-Z003                                 YBB-Z003             BSUSER                   13
    Owner                WORK\ZYSF                                          ZYSF                 BSUSERP                  12
    Administrator        WORKGROUP\REG2                                     REG2                 BSUSER                    8

    9 rows selected.



    ROW CACHE LOCK 等待的分析方法分析如下:
    1 ROW CACHE LOCK等待事件是一個共享池相關的等待事件。是由於對於字典緩衝的訪問造成的。
    P1 – Cache Id
    P2 – Mode Held
    P3 – Mode Requested

    Issues by Row Cache Enqueue Type

    --引起該問題的原因

    For each enqueue type, there are a limited number of operations that require each enqueue. The enqueue type therefore may give an indication as the type of operation that may be causing the issue. As such some common reasons are outlined below:

    DC_TABLESPACES
    Probably the most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

    DC_SEQUENCES
    Check for appropriate caching of sequences for the application requirements.

    DC_USERS
    Deadlock and resulting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

    DC_SEGMENTS
    This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

    DB_ROLLBACK_SEGMENTS
    This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks. Remember that on a multi-node system (RAC) the holder may be on another node and so multiple systemstates from each node will be required.

    DC_AWR_CONTROL
    This enqueue is related to control of the Automatic Workload Repository. As such any operation manipulating the repository may hold this so look for processes blocking these.


    定位的辦法:
    --查詢row cache lock等待
    select event,p1  from v$session where  event= 'row cache lock' and status='ACTIVE';
     
    --查詢rowcache 名稱
    select * from v$rowcache where cache# =p1;


    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-755774/，如需轉載，請註明出處，否則將追究法律責任。

    相關文章