之前分享过12c后关于用户登录的新特性笔记《Oracle 12c New Feature: Last Login Time for Non-Sys Users》, 此特性对于分别哪些用户长时间未登录(不使用)非常有用，从是从12c开始一直到当前最新的RU19.12 ，都没有在RU中打包关闭该特性的方法。《library cache lock或row cache lock, Failed Logon Delay 因为错误的密码尝试》也有记录登录常见的几个性能问题的event, 前几年在12c就遇到过较高的library cache lock是与该特性有关，用户无法登录，当时的SQL 更新user$, namespace为Last_Successful_Logon_Time，此问题特性只到21C base版本才引入了隐藏参数来禁用。 下面继续我们《Oracle19c避雷系列》的主动规避此特性。
Top Event P1/P2/P3 Values
- Top Events by DB Time and the top P1/P2/P3 values for those events.
- % Event is the percentage of DB Time due to the event
- % Activity is the percentage of DB Time due to the event with the given P1,P2,P3 Values.
| Event | % Event | P1, P2, P3 Values | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|---|---|---|
| library cache lock | 91.75 | “7810470960”,”23613858232″,”8323074“ | 0.68 | handle address | lock address | 100*mode+namespace |
| enq: TX – row lock contention | 8.22 | “1415053318”,”4522007″,”24103″ | 8.22 | name|mode | usn<<16 | slot | sequence |
上图AWR,ASH中的TOP EVENT,也可以写SQL从ASH view中分析
select p3,count(*) from v$active_session_history where event like 'library cache lock%' and ....
group by p3
SQL> select round(bitand( 8323074,to_number('FF0000','xxxxxxxxx'))/to_number('FFFF','xxxxxxxxx')) from dual;
ROUND(BITAND(8323074,TO_NUMBER('FF0000','XXXXXXXXX'))/TO_NUMBER('FFFF','XXXXXXXXX'))
------------------------------------------------------------------------------------
127
SQL> select KGLSTDSC from X$KGLST where indx=127;
KGLSTDSC
----------------------------------------------------------------
Last_Successful_Logon_Time
SQL> @desc dba_users;
Name Null? Type
------------------------------- -------- ----------------------------
1 USERNAME NOT NULL VARCHAR2(128)
2 USER_ID NOT NULL NUMBER
3 PASSWORD VARCHAR2(4000)
4 ACCOUNT_STATUS NOT NULL VARCHAR2(32)
5 LOCK_DATE DATE
6 EXPIRY_DATE DATE
7 DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
8 TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
9 LOCAL_TEMP_TABLESPACE VARCHAR2(30)
10 CREATED NOT NULL DATE
11 PROFILE NOT NULL VARCHAR2(128)
12 INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
13 EXTERNAL_NAME VARCHAR2(4000)
14 PASSWORD_VERSIONS VARCHAR2(17)
15 EDITIONS_ENABLED VARCHAR2(1)
16 AUTHENTICATION_TYPE VARCHAR2(8)
17 PROXY_ONLY_CONNECT VARCHAR2(1)
18 COMMON VARCHAR2(3)
19 LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
20 ORACLE_MAINTAINED VARCHAR2(1)
21 INHERITED VARCHAR2(3)
22 DEFAULT_COLLATION VARCHAR2(100)
23 IMPLICIT VARCHAR2(3)
24 ALL_SHARD VARCHAR2(3)
25 PASSWORD_CHANGE_DATE DATE
查看该dba_users定义LAST_LOGIN对应
SQL> select from_tz(to_timestamp(to_char(u.spare6, 'DD-MON-YYYY HH24:MI:SS'),
2 'DD-MON-YYYY HH24:MI:SS'), '0:00')
3 at time zone sessiontimezone from user$ u;
做SSD trace中可以看到blocking的session current SQL
update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
解决方法安装One-off Patch 32164034后引入新的隐藏参数_disable_last_successful_login_time ，再配置该参数为true. 禁用该特性，此patch 在12c和19c中当前可以下载，未提供18c patch. 21c base中已经存在。
SQL> @i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS CDB$ROOT-anbob21c oel7db1 1 1 54741 21.0.0.0.0 20210821 11143 43 11000 0000000077741858 00000000775910E0
SQL> @pd login_time
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
802 322 _disable_last_successful_login_time FALSE Disable writing Last Successful Login Time to USER$
— enjoy —