row cache

This latch comes into play when user processes are attempting to access or update the cached data dictionary values.

Solutions

Problem:

To determine if the row cache is being used efficiently, execute the following SQL. If the ratio is not close to 1 then some tuning required.

SELECT parameter, sum(gets), sum(getmisses),
100*sum(gets – getmisses) / sum(gets) pct_succ_gets,
sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;

Options for tuning row cache are so limited that some resources call it non-tunable. However if the value of the column pct_succ_gets in the above query is not close to “1” or if Ignite for Oracle shows significant waits for the “Row Cache Objects” latch, then consider increasing SHARED_POOL_SIZE.

Another way to adjust the dictionary cache is to increase the library cache. Since the dictionary cache is a part of the library cache, the increase will indirectly increase the dictionary cache. Also, consider the following:

Using Locally Managed tablespaces for application objects, especially indexes, may decrease Row Cache locks.

Review and amend database logical design. For example, try to decrease the number of indexes on tables with frequent inserts.

一套AIX上的11.1.0.7系统，应用启动时出现大量row cache lock等待，具体的systemstate dump信息如下:

FILE VERSIONS
-----------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/database
System name: AIX
Node name: HA5_4ADB01
Release: 3
Version: 5
Machine: 000687C2D900
Instance name: HN4A1


TRACE FILE
---------------------------
Filename=HN4A1_ora_2061038.trc



Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle)
     Chain 1 Signature Hash: 0x75bdd0c
 [b] Chain 2 Signature: 'row cache lock'<='row cache lock' (cycle)
     Chain 2 Signature Hash: 0x75bdd0c
 [c] Chain 3 Signature: 'row cache lock'<='row cache lock' (cycle)
     Chain 3 Signature Hash: 0x75bdd0c
 
===============================================================================
Cycles:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (hn4a.hn4a1)
                   os id: 2114372
              process id: 211, oracle@HA5_4ADB01
              session id: 610
        session serial #: 5
    }
    is waiting for 'row cache lock' with wait info:
    {
                      p1: 'cache id'=0x7
                      p2: 'mode'=0x0
                      p3: 'request'=0x5
            time in wait: 0.491964 sec
      heur. time in wait: 26.859741 sec
           timeout after: 2.508036 sec
                 wait id: 311
                blocking: 1 session
            wait history:
              1.     event: 'row cache lock'
                   wait id: 310             p1: 'cache id'=0x7
               time waited: 2.929713 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              2.     event: 'row cache lock'
                   wait id: 309             p1: 'cache id'=0x7
               time waited: 2.929726 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              3.     event: 'row cache lock'
                   wait id: 308             p1: 'cache id'=0x7
               time waited: 2.929720 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (hn4a.hn4a1)
                   os id: 2118476
              process id: 209, oracle@HA5_4ADB01
              session id: 616
        session serial #: 5
    }
    which is waiting for 'row cache lock' with wait info:
    {
                      p1: 'cache id'=0x7
                      p2: 'mode'=0x0
                      p3: 'request'=0x5
            time in wait: 0.491910 sec
      heur. time in wait: 26.859705 sec
           timeout after: 2.508090 sec
                 wait id: 308
                blocking: 1 session
            wait history:
              1.     event: 'row cache lock'
                   wait id: 307             p1: 'cache id'=0x7
               time waited: 2.929713 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              2.     event: 'row cache lock'
                   wait id: 306             p1: 'cache id'=0x7
               time waited: 2.929729 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              3.     event: 'row cache lock'
                   wait id: 305             p1: 'cache id'=0x7
               time waited: 2.929715 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
    }
    and is blocked by the session at the start of the chain.
 
Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle)
Chain 1 Signature Hash: 0x75bdd0c

...==>many similar chains

PROCESS 211:
  ----------------------------------------
  SO: 0x7000008a1370178, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x7000008a1370178, name=process, file=ksu.h LINE:10706 ID:, pg=0
  (process) Oracle pid:211, ser:3, calls cur/top: 0x7000008ac61a0e8/0x7000008ac61a0e8
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x0)
            int error: 0, call error: 0, sess error: 0, txn error 0
  ksudlp FALSE at location: 0
  (post info) last post received: 0 0 201
              last post received-location: kqr.h LINE:2181 ID:kqrbgl: compatible mode
              last process to post me: 70000089134b800 1 6
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x7000008a153a4c8
    O/S info: user: oracle, term: UNKNOWN, ospid: 2114372
    OSD pid info: Unix process pid: 2114372, image: oracle@HA5_4ADB01
    Short stack dump:
<-ksedsts()+0254<-ksdxfstk()+0028<-ksdxcb()+05d8<-sspuser()+0074<-44c0<-skgpwwait()+00b8<-ksliwat()+0c48<-kslwaitctx()+0150<-kqrigt()+06ac<-kqrLockAndPinPo()+0250<-kqrpre1()+061c<-kqrpre()+001c<-kziavua()+0384<-kpolnb()+0ffc<-kpoauth()+07a0<-opiodr()+0b98<-ttcpip()+115c<-opitsk()+1700<-opiino()+09f0<-opiodr()+0b98<-opidrv()+0440<-sou2o()+0090<-opimai_real()+01b0<-main()+0090<-__start()+0098


    service name: SYS$USERS
    client details:
      O/S info: user: aiuap, term: unknown, ospid: 1234
      machine: HA5-4A26 program: JDBC Thin Client
      application name: JDBC Thin Client, hash value=2546894660
...
         proc=0x7000008a1370178, name=row cache enqueues, file=kqr.h LINE:2004 ID:, pg=0
        row cache enqueue: count=1 session=7000008916dee00 object=7000008dfd84428, request=X
        savepoint=0x3e
        row cache parent object: address=7000008dfd84428 cid=7(dc_users)
        hash=481062cf typ=21 transaction=700000888da17a0 flags=00000002
        own=7000008dfd844f0[7000008bf6e18f0,7000008bf6e18f0] wat=7000008dfd84500[7000008bf5d2750,7000008ef34e818] mode=X
        status=VALID/-/-/-/-/-/-/-/-
        request=N release=FALSE flags=8
        instance lock=QK 481062cf bb6f8fe9
...
      LIBRARY OBJECT LOCK: 7000008ef5069d8 handle=7000008dfd22338 mod=N
      pnc=0 pns=0 cbb=1 rpr=1 exc=1 ilh=0 ctx=0
      use=7000008916dee00 ses=7000008a15c44f0 cnt=1 flg=CNB/[0001] spn=0x4c3d09ea
      LIBRARY HANDLE:7000008dfd22338 bid=105620 hid=a3c59c94 lmd=N pmd=0 sta=VALD
      name=select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
      hash=8973f7c629f09e3f081962c2a3c59c94 idn=0

众多wait chain的源头均是row cache lock，systemstate转储中该等待事件相关的p1，也就是cache id均为7；cache#为7的字典缓存代表着dc_users，即字典中的用户信息(包括用户名，密码等信息):
[Read more…]

latch: row cache objects等待事件

Row Cache lock Problem

Oracle内部错误ORA-600:[1112]