Latch row cache object问题分析

客户的系统,先后发生了几次Latch:row cache object等待,该等待会导致CPU迅速达到100%。

SQL> select event,p1,P1RAW from v$session where event='latch: row cache objects';
EVENT                                                P1 P1RAW
-------------------------------------------- ---------- ----------------
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918
latch: row cache objects                     5.0440E+17 07000018A74C3918

我们可以从p1raw的值中可以看到,几乎全部的p1raw等待的地址都是07000018A74C3918。继续通过这个地址查询基表,我们发现全部的等待都集中在数据字典dc_user上。

SQL> SELECT kqrsttxt PARAMETER, kqrstcid CACHE#, kqrstcln "Lchild#", kqrstgrq "DCGets", l.gets   "LGets", l.misses "Misses"  FROM X$KQRST, V$LATCH_CHILDREN l WHERE l.addr='07000018A74C3918' and l.child#=KQRSTCLN ORDER BY 1,2;
PARAMETER            CACHE#    Lchild#     DCGets      LGets     Misses
--------------------------- ---------- ---------- ---------- ----------
dc_users                  7          7 -432944865 2494354363 3396983919
dc_users                  7          7          0 2494354363 3396983919
dc_users                  7          7 -488978240 2494354363 3396983919
dc_users                  7          7    6423076 2494354363 3396983919

出问题时间段AWR报告的情况。latch: row cache objects占据了整个等待的53.5%

Top 5 Timed Events

Event

Waits

Time(s)

Avg Wait(ms)

% Total Call Time

Wait Class

latch: row cache objects

3,074,567

292,212

95

53.5

Concurrency
CPU time

147,945

27.1

db file sequential read

4,342,112

37,436

9

6.9

User I/O
gc buffer busy

2,573,835

10,621

4

1.9

Cluster
read by other session

493,143

2,536

5

.5

User I/O

数据字典使用情况

Dictionary Cache Stats

  • “Pct Misses” should be very low (< 2% in most cases)
  • “Final Usage” is the number of cache entries being used

Cache

Get Requests

Pct Miss

Scan Reqs

Pct Miss

Mod Reqs

Final Usage

dc_awr_control

148

0.00

0

2

1

dc_database_links

2,361

0.00

0

0

1

dc_files

36,460

0.00

0

0

3,646

dc_global_oids

2,833

0.28

0

0

30

dc_histogram_data

36,128

3.26

0

0

30,437

dc_histogram_defs

54,038

3.81

0

0

28,382

dc_object_grants

652

3.07

0

0

65

dc_object_ids

94,802

0.32

0

0

4,453

dc_objects

8,349

3.03

0

59

2,702

dc_profiles

1,176

0.00

0

0

2

dc_rollback_segments

961,666

0.00

0

0

925

dc_segments

23,648

39.61

0

1,535

69,506

dc_sequences

739

82.14

0

739

224

dc_tablespace_quotas

4,865

4.07

0

0

162

dc_tablespaces

149,688

0.00

0

0

229

dc_usernames

24,338

0.00

0

0

979

dc_users

121,991,028

0.00

0

0

2,929

outstanding_alerts

2,735

97.95

0

6

452

 

正常时间段AWR报告

Top 5 Timed Events

Event

Waits

Time(s)

Avg Wait(ms)

% Total Call Time

Wait Class

db file sequential read

5,091,217

41,713

8

65.6

User I/O

CPU time

18,880

29.7

gc buffer busy

1,098,325

4,530

4

7.1

Cluster

db file scattered read

269,197

1,225

5

1.9

User I/O

db file parallel write

1,113,985

1,060

1

1.7

System I/O

正常时间段数据字典使用情况

Dictionary Cache Stats

  • “Pct Misses” should be very low (< 2% in most cases)
  • “Final Usage” is the number of cache entries being used

Cache

Get Requests

Pct Miss

Scan Reqs

Pct Miss

Mod Reqs

Final Usage

dc_awr_control

67

1.49

0

1

1

dc_database_links

2,801

0.00

0

0

1

dc_files

76,566

0.00

0

0

3,646

dc_global_oids

1,926

0.00

0

0

22

dc_histogram_data

22,093

5.27

0

0

29,261

dc_histogram_defs

26,514

6.77

0

0

27,758

dc_object_grants

96

0.00

0

0

45

dc_object_ids

91,305

0.15

0

0

4,229

dc_objects

5,241

1.74

0

74

2,599

dc_profiles

1,396

0.00

0

0

2

dc_rollback_segments

376,713

0.00

0

0

925

dc_segments

22,509

55.27

0

1,992

60,544

dc_sequences

676

86.09

0

676

243

dc_tablespace_quotas

6,815

2.61

0

0

163

dc_tablespaces

83,635

0.00

0

0

229

dc_usernames

22,585

0.00

0

0

978

dc_users

69,522,674

0.00

0

0

2,928

outstanding_alerts

2,730

98.02

0

0

5

 

经过正常时间段和异常时间段相比,发现dc_users的request的数量从69522674上升到了121991028次,多了一倍,正好和我们前面查询出的结果吻合。

通过MOS文档Heavy “latch: row cache objects” Contention on dc_objects and dc_users when Using Roles and System Level Grants (文档 ID 1639293.1)中间的描述。

Since a set of privileges grouped together in a role cannot be static and can change frequently, the role cannot be cached as a library_cache object. Additionally, because the role includes system level privileges it needs to be looked up during every execution of the cursor irrespective of whether it is cached or not.

Under heavy concurrent execution this can lead to high numbers or lookups for object privileges and cause ‘latch: row cache objects’ contention

If you are granting ‘GRANT ANY …’ privileges to users then, because of an optimization introduces in Bug 10023443, if you have thousands of different users executing RPCs without direct privileges then a large set of validation data has to be scanned taking a lot of time and leading to high row cache contention. Granting the privileges directly to the object avoids this contention.

Solution

If these symptoms occur, explicitly grant access to the underlying object to the user (not through the role):

这里提到如果用户不是当前的用户,而大批量执行会导致大量对对象权限的检查,引起latch:row cache objects,一个有效的避免办法就是直接赋予对象授权。

我们在看高并发一个时间段具体的情况,这里可以看到user_id集中在120上,总共有69个会话同一时间发起对不同对象的请求。

SQL> select USER_ID,to_char(sample_time,'YYYY-MM-DD HH24:MI:ss') atime,event,CURRENT_OBJ#
from dba_hist_active_sess_history where SNAP_ID >46342 and SNAP_ID<=46343 
and event='latch: row cache objects' and to_char(sample_time,'YYYY-MM-DD HH24:MI:ss')='2014-12-31 10:53:41' and CURRENT_OBJ# not in (-1,0);

   USER_ID ATIME               EVENT                                                            CURRENT_OBJ#
---------- ------------------- ---------------------------------------------------------------- ------------
       120 2014-12-31 10:53:41 latch: row cache objects                                              7956834
       120 2014-12-31 10:53:41 latch: row cache objects                                              7956834
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107750
       120 2014-12-31 10:53:41 latch: row cache objects                                              8435865
       120 2014-12-31 10:53:41 latch: row cache objects                                              7956834
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107765
       120 2014-12-31 10:53:41 latch: row cache objects                                              1855183
       120 2014-12-31 10:53:41 latch: row cache objects                                              8431388
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107772
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107741
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107747
       123 2014-12-31 10:53:41 latch: row cache objects                                              4924792
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107751
       123 2014-12-31 10:53:41 latch: row cache objects                                              5199531
       120 2014-12-31 10:53:41 latch: row cache objects                                              8431388
       120 2014-12-31 10:53:41 latch: row cache objects                                              8431389
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107762
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107770
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107758
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107762
       103 2014-12-31 10:53:41 latch: row cache objects                                              5138768
       123 2014-12-31 10:53:41 latch: row cache objects                                              4924792
       120 2014-12-31 10:53:41 latch: row cache objects                                              8428997
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107770
       120 2014-12-31 10:53:41 latch: row cache objects                                              8431319
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107749
       123 2014-12-31 10:53:41 latch: row cache objects                                              8470682
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107764
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107747
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107771
       120 2014-12-31 10:53:41 latch: row cache objects                                              8435513
       120 2014-12-31 10:53:41 latch: row cache objects                                              8472729
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107765
       123 2014-12-31 10:53:41 latch: row cache objects                                              2540191
       120 2014-12-31 10:53:41 latch: row cache objects                                              1882550
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107771
       120 2014-12-31 10:53:41 latch: row cache objects                                              6916742
       120 2014-12-31 10:53:41 latch: row cache objects                                              8428997
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107766
       120 2014-12-31 10:53:41 latch: row cache objects                                              1886171
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107767
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107754
       120 2014-12-31 10:53:41 latch: row cache objects                                              8164310
       120 2014-12-31 10:53:41 latch: row cache objects                                              1882543
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107752
       120 2014-12-31 10:53:41 latch: row cache objects                                              1855381
       120 2014-12-31 10:53:41 latch: row cache objects                                              1248853
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107726
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107756
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107750
       103 2014-12-31 10:53:41 latch: row cache objects                                              8427924
       120 2014-12-31 10:53:41 latch: row cache objects                                              8435865
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107754
       120 2014-12-31 10:53:41 latch: row cache objects                                              8428997
       120 2014-12-31 10:53:41 latch: row cache objects                                              8431388
       120 2014-12-31 10:53:41 latch: row cache objects                                              1855351
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107766
       120 2014-12-31 10:53:41 latch: row cache objects                                              1889082
       120 2014-12-31 10:53:41 latch: row cache objects                                              7624584
       120 2014-12-31 10:53:41 latch: row cache objects                                              8428997
       120 2014-12-31 10:53:41 latch: row cache objects                                              1855718
       123 2014-12-31 10:53:41 latch: row cache objects                                              4924792
       120 2014-12-31 10:53:41 latch: row cache objects                                              2540227
       120 2014-12-31 10:53:41 latch: row cache objects                                              8435865
       120 2014-12-31 10:53:41 latch: row cache objects                                              1905505
       120 2014-12-31 10:53:41 latch: row cache objects                                              3026654
       123 2014-12-31 10:53:41 latch: row cache objects                                              8157538
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107742
       120 2014-12-31 10:53:41 latch: row cache objects                                              8107747
69 rows selected.

查询user_id为120用户的权限情况

SQL> select username from dba_users where user_id=120; 
USERNAME
------------------------------
TUX_INTRFC

SQL> select * from dba_sys_privs where GRANTEE='TUX_INTRFC';

GRANTEE         PRIVILEGE                 ADMIN_
--------------- ------------------------- ------
TUX_INTRFC      SELECT ANY SEQUENCE       NO
TUX_INTRFC      ALTER ANY SEQUENCE        NO
TUX_INTRFC      ALTER SESSION             NO
TUX_INTRFC      CREATE SEQUENCE           NO
TUX_INTRFC      CREATE ANY INDEX          NO
TUX_INTRFC      SELECT ANY TABLE          NO
TUX_INTRFC      ALTER ANY TABLE           NO
TUX_INTRFC      CREATE ANY TABLE          NO
TUX_INTRFC      SELECT ANY DICTIONARY     NO
TUX_INTRFC      EXECUTE ANY INDEXTYPE     NO
TUX_INTRFC      ALTER ANY INDEX           NO
TUX_INTRFC      LOCK ANY TABLE            NO
TUX_INTRFC      CREATE ANY SEQUENCE       NO
TUX_INTRFC      DELETE ANY TABLE          NO
TUX_INTRFC      EXECUTE ANY PROCEDURE     NO
TUX_INTRFC      UPDATE ANY TABLE          NO
TUX_INTRFC      INSERT ANY TABLE          NO
TUX_INTRFC      UNLIMITED TABLESPACE      NO      

SQL> select * from dba_role_privs where GRANTEE='TUX_INTRFC';
GRANTEE         GRANTED_ROLE                                                 ADMIN_ DEFAUL
--------------- ------------------------------------------------------------ ------ ------
TUX_INTRFC      RESOURCE                                                     NO     YES
TUX_INTRFC      CONNECT                                                      NO     YES

SQL> select * from DBA_TAB_PRIVS where GRANTEE='TUX_INTRFC';
no rows selected

这里可以看到TUX_INTRFC用户只具备系统权限,而不具备对象权限。这就会导致文档1639293.1中描述的情况产生。

解决办法

首先查出具体问题时间段影响最多的用户。这里可以看到120用户是影响最多的。

SQL> select user_id,count(1) from dba_hist_active_sess_history where SNAP_ID >46342 and SNAP_ID<=46343
 and event='latch: row cache objects' group by user_id order by 2;

   USER_ID   COUNT(1)
---------- ----------
        53          1
       149          1
        71          2
       122          3
         0          4
        72          5
        74          5
        46          5
        70          6
        77          6
        75          6
        76          7
        73         10
        32         15
        64        107
        58        122
        62        124
        65        126
        59        127
        60        129
        66        130
        67        130
        55        131
        57        135
        69        136
        63        136
        56        140
        54        140
        68        146
        61        151
       103        217
       123       1573
       120      24998

出问题时间段120用户访问表对象。

SQL> select owner,object_name from 
  2  (
  3  select distinct CURRENT_OBJ#
  4  from dba_hist_active_sess_history where SNAP_ID >46342 and SNAP_ID<=46343
  5  and event='latch: row cache objects' and user_id=120 and CURRENT_OBJ# not in (1,0)
  6  ) b ,(select owner,object_name,object_type,object_id from dba_objects) a 
  7  where a.object_id = b.CURRENT_OBJ#
  8  and object_type='TABLE';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
FFPDUSER                       CP_KZH_MEMBER_INFO
FFPDUSER                       CP_KZH_FAMILY_INFO
FFPDUSER                       C_IDS_ID_CHGCON_TBL
FFPDUSER                       C_PRNT_CNTRCT_SUSPEND_CHNG_END
FFPDUSER                       C_IDS_ID_APP_POS_TBL
FFPDUSER                       C_IDS_PRE_CDELREC_TBL
FFPDUSER                       CP_GPS_RQSTING
FFPDUSER                       C_PRNT_SPEC_TERM_CHNG
FFPDUSER                       CP_CNTRCT_DETAIL
FFPDUSER                       CP_INTRFC_BIZUNIT_AUTH
FFPDUSER                       C_INTRFC_PREM_RENEW_TBL
FFPDUSER                       CT_QUA_EMPNO
FFPDUSER                       CP_FINANCIAL_DTL
FFPDUSER                       CP_PARTICIPATION
FFPDUSER                       CP_PAYROLL
FFPDUSER                       C_PRNT_ACCNT_STATEMENT
FFPDUSER                       CP_HISTORICAL_NOTE
FFPDUSER                       CP_CLAUSE
FFPDUSER                       C_IDS_ADDR_TBL
FFPDUSER                       C_IDS_SPREREC_TBL
FFPDUSER                       C_IDS_RISKCON_TBL
FFPDUSER                       CP_LIFE_SURVEY
FFPDCMS                        CP_ELECTRONIC_ADDR
FFPDUSER                       AGT_CREDIT_GRADE
FFPDUSER                       C_IDS_RISKCON_RENEW_TBL
FFPDUSER                       C_IDS_ACCTLIST_TBL
FFPDUSER                       C_IDS_PHONE_TBL
FFPDUSER                       C_IDS_PERSON_TBL
FFPDCPICDATA                   IDL_WL_PYEAR_PAY2010
FFPDUSER                       CP_PRSN_WEB
FFPDUSER                       C_APP_NB_TBL
FFPDUSER                       CP_ACCEPT_INFO
FFPDUSER                       C_PRNT_CNTRCT_REPAY_END
FFPDUSER                       C_PRNT_PRSN_MSG_CHNG_END
FFPDCPICDATA                   IDL_WL_PYEAR_PAY2009
FFPDCPICDATA                   IDL_WL_RISKBONUS_ENG
FFPDMASTER                     CD_CVRG_BENEFIT
FFPDMASTER                     CD_CLAUSE_LINK
FFPDMASTER                     CD_VARIABLE
FFPDUSER                       CP_CLOB_NO_DATE
FFPDUSER                       CP_CLOB_FULL_NO_PRPGT
FFPDUSER                       C_IDS_PARTY_BRANCH_RLSHIP_TBL
FFPDUSER                       CP_KZH_TJ_INQUIRY
FFPDUSER                       CP_KZH_TJ_PRODUCT
FFPDUSER                       C_INTRFC_WX_SMS_TBL
FFPDCMS                        CP_ORG
FFPDUSER                       CO_DISTRIBUTOR
FFPDUSER                       CP_CLAUSE_LINK
FFPDUSER                       CP_CVRG_BENEFIT_PAYOUT_DTL
FFPDUSER                       CP_DVDND_PAYOUT_DTL
FFPDUSER                       CP_LNK
FFPDUSER                       CP_LOAN
FFPDUSER                       CP_SECURITY_KEY_DISTRIBUTOR
FFPDUSER                       CP_STTLMNT_CVRG
FFPDUSER                       C_IDS_APP_NB_TBL
FFPDUSER                       C_IDS_MONEYSCH_TBL
FFPDUSER                       C_IDS_POLICYST_TBL
FFPDUSER                       C_IDS_TMPPAY_TBL

使用下列语法赋予相关对象权限

grant select on FFPDUSER.CP_KZH_MEMBER_INFO to TUX_INTRFC;
grant insert on FFPDUSER.CP_KZH_MEMBER_INFO to TUX_INTRFC;
grant update on FFPDUSER.CP_KZH_MEMBER_INFO to TUX_INTRFC;
grant delete on FFPDUSER.CP_KZH_MEMBER_INFO to TUX_INTRFC;
分享到: 更多