Multiple failed login attempts can cause high number of row cache lock wait events in 11g

From Oracle 11g, there is a new security enhancement that has potential of creating huge performance issues due to failed login attempts through multiple sessions.

Yesterday we faced this issue on production database, all application users were complaining about slow performance especially while making new connections with database.

After having quick check with v$session, I found there are huge number of sessions waiting on event : row cache lock.

select event,count(0) from v$session
where username is not null
and status='ACTIVE'
group by event
order by 2 desc;
---------------------------------------------------------------- ----------
row cache lock 137
class slave wait 4
direct path read 3
db file sequential read 2
db file scattered read 1
SQL*Net message to client 1

Row cache lock ???

After searching on MOS I found following note :

Bug 7715339 – Logon failures causes “row cache lock” waits – Allow disable of logon delay (Doc ID 7715339.8).

As per this bug: 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 .

After 3 successive failures a sleep delay is introduced starting at 3 seconds 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).

This can severely impact applications as any new connection would not be possible. All existing sessions will continie to work without issue.

In AUD$ we found that , one of the application schema was with high number of failed login attempts , all of which were done through database link from other database.

After troubleshooting issues with database link, database performance was back to normal.

Hope so u will find this post very useful 🙂




6 replies

  1. Woah! I’m really loving the template/theme of this blog. It’s simple, yet effective. A lot of times it’s tough to get that “perfect balance” between usability and appearance. I must say you’ve done a excellent job with this. In addition, the blog loads extremely quick for me on Firefox. Outstanding Blog!

  2. Heya i am for the first time here. I came across this board and I find It truly
    useful & it helped me out a lot. I hope to give something back and aid others like you helped me.

  3. Great post and right to the point. I am not sure if this is in fact the best place to ask but do you people have any thoughts on where to employ some professional writers? Thx 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s