客户的11.2.0.2 RAC for Linux X86-64环境的数据库在登录时，发现出现长时间等待。
这一篇描述现象重现过程。
11.2数据库登录出现library cache lock等待（一）：https://yangtingkun.net/?p=279
上一篇描述了客户的11.2.0.2 RAC for Linux X86-64环境出现library cache lock的问题，同事回来后想要模拟这个现象，在Windows环境下的11.2.0.1上却没有模拟出来，我也在Windows上的11.2.0.1上尝试了一下，结果没有出现library cache lock等待，但是出现了row cache lock等待事件。
测试步骤很简单，开启三个sqlplus，其中一个设置SET TIME ON，获取时间信息，并不断的已错误的用户名密码尝试连接数据库。另一个会话以正确的用户名和密码连接到数据库，设置SQLPROMPT为SQL2>，以便于和第一个会话区别。最后一个会话以SYS登录数据库，检查会话的等待状态：
SQL> SET TIME ON 08:34:41 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:42 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:42 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:43 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:44 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:46 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:49 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:54 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:34:59 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:35:05 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:35:12 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:35:21 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:35:30 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:35:40 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:35:50 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:36:01 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:36:01 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:36:01 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:36:01 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:36:02 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:36:05 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 . . . 08:38:00 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:38:10 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:38:20 SQL> CONN TEST/A@192.25.1.100/TEST112 ERROR: ORA-01017: 用户名/口令无效; 登录被拒绝 08:38:30 SQL> |
可以看到，会话1登录失败的等待时间从1秒慢慢涨到了10秒，随后又缩短到1秒以内，最后又一次涨到了10秒。
之所以等待时间被重置，是因为会话2上成功的执行一次登录：
SQL> CONN TEST/TEST@192.25.1.100/TEST112 已连接。 SQL> SET SQLP 'SQL2> ' SQL2> CONN TEST/TEST@192.25.1.100/TEST112 已连接。 SQL2> |
会话2的登录成功，使得会话1上10秒的延迟验证被重置到1秒以内。
最后看一下V$SESSION视图查询的等待信息：
SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 4 63 TEST sqlplusw.exe SQL*Net message FROM client 89 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 2 63 TEST sqlplusw.exe SQL*Net message FROM client 103 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 6 63 TEST sqlplusw.exe SQL*Net message FROM client 107 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 8 63 TEST sqlplusw.exe SQL*Net message FROM client 143 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 1 63 sqlplusw.exe ROW cache LOCK 1 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 4 63 sqlplusw.exe ROW cache LOCK 4 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 6 63 sqlplusw.exe ROW cache LOCK 6 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 12 sqlplusw.exe SQL*Net message FROM client 9 63 sqlplusw.exe ROW cache LOCK 8 SQL> SELECT SID, USERNAME, PROGRAM, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION 3 WHERE NVL(USERNAME, 'OTHER') != USER 4 AND NVL(PROGRAM, 'OTHER') NOT LIKE 'ORACLE.EXE%'; SID USERNAME PROGRAM EVENT SECONDS_IN_WAIT ---------- ---------- --------------- ------------------------------------- --------------- 63 TEST sqlplusw.exe SQL*Net message FROM client 1 69 sqlplusw.exe SQL*Net message FROM client 0 |
可以看到，如果只有一个会话连接数据库失败，则不会导致任何异常等待的出现，如果这时存在另一个会话以同样的用户名来访问数据库，那么不管这个用户使用的密码是否正确，都会引发row cache lock等待事件。
而同样的测试在11.2.0.2的环境中，出现的等待是library cache lock。检查了一下，当前的row cache lock等待事件，实际上是11.2.0.1的一个bug：Bug 9720182: DUE TO ROW CACHE LOCK WAIT EVENTS IN DATABASE APPLICATION GOT HUNG。
Oracle提供了专门的PATCH可以解决这个问题，其实解决这个问题的最有效的办法，就是避免用户使用不正确的密码来连接数据库。