首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle19c 建议的 One-off patch之disable LAST SUCCESSFUL LOGIN TIME

Oracle19c 建议的 One-off patch之disable LAST SUCCESSFUL LOGIN TIME

之前分享过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 —

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：