[20211013]19C 關於LAST SUCCESSFUL LOGIN TIME.txt

[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開始提供引數控制這些行為：
--//連結：
--//
--//

_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