High 'library cache lock' Wait Time Due to Invalid Login Attempts

High 'library cache lock' Wait Time Due to Invalid Login Attempts (文件 ID 1309738.1)

Applies to:

Symptoms

• 'Library cache lock' or 'row cache lock' can be observed when concurrent users login with wrong password to the database.
• The 'row cache lock' is seen in 10.2 and 11.1 while the 'library cache lock' is seen in 11.2.
• ASH Report displays
  • High Percentage of execution time attributed to Connection Management:
    
    
    
  • High percentage of calls of type OAUTH
    
    
    
    
• Stack contains one of the following functions: 
  
  kziavua
  kziaia  
  kziasfc
   
• Checking the exclusive holder from DBA_DDL_LOCKS,  a session may be seen holding a lock type (kglhdnsp) 79 on object (kglnaobj) 5:
  

  SQL> select * from dba_ddl_locks where mode_held='Exclusive';

   SESSION_ID OWNER     NAME       TYPE       MODE_HELD MODE_REQU

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

         612           5          79         Exclusive None

• If AUDIT_TRAIL is enabled, login failures can be checked by running SQLs similar to the following:
  

  Checks for entries in the last 7 days in DBA_AUDIT_TRAIL with error ORA-1017  invalid username/password; logon denied

  select username, os_username, userhost, client_id, trunc(timestamp), count(*) fa
  iled_logins
  from  dba_audit_trail
  where returncode = 1017
  and timestamp > sysdate - 7
  group by username, os_username, userhost, client_id, trunc(timestamp);

  
  Checks for entries in the last 7 days in DBA_AUDIT_SESSION where an error was returned

  select username, os_username, userhost, timestamp, returncode
  from sys.dba_audit_session
  where returncode != 0
  and timestamp > sysdate - 7;

  
  

Changes

Many users with wrong password try to login to the database simultaneously

Cause

A hang is possible in earlier versions of RDBMS as a result of an unpublished bug fixed in the following versions:

    12.1.0.1 (Base Release)
    11.2.0.2 (Server Patch Set)
    11.1.0.7 Patch 42 on Windows Platforms

Even with this fix, numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.

This was reported in:

This was closed as not a bug because there is an intentional wait when a login fails.

Solution

In Oracle 11g Release 11.1.0.7,  the wait is disabled unconditionally

In Oracle 11g Release 2 and higher, in order to disable the wait between login failures the event 28401 needs to be explicitly enabled:

The event can be set as follows:

For more information see:

For databases using MTS, a further enhancement has been created in: 

as setting the event is not as effective as it is for dedicated systems. The following enhancement is included in 12.2:

"The failed login counter will be disabled entirely for any user that has the setting UNLIMITED for their account's FAILED_LOGIN_ATTEMPTS password profile setting."

References