Oracle 性能优化-密码延时特性导致系统缓慢
Oracle 性能优化-密码延时特性导致系统缓慢
问题
：
NC
登录缓慢；
打开任意节点都特别慢；
问题现象：
查看后台 SQL 执行效率特别低，等待事件均为 row cache lock
解决方案
可能和11g密码延时特性有关，数据库设置 28401事件，重启数据库，解决此问题；
关于密码延时特性实验如下：
---密码连续输入次数为10次，同一个用户连续输入10次错误密码，用户会被锁定
select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS' and profile='DEFAULT';
结论：
在同一个 session 下，同一用户输入密码错误时，每次的登录延时都会延长，延时由 1 秒延时到 7 秒，错误 10 次，用户被锁；
如果在两个或多个 session 下，其中一个 session 一直以错误的密码连接数据库用户，其他 session 即使是以正常的密码连接相同的数据库用户，也会出现延时阻塞；
解决办法：
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS' and profile='DEFAULT';
--- 查看密码输入错误次数
select name,lcount from user$ where name='CHEN';
select name,lcount from user$ where name='CHEN';
即使正确的密码，也会出现密码延时问题
另一个会话，即使以正确的密码连接相同数据库用户，也会出现密码延时问题；
ALTER system SET event=' 28401 trace name context forever, level 1' scope=spfile;
再次测试，已经没有延时现象了；
---ALTER system SET event='28401 trace name context off' scope=spfile;
|
Bug 7715339 - Logon failures causes "row cache lock" waits - Allow disable of logon delay ( 文档 ID 7715339.8) |
|
|
|
Bug 7715339 Logon failures causes "row cache lock" waits - Allow disable of logon delay
This note gives a brief overview of
bug 7715339.
Affects:
Fixed:
Description In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay . This can lead to excessive row cache lock waits for DC_USERS for specific users / schemas .
This "fix" allows the logon delay to be disabled in 11.2.0.1 onwards by setting event 28401 in the init.ora. eg: event="28401 trace name context forever, level 1" # disable logon delay. This "event" will disable the logon sleep delay system-wide, ie. it will affect all user accounts, system-wide, and so should be used with extreme caution.
Example scenario: A mix of correct and incorrect logon attempts occur for user X On each successive failed login attempt the failed logon count is incremented for user X.
Without this fix (without the event set): After 3 successive failures a sleep delay is introduced starting at 1 second and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).
With the fix (with the event set): There is no sleep delay.
In either scenario the configured logon profile rules are still applied (eg: The profile option FAILED_LOGIN_ATTEMPTS is still honored and so if the account becomes locked due to exceeding this FAILED_LOGIN_ATTEMPTS then further attempts to log in will then correctly fail immediately with no delay).
Note: One off fixes for this issue for 11.1.0.7 do not need an event set - interim patches for 11.1 disable the delay unconditionally.
Work Around: Ensure the correct password is used - especially for connection intensive logons
Getting a Fix Use one of the "Fixed" versions listed above (for Patch Sets / bundles use the latest version available as contents are cumulative - the "Fixed" version listed above is the first version where the fix is included) or You can check for existing interim patches here: Patch:7715339
References
Bug:7715339
(This link will only work for
PUBLISHED bugs)
|
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29785807/viewspace-2124332/ ，如需转载，请注明出处，否则将追究法律责任。
