首页 > 数据库开发 > 数据库慢之Library cache lock

数据库慢之Library cache lock

  1. 今天客户来电,表示数据库很慢,表空间快满了。当时第一反应是不科学,因为年假之前刚检查并添加了数据文件,没有理由那么快。不管那么多,慢是一定的,看看原因先。
  2. 1.针对客户反映表空间问题,查询表空间使用率:
  3. TABLESPACE_NAME         TOTAL_G     FREE_G     USED_G USED_PERCENT
  4. -------------------- ---------- ---------- ---------- ------------
  5. USERS                     463.1       4.89     458.21        98.95   --确实比较满
  6. SYSTEM                       51      18.24      32.76        64.24
  7. UNDOTBS2                   30.1      22.54       7.55        25.09
  8. SYSAUX                     31.1      23.92       7.18        23.08
  9. UNDOTBS1                   44.3      37.54       6.76        15.26
  10. 但是:
  11. TABLESPACE_NAME      TOTAL_WITHOUT_EXTEND_GB TOTAL_WITH_EXTEND_GB FREE_WITH_EXTEND_GB    USED_GB USED_PERCENT_WITH_EXTEND
  12. -------------------- ----------------------- -------------------- ------------------- ---------- ------------------------
  13. USERS                                  463.1                  566              107.79     458.21                    80.96  -- 其实并不满
  14. SYSTEM                                    51                   64               31.24      32.76                    51.19
  15. UNDOTBS2                                30.1                   62               54.46       7.54                    12.17
  16. SYSAUX                                  31.1                   64               56.82       7.18                    11.22
  17. UNDOTBS1                                44.3                   64               57.25       6.75                    10.55
  18. 数据文件是由可扩展属性的。那么,为什么慢呢?那就查看等待事件。
  19. 2.查看等待事件
  20. SQL> select inst_id,event,count(1) from gv$session where wait_class#<> 6 group by inst_id,event order by 1,3;
  21.    INST_ID EVENT                                                              COUNT(1)
  22. ---------- ---------------------------------------------------------------- ----------
  23.          1 db file scattered read                                                    1
  24.          1 gcs log flush sync                                                        1
  25.          1 gc cr request                                                             1
  26.          1 direct path read                                                          1
  27.          1 db file sequential read                                                   2
  28.          1 gc current request                                                        2
  29.          1 cursor: pin S wait on X                                                  36
  30.          1 library cache lock                                                      130
  31.          2 log file parallel write                                                   1
  32.          2 db file parallel read                                                     1
  33.          2 latch: row cache objects                                                  1
  34.    INST_ID EVENT                                                              COUNT(1)
  35. ---------- ---------------------------------------------------------------- ----------
  36.          2 gc current request                                                        1
  37.          2 library cache pin                                                         1
  38.          2 gc cr request                                                             1
  39.          2 db file scattered read                                                    2
  40.          2 db file sequential read                                                   3
  41.          2 cursor: pin S wait on X                                                  39
  42.          2 library cache lock                                                      127
  43. 18 rows selected.
  44. 等待事件中,library cache lock比较严重。1节点130,2节点也有127,不慢才怪了
  45. 赶紧开始解决问题(参考:http://blog.csdn.net/msdnchina/article/details/46493643):
  46. SQL> select sid,saddr from v$session where event'library cache lock';
  47.        SID SADDR
  48. ---------- ----------------
  49.          3 0000000E40BE1440
  50.         10 0000000E40BDE360
  51.         41 0000000E88C3F430
  52.         42 0000000E48BA2870
  53.         43 0000000E20BC1A60
  54.         80 0000000E20BD0EC0
  55.        115 0000000E88C5DCF0
  56.        146 0000000E48BD3670
  57.        147 0000000E20BF2860
  58.        184 0000000E20C01CC0
  59.        188 0000000E40C2DA20
  60.        SID SADDR
  61. ---------- ----------------
  62.        220 0000000E48BF1F30
  63.        222 0000000E28C12E58
  64.        260 0000000E30C03E68
  65.        295 0000000E20C2F9E0
  66.        296 0000000E28C31718
  67.        326 0000000E28C43C58
  68.        328 0000000E38C2AAF0
  69.        329 0000000E40C6DC80
  70.        332 0000000E20C3EE40
  71.        362 0000000E20C51380
  72.        365 0000000E38C39F50
  73.        SID SADDR
  74. ---------- ----------------
  75.        366 0000000E40C7D0E0
  76.        368 0000000E48C2F0B0
  77.        398 0000000E48C415F0
  78.        400 0000000E28C62518
  79.        401 0000000E30C440C8
  80.        405 0000000E48C3E510
  81.        406 0000000E20C5D700
  82.        437 0000000E28C71978
  83.        441 0000000E88CEA530
  84.        471 0000000E88CFCA70
  85.        476 0000000E38C67C70
  86.        SID SADDR
  87. ---------- ----------------
  88.        509 0000000E48C6F310
  89.        548 0000000E28C9F698
  90.        551 0000000E40CC96C0
  91.        581 0000000E40CDBC00
  92.        583 0000000E48C8DBD0
  93.        617 0000000E38CA7ED0
  94.        652 0000000E28CD0498
  95.        654 0000000E38CB7330
  96.        691 0000000E38CC6790
  97.        692 0000000E40D09920
  98.        693 0000000E88D584B0
  99.        SID SADDR
  100. ---------- ----------------
  101.        724 0000000E48CCDE30
  102.        725 0000000E20CED020
  103.        764 0000000E30CDFD68
  104.        765 0000000E38CE5050
  105.        766 0000000E40D281E0
  106.        796 0000000E40D3A720
  107.        797 0000000E88D892B0
  108.        799 0000000E20D0B8E0
  109.        834 0000000E88D98710
  110.        836 0000000E20D1AD40
  111.        837 0000000E28D1CA78
  112.        SID SADDR
  113. ---------- ----------------
  114.        868 0000000E30D10B68
  115.        869 0000000E38D15E50
  116.        872 0000000E48D0AFB0
  117.        902 0000000E48D1D4F0
  118.        909 0000000E48D1A410
  119.        939 0000000E48D2C950
  120.        940 0000000E20D4BB40
  121.        973 0000000E38D46C50
  122.        975 0000000E88DD8970
  123.        981 0000000E40D86D00
  124.       1015 0000000E28D6C138
  125.        SID SADDR
  126. ---------- ----------------
  127.       1017 0000000E38D52FD0
  128.       1082 0000000E28D8DAD8
  129.       1083 0000000E30D6F688
  130.       1085 0000000E40DB7B00
  131.       1155 0000000E20DAA660
  132.       1156 0000000E28DAC398
  133.       1157 0000000E30D8DF48
  134.       1196 0000000E40DE5820
  135.       1197 0000000E88E343B0
  136.       1233 0000000E40DF4C80
  137.       1265 0000000E48DB9190
  138.        SID SADDR
  139. ---------- ----------------
  140.       1266 0000000E20DD8380
  141.       1269 0000000E38DC0F50
  142.       1270 0000000E40E040E0
  143.       1297 0000000E28DEC5F8
  144.       1298 0000000E30DCE1A8
  145.       1303 0000000E20DE77E0
  146.       1334 0000000E28DFBA58
  147.       1337 0000000E40E25A80
  148.       1338 0000000E88E74610
  149.       1373 0000000E38DF1D50
  150.       1375 0000000E88E83A70
  151.        SID SADDR
  152. ---------- ----------------
  153.       1378 0000000E28E07DD8
  154.       1414 0000000E20E15500
  155.       1444 0000000E20E27A40
  156.       1445 0000000E28E29778
  157.       1449 0000000E88EA2330
  158.       1483 0000000E30E1A788
  159.       1521 0000000E38E2EED0
  160.       1551 0000000E38E41410
  161.       1585 0000000E20E67CA0
  162.       1588 0000000E38E50870
  163.       1590 0000000E88EE2590
  164.        SID SADDR
  165. ---------- ----------------
  166.       1625 0000000E38E5FCD0
  167.       1630 0000000E28E75D58
  168.       1659 0000000E20E86560
  169.       1662 0000000E38E6F130
  170.       1663 0000000E40EB22C0
  171.       1696 0000000E20E959C0
  172.       1698 0000000E30E792A8
  173.       1701 0000000E88F102B0
  174.       1730 0000000E40ED3C60
  175.       1738 0000000E88F1F710
  176.       1769 0000000E48E95090
  177.        SID SADDR
  178. ---------- ----------------
  179.       1770 0000000E20EB4280
  180.       1771 0000000E28EB5FB8
  181.       1804 0000000E40EF2520
  182.       1805 0000000E88F410B0
  183.       1838 0000000E28ED7958
  184.       1845 0000000E28ED4878
  185.       1876 0000000E30EC8968
  186.       1877 0000000E38ECDC50
  187.       1878 0000000E40F10DE0
  188.       1914 0000000E38EDD0B0
  189.       1950 0000000E30EE7228
  190.        SID SADDR
  191. ---------- ----------------
  192.       1981 0000000E38EFEA50
  193.       1984 0000000E48EF3BB0
  194.       1987 0000000E30EF6688
  195.       2019 0000000E40F51040
  196.       2020 0000000E88F9FBD0
  197.       2024 0000000E30F05AE8
  198.       2053 0000000E28F36478
  199.       2055 0000000E38F1D310
  200.       2059 0000000E20F31660
  201.       2091 0000000E30F27488
  202.       2098 0000000E30F243A8
  203.        SID SADDR
  204. ---------- ----------------
  205.       2127 0000000E28F54D38
  206.       2130 0000000E40F7ED60
  207.       2133 0000000E20F4FF20
  208.       2164 0000000E28F64198
  209.       2167 0000000E40F8E1C0
  210.       2200 0000000E20F718C0
  211.       2203 0000000E38F5A490
  212.       2204 0000000E40F9D620
  213.       2271 0000000E40FBEFC0
  214.       2276 0000000E30F73A68
  215. 142 rows selected.
  216. 随便选了一个SADDR
  217. SQL>  select kgllkhdl Handle,kgllkreq Request, kglnaobj Object  from x$kgllk
  218.   2   where kgllkses = '0000000E40FBEFC0'  and kgllkreq > 0;
  219. HANDLE              REQUEST OBJECT
  220. ---------------- ---------- ------------------------------------------------------------
  221. 0000000E567B8CD0          2 GEXXXXXGUIDS
  222. 查到了对象名称:GEXXXXXGUIDS
  223. 通过生成trace文件的方式查找持有的会话:
  224. $sqlplus '/ as sysdba'
  225.  oradebug setmypid
  226.  oradebug unlimit
  227.  oradebug dump systemstate 266
  228. 查到自己的SID2127
  229.     select pid from v$process where addr=
  230.      (select paddr from v$session where sid2127 );
  231. 通过SID找到了进程id=23607
  232. 根据23607找到了trace文件
  233. 根据上面提到的文章(http://blog.csdn.net/msdnchina/article/details/46493643) :
  234. select pid from v$process where addr=
  235.     1.  (select paddr from v$session where sid<sid_of_hanging_session> );  --随便找了一个hang的sid,得到pid=123
  236. 在trace文件中:
  237. Open the tracefile and do a search for "PROCESS <PID from above>".----->先定位到等待者session对应的pid (非ospid)
  238. In the process section, search for the wait event by doing a search on 'waiting for'. ----->再用'waiting for'定位,注意'waiting for'下一行的handle address 地址,接下来会用该地址进行搜索 --假设ADD
  239. 结果发现:waiting for中没有library cache,然后再次在数据库中检查,竟然发现,所有的library cache lock均没有了!
  240. 那么,就写下接下来的步骤,以备后用:
  241. 我 看上面提到的文章(http://blog.csdn.net/msdnchina/article/details/46493643),在使用 handle address查找对象的时候,前面有LIBRARY OBJECT LOCK ,机制如我,果断在trace文件中查找 LIBRARY OBJECT LOCK,统计竟有317处LIBRARY OBJECT LOCK(将trace文件中pid=123的记录单独取出查找)发现了很多对象。。不过,已经没有锁。
  242. 用上面的ADD查找锁定的对象OBJ
  243. 继续使用ADD找到持有mode=X的会话,找到process PPP,问下客户,杀掉。
  244. select sid from v$session where paddr=(select addr from v$process where pid=PPP);
  245. kill -9

本文固定链接: http://www.devba.com/index.php/archives/6493.html | 开发吧

报歉!评论已关闭.