[20211013]19C 关于LAST SUCCESSFUL LOGIN TIME.txt

--//12c使用sqlplus登陆会显示上次登陆的时间.我开始以为仅仅sqlplus有这个功能，实际上其他管理工具toad登陆包括共享
--//模式登陆也一样.它会修改user$.spare6,我当时就提到oracle为什么要这样设计，如果大量的登陆，同时修改这个字段将
--//导致性能出现问题。

--//参考链接：
--//http://blog.itpub.net/267265/viewspace-2140256/=>[20170603]12C sqlplus 显示用户上次登录时间.txt
--//http://blog.itpub.net/267265/viewspace-2215493/=>[20181006]12c sqlplus显示用户上次登录时间.txt

SCOTT@test01p> select sql_id,executions,sql_text c120 from v$sqlarea where sql_id in ('9zg9qd9bm4spu','865qwpcdyggkk');
SQL_ID        EXECUTIONS C120
------------- ---------- ----------------------------------------------------------------------------------------------------------
9zg9qd9bm4spu          7 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
865qwpcdyggkk          7 select spare6 from user$ where user#=:1

--//19c开始提供参数控制这些行为：
--//链接：
--//https://www.anbob.com/archives/6411.html
--//https://www.anbob.com/archives/6444.html

_disable_last_successful_login_time

_disable_last_successful_login_time_unlimited_iat

– This controls whether  users associated with a password profile with UNLIMITED Inactive Account Time will not have
   their LSLT updated.

_granularity_last_successful_login_time

– This controls the granularity of LSLT update for _ALL_ users. Say, it is set to 30 seconds and then all users logons
   within that period of 30 seconds will NOT update the LSLT

--//不过这些隐含参数我们都用不上，我们19c版本太低了，不支持这些参数或者没有打补丁，仅仅做一个记录。

1.环境:
SYS@127.0.0.1:17101/PPPPP> @ ver
SYS@127.0.0.1:17101/PPPPP> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.检查:
SYS@127.0.0.1:17101/ppppp> @ tpt/sql_id 9zg9qd9bm4spu %
Show SQL text, child cursors and execution stats for SQLID 9zg9qd9bm4spu child %
  HASH_VALUE  CH# SQL_TEXT
------------ ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
  1462919866    1 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
  1462919866    4 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
  1462919866    6 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
  1462919866    9 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
  1462919866   10 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1

 CH# PARENT_HANDLE    OBJECT_HANDLE       PLAN_HASH       PARSES     H_PARSES   EXECUTIONS      FETCHES ROWS_PROCESSED       CPU_MS       ELA_MS         LIOS         PIOS        SORTS USERS_EXECUTING
---- ---------------- ---------------- ------------ ------------ ------------ ------------ ------------ -------------- ------------ ------------ ------------ ------------ ------------ ---------------
   1 000000015FD050F8 000000015E57C0C0   2447725225        43138           52        43139            0          43139    48445.564    44139.606       173733            0            0               0
   4 000000015FD050F8 000000015A7170F8   2447725225        59582           48        59582            0          59582    68945.436    63918.485       239963            0            0               0
   6 000000015FD050F8 000000015A1C6268   2447725225        14940           47        14941            0          14941    17024.695    15456.238        60160            0            0               0
   9 000000015FD050F8 0000000156E0AFE8   2447725225          103           35          104            0            104        95.58       87.695          420            0            0               0
  10 000000015FD050F8 000000015B43BB78   2447725225          371           18          371            0            371      383.448      351.818         1492            0            0               0
--//怎么会有5个版本.

SYS@127.0.0.1:17101/ppppp> @ d_buffer 9zg9qd9bm4spu 60 1
    EXECUTIONS1    BUFFER_GETS1   ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets    每次执行时间  平均处理记录数         INST_ID
--------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------
         118118          475692       123929016          118118 4.0272608747185 1049.1967016035               1               1

... sleep 60 , waiting ....

    EXECUTIONS2    BUFFER_GETS2   ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets    每次执行时间  平均处理记录数         INST_ID
--------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------
         118130          475740       123944755          118130  4.027258105477 1049.2233556252               1               1

       执行次数   总buffer_gets      总执行时间    总处理记录数 每次buffer_gets    每次执行时间  平均处理记录数
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
             12              48           15739              12               4 1311.5833333333               1
--//还好不是登录很多次.

$ echo @ share 9zg9qd9bm4spu | rlsql -s -l /  as sysdba | grep "Y$"| sort | uniq -c
      5 LANGUAGE_MISMATCH              = Y
      5 PURGED_CURSOR                  = Y
      5 ROLL_INVALID_MISMATCH          = Y