最近处理一起apply 202101RU 后Last_Successful_Logon_Time导致的library cacha lock，详细如下：

问题描述

一套12.2 for x86-64的生产库，突然收到告警active会话暴增。几百的活动会话增涨到4k多。当时是紧急停应用处理的。

问题分析

首先排查故障时间段ash分布情况,大量的liarbry cache lock 被enq: US - contention阻塞。

TO_CHAR(A.SAMPL SESSION_ID EVENT                                         BLOCKING_SESSION BLOCK_EVENT                                                        COUNT(*)
--------------- ---------- --------------------------------------------- ---------------- ---------------------------------------------------------------- ----------
20210602  15:34       5550 library cache lock                                       39360 enq: US - contention                                                   8607
20210602  15:34      15360 library cache lock                                       39360 enq: US - contention                                                   8607
20210602  15:34      17262 library cache lock                                       39360 enq: US - contention                                                   8607
20210602  15:34      38410 library cache lock                                       39360 enq: US - contention                                                   8607
20210602  15:34      25213 library cache lock                                       39360 enq: US - contention                                                   8607
20210602  15:34       5591 library cache lock                                       29866 enq: US - contention                                                   9918
20210602  15:34      27346 library cache lock                                       29251 enq: US - contention                                                   9918
20210602  15:34      36869 library cache lock                                       29251 enq: US - contention                                                   9918
20210602  15:34      46696 library cache lock                                       29251 enq: US - contention                                                   9918
20210602  15:34      41529 library cache lock                                       29866 enq: US - contention                                                   9918
20210602  15:34       7384 library cache lock                                       29251 enq: US - contention                                                   9918

TO_CHAR(A.SAMPL SESSION_ID EVENT                                         BLOCKING_SESSION BLOCK_EVENT                                                        COUNT(*)
--------------- ---------- --------------------------------------------- ---------------- ---------------------------------------------------------------- ----------
20210602  15:34      24261 library cache lock                                       29251 enq: US - contention                                                   9918
20210602  15:34      50103 library cache lock                                       29251 enq: US - contention                                                   9918
20210602  15:34      30175 library cache lock                                       29866 enq: US - contention                                                   9918
复制

查看下blocking session动作：

SQL_ID        EVENT                                              PROGRAM                                  MODULE                                   MACHINE
------------- -------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
9zg9qd9bm4spu enq: US - contention                               JDBC Thin Client                         JDBC Thin Client                         wcapes1
9zg9qd9bm4spu enq: US - contention                               JDBC Thin Client                         JDBC Thin Client                         9579a8402f9a
9zg9qd9bm4spu enq: US - contention                               JDBC Thin Client                         JDBC Thin Client                         scbke1814
9zg9qd9bm4spu enq: US - contention                               oracle@wkhcsdb2 (TNS V1-V3)              oracle@wkhcsdb2 (TNS V1-V3)              wkhcsdb2

70 rows selected.


SYS@dddb1>select sql_text from v$sql where sql_id='9zg9qd9bm4spu';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
复制

一条update user的sql导致的阻塞。为什么登录会有大量的upate user动作呢。
通过library cache lock p3值继续进行分析：

SYS@dddb1>select to_char(p3,'xxxxxxxxxxxxxx') p3hex,count(*) from  v$active_session_history where event='library cache lock' 
  2  and SAMPLE_TIME>=to_date('2021-06-02 15:30:00','yyyy-mm-dd hh24:mi:ss') 
  3  and SAMPLE_TIME<=to_date('2021-06-02 15:35:00','yyyy-mm-dd hh24:mi:ss') 
  4  group by to_char(p3,'xxxxxxxxxxxxxx');

P3HEX             COUNT(*)
--------------- ----------
         7f0002      22846

SYS@dddb1>set linesize 500
SYS@dddb1>select KGLHDNSP,KGLHDNSD from x$kglob where KGLHDNSP=(select to_number('7f','xx') from dual);  

  KGLHDNSP KGLHDNSD
---------- --------------------------------------------------------------------------------------------------------------------------------
       127 Last_Successful_Logon_Time
复制

Last_Successful_Logon_Time是12c一个特性，记录用户的最后登录时间。这个特性导致的library cache lock。

处理思路

类似这种oracle 内部sql，可以先从mos、bug入手。此环境一个月前 apply 2101 RU+OJVM patch.应用补丁前没有过类似的问题。而oracle 从202010 RU之后，禁止直接对user基表直接进行修改，会不会有新的bug。果然，提sr之后确认这是一个比较新的补丁或高版本才会触发的未公开bug。并且在12c中不在进行修复以及发布bug 补丁。将在21c基础版本和19c后续的补丁中解决，解决方案是'_disable_last_successful_login_time'引进一个隐含参数，关闭update user的动作，从而规避该问题。