[ORACLE 11G]ROW CACHE LOCK 等待

原文地址：http://blog.itpub.net/15747463/viewspace-755774/

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

数据库版本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;