Last Login Time 造成的效能問題

Oracle 版本: 12.2.0.1

OS 版本: AIX 5.3

問題描述:

資料庫發生了嚴重的 Concurrency Wait Event :

問題分析:

由 Concurrency Wait 的 sql 顯示語法為 :

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

這句語法主要的功能是用來記錄使用者上次成功的登入時間， Last Successful Login Time 為 12c 的新功能，比對 11g 與 12c 的登入畫面， 12c 多了 Last Successful login time :

不過這個功能有個嚴重的 Bug 33121934 ， update user$ 這段語法產生大量的 Library Cache Lock 造成嚴重的效能問題。

解決方法:

1. Apply Patch 33121934 。

2. 升級至 19.14 版本以上， Bug 33121934 於此版本中修復。

3. 將參數 _disable_last_successful_login_time 設定為 TRUE ，停用此功能。

Failed Logon 造成系統資源不足案例

Oracle 版本: 12.1.0.2.0 ， RAC

OS 版本: AIX 5.3

問題描述:

資料庫發生異常，檢查 DB alert log 發現 ORA-27300 、 ORA-27301 等訊息 :

在 AIX 環境上出現 Not enough space 往往指的是系統記憶體不足的訊息，由 alert log 顯示由於系統資源不足導致 Oracle 無法產生新的 process 而造成異常。

問題分析:

由系統 NMON 等監控發現消耗系統資源的，仍然多數為 oracle process ，查詢 gv$resource_limit 發現 oracle process 數量異常 :

平常時段的 process 數量最多大約 500 多個，到了事件時段 process 數量異常上升到了 2000 多個。

由 Awr Report 的 Top Event 顯示大量 Failed Logon Delay 的等待事件 :

資料庫的 Failed Logon Delay 機制原本是為了避免過度的 Logon 造成 Logon Strom / Connection Strom ，但在 12.1 的版本仍然有 Bug 19867671 - "library cache lock" caused by wrong password login ，在 Logon Delay 的過程中產生了 library cache lock 導致 session 無法及時退出，最終導致 process 越積越多，由 ash 也顯示出當時資料庫也出現了不少 library cache lock 等待 :

查詢 audit log 找出 Failed Logon 的來源 :

SQL> select os_username,userhost,dbusername,CLIENT_PROGRAM_NAME program,event_timestamp,action_name,return_code   from unified_audit_trail a  where a.return_code=1017   and a.unified_audit_policies ='ORA_LOGON_FAILURES'   and a.event_timestamp between to_date('2021-12-18 08:30:00','yyyy-mm-dd hh24:mi:ss') and to_date('2021-12-18 08:44:59','yyyy-mm-dd hh24:mi:ss')

Failed Logon 來自於 AP Server ，判斷應為 DB 的 AP User 改密碼而 AP Server 端沒有更改到導致 Logon Failed ，再加上 AP 的 retry 機制最終導致問題。

解決方法:

可以從幾個面向來解決此問題 :

1. 資料庫升級至 12.2 以上版本，Bug 19867671 於 12.2 版本以上修復。

2. 於 user profile 設置 FAILED_LOGIN_ATTEMPTS 次數，超過次數的 Failed Logon 則將 user lock 。

3. 設置隱藏參數 _sys_logon_delay 為 0 ，表示停用 Failed Logon Delay 機制，不過更改隱藏參數是比較不建議的做法。