原文地址: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.
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;