Oracle11g的delayed failed logins特性引起的性能问题

用户反映修改密码后程序明显变慢，查看AWR发现:

 

ASH信息如下：

 

进一步验证:

SQL>select event,p1 from v$session t where t.username is not null and t.status='ACTIVE';

EVENT                                                                    P1

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

row cache lock                                                            7

row cache lock                                                            7

row cache lock                                                            7

row cache lock                                                            7

 

查询v$rowcache

SQL> select parameter from v$rowcache where cache#=7;

PARAMETER

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

dc_users

dc_users

dc_users

 

 

经查阅资料，在10.2.0.5及以后版本，使用错误密码登陆尝试会导致很高的Library Cache Locks或row cache lock，可以设置Oracle的隐含参数28401来避免：

alter system set event='28401 trace name context forever,level 1' scope=spfile;

 

补充：

oracle 在12c以后，用户密码错误连续登陆数据库等待事件不再是ibrary cache lock 而是Failed Logon Delay，同时也启用了新的隐含参数“_sys_logon_delay”控制。
经测试在19c中将“_sys_logon_delay”=0设置为0可以解决此问题，在12C中没有测试感觉应该也一样。
alter system set “_sys_logon_delay”=0 scope=spfile;

 

官方参考如下：

https://blogs.oracle.com/Database4CN/entry/%E9%94%99%E8%AF%AF%E7%9A%84%E7%94%A8%E6%88%B7%E5%90%8D%E5%AF%86%E7%A0%81%E7%99%BB%E5%BD%95%E5%AF%BC%E8%87%B4%E7%9A%84%E6%95%B0%E6%8D%AE%E5%BA%93%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98

 

通过审计功能分析是哪台机器哪个用户的频繁登陆失败

select *
  from (select os_username, userhost, terminal, username, count(*) cnt
          from dba_audit_trail
         where returncode = 1017
           and timestamp >= date '2015-12-13'
         group by os_username, userhost, username, terminal
         order by 5 desc)
 where rownum < 10;

 

有时我们一台机器上部署了多个应用（服务），需要确认是哪一个进程在使用错误的密码访问Oracle，这里我们可以借助Oracle的数据库触发器记录日志

create table tmp_logon_denied
(
   id varchar2(36),
   ip varchar2(50),
   osuser varchar2(100),
   loginuser varchar2(100),
   machine varchar2(100),
   program varchar2(100),
   timestamp date,
   primary key (id)
);
/

CREATE OR REPLACE TRIGGER logon_denied_to_alert
  AFTER servererror ON DATABASE
DECLARE
  message   VARCHAR2(168);
  ip        VARCHAR2(15);
  v_os_user VARCHAR2(80);
  v_module  VARCHAR2(50);
  v_action  VARCHAR2(50);
  v_pid     VARCHAR2(10);
  v_sid     NUMBER;
  v_program VARCHAR2(48);
BEGIN
  IF (ora_is_servererror(1017)) THEN

    -- get ip FOR remote connections :
    IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
      ip := sys_context('userenv', 'ip_address');
    END IF;

    SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
    SELECT p.spid, v.program
      INTO v_pid, v_program
      FROM v$process p, v$session v
     WHERE p.addr = v.paddr
       AND v.sid = v_sid;

    v_os_user := sys_context('userenv', 'os_user');
    dbms_application_info.read_module(v_module, v_action);

    message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
               ' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
               v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
               v_module || ' ' || v_action;

    --sys.dbms_system.ksdwrt(2, message);
    
    insert into tmp_logon_denied(id, ip, osuser, loginuser, machine, program, timestamp)
    select sys_guid(), ip, t.OSUSER, t.USERNAME, t.MACHINE, t.PROGRAM, sysdate
    from v$session t
    where t.SID = v_sid;
    
  END IF;
END;
/