11.2資料庫登入出現library cache lock等待（一）

客戶的11.2.0.2 RAC for Linux X86-64環境的資料庫在登入時，發現出現長時間等待。

這一篇描述問題的現象的診斷。

出問題的時候我正好在客戶現場，於是當時診斷了一下。

客戶反映，問題發生在一個使用者上，使用這個使用者登入需要等待很長時間，而使用其他的使用者登入則不存在問題。

首先檢查了DBA_PROFILES，確認和密碼以及登入有關的PROFILE是否存在限制，當前資料庫已經都設定為UNLIMITED，那麼問題應該和PROFILE無關。

檢查出現問題的使用者，也未發現任何特別之處。

在sqlplus上使用這個使用者登入，經歷了將近10秒左右的等待，終於成功登入。同時檢查到會話當時出現library cache lock等待事件。

當再次嘗試重現問題時，卻已發現問題無法重現了，現在即使使用剛才的問題使用者，也可以很快登入成功，並不會出現明顯的登入等待。莫非一次成功的登入，就可以解決這個問題。

不過很快，問題再次出現，為了檢查會話執行的具體操作，對這個問題使用者建立了一個登入觸發器，在登入觸發器中設定會話的TRACE：

SQL> CREATE OR REPLACE TRIGGER T_AFTER_LOGON AFTER LOGON ON DATABASE
2 BEGIN
3 IF USER = 'GJT' THEN
4 DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE, TRUE);
5 END IF;
6 END;
7 /

Trigger created.

雖然問題重現了，但是找到會話的TRACE資訊，卻沒有發現任何異常之處，甚至在TRACE資訊中都找不到任何library cache lock等待事件的資訊。

而且此時還發現一個現象，就是利用這個使用者登入時，即使使用者名稱的密碼輸入錯誤，sqlplus也會等待很長時間，然後才會返回錯誤資訊。而這個長時間的等待從V$SESSION檢視中查詢，恰恰就是library cache lock等待事件。

上面的兩個現象說明，library cache lock的等待實際上是發生在使用者登入之前的。其實從資料庫V$SESSION檢視中也可以看出這個問題：

SQL> select sid, username, event, p1text, p1, p2text, p2, p3text, p3, seconds_in_wait
  2  from gv$session
  3  where event = 'library cache lock';

        SID USERNAME                       EVENT              SECONDS_IN_WAIT
----------- ------------------------------ ------------------ ---------------
         19                                library cache lock              21
       2017                                library cache lock              20
       2062                                library cache lock               0
       2147                                library cache lock              10
       2210                                library cache lock              21

可以看到，所有出現library cache lock等待的會話使用者名稱都是空。這些會話並不是Oracle後臺程式，而是剛才提到的問題使用者，這同樣說明當方式這個library cache lock等待時，會話還沒有成功的登入到資料庫中。

那麼現在問題有點棘手，如果會話沒有登入，則沒有辦法檢查會話執行了哪些操作，Oracle的文件中也沒有提到過，會話登入之前會進行哪些操作，經歷哪些等待。

分析一下這個問題，整個資料庫目前只有這個使用者出現了library cache lock的等待，而其他使用者沒有出現，說明問題肯定和這個使用者的自身特點有關。而資料庫中只存在預設的DEFAULT PROFILE，且所有限制都設定為UNLIMITED，那麼問題應該和PROFILE沒有關係。透過登入觸發器設定TRACE後發現，會話登入後並無任何異常操作，且TRACE檔案中看不到library cache lock等待資訊。而且即使使用者名稱密碼錯誤，也會出現這個等待事件。這說明等待發生在登入之前，與使用者登入後的行為無關。

簡單總結一下，問題和當前使用者的自身特性有關，且與DBA_PROFILE無關，也與使用者登入後的行為無關。如果不考慮PROFILE，那麼使用者特有的屬性恐怕就剩下了使用者名稱和密碼了。而且在一次成功的登入資料庫後，這個現象曾短時間消失，這說明問題可能確實和密碼有關係。

在11g中，Oracle的密碼策略確實出現了一些改變，比如密碼變成大小寫敏感。這個問題很可能導致老的程式來連線資料庫時出現密碼錯誤的現象。此外，11g還新增了一個密碼相關的特性——密碼錯誤延遲驗證：當使用者連續的輸入錯誤的密碼，Oracle所需要的密碼驗證時間會逐步增加，這可以有效的避免有人試圖透過暴力方式來破解密碼。關於11g新增密碼錯誤延遲驗證的詳細內容，可以參考：http://yangtingkun.itpub.net/post/468/505041

那麼當前的問題和這個密碼延遲驗證有關係嗎，如果是密碼延遲驗證的問題，那麼至少要多次重複輸入錯誤的密碼。不過從剛才的V$SESSION檢視中可以看到，問題使用者存在多個會話在登入資料庫，如果是程式連線資料庫，且配置了錯誤的密碼，那麼基本上就可以確定問題了。

詢問了相關程式人員，發現一個測試程式的密碼確實配置錯誤，且這個程式目前仍然在後臺不斷的嘗試連線資料庫。

現在所有的疑問都解開了，使用者程式已錯誤的密碼不斷登入，在加上11g的密碼延遲驗證，使得使用者的驗證等待時間不斷加長。這也解釋了為什麼輸入正確的密碼登入後，這個現象曾短暫消失，因為使用者成功登入，密碼延遲驗證的時間歸零。

找到問題的原因後，在客戶的資料庫上以其他的使用者嘗試模擬這個現象，測試發現，如果一個會話登入資料庫，即使每次密碼都不正確使得延遲驗證時間不斷變長，也不會引發library cache lock的等待時間，但是隻要該使用者存在第二個登入會話，這時library cache lock會在兩個會話同時出現，而且即使這個會話嘗試使用正確的密碼登入，在成功登入之前，也要等待library cache lock事件。

根據這個現象，個人推測Oracle為了實現延遲驗證，必然需要在共享池中儲存一個類似計數器的物件。這個計數器記錄使用者登入連續密碼錯誤次數，從而確定延遲驗證的等待時間。當使用者成功登入，計數器清零。如果是一個會話，那麼只需要獨佔這個計數器就可以了，當存在兩個以上的會話，且兩個會話都試圖修改計數器的內容，那麼資源競爭就出現了，而體現在資料庫中的等待事件就是library cache lock。當然這只是個人的猜測而已，還沒有看到Oracle官方對這種情況的說明。