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;
EVENT COUNT(0)
---------------------------------------------------------------- ----------
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 🙂

Cheers

Regards,

Adityanath