实战课堂：数据库高Library Cache Lock导致Hang的故障分析
实战课堂：数据库高Library Cache Lock导致Hang的故障分析
编辑手记：在现实的生产环境中，DBA可能遭遇到各种各样的异常，或简单、或复杂，但是无一不考验DBA的经验和能力，在『实战课堂』栏目中，我们将整理和分享来自云和恩墨一线的各种案例，以其帮助走在DBA道路上的朋友，积累经验，扩展知识。
案情描述：
客户数据库发生hang现象，大量业务操作超时，DBA介入分析。
通过OEM控制台的监控工具，可以看到客户数据库的“平均活动会话数”从21点开始active session出现明显增长，最高超过60个直至10点左右恢复。
在图上出现一个明显的“波峰”，且等待事件类为concurrency：
对于这类情况，如果数据库可以操作，我们仍然可以从ASH或者AWR入手，快速获取信息。
收集 21点至22点的AWR报告，其“Top 5 Timed Events”前两个为：library cache lock、library cache pin且平均等待时间分别为2928和2910毫秒，出现严重的性能问题。这两者的Wait Class就是Concurrency，也就是监控中所表现出来的问题。
查看AWR中“SQL ordered by Elapsed Time”可以发现所有执行时间长的语句都为调用过程和包，每次执行时间基本都是超过1秒最长达到35.9秒，远远超出了正常值：
通过以上信息进行初步分析，我们怀疑数据库缓慢的原因可能是对高使用率的核心存储过程和包进行编译导致。
该问题的发生一般伴随着“LIBRARY CACHE PIN” 和“LIBRARY CACHE LOCK”等待事件。
为了进一步确认问题，如果有相关包和存储过程在问题期间进行编译过可以通过DBA_OBJECTS视图的"LAST_DDL_TIME"字段观察到最近一次编译时间。
OWNER | OBJECT_NAME | OBJECT_TYPE | LAST_DDL_TIME |
|---|---|---|---|
MAIN | PKG_DTG_CARD | PACKAGE BODY | 2016/12/6 23:56 |
MAIN | PKG_DEPT_SAVE | PACKAGE BODY | 2016/12/6 23:56 |
MAIN | PKG_TRANS_T | PACKAGE BODY | 2016/12/6 23:56 |
MAIN | KM_TRAN_T | TABLE | 2016/12/6 23:56 |
MAIN | PKG_DG_CMG | PACKAGE | 2016/12/6 21:57 |
MAIN | PKG_TOLS_PROD | PACKAGE BODY | 2016/12/6 21:53 |
MAIN | SP_REXEC | PROCEDURE | 2016/12/6 21:51 |
MAIN | EMP_NOTE | TABLE | 2016/12/6 21:30 |
客户告知当晚存在对表进行添加字段的操作，经过客户与操作人员确认进行添加字段的表为MAIN.EMP_NOTE与上表格标红的行的表名和时间吻合。
存储过程或包引用的表结构发生变更时，对象会变为无效。Oracle会在第一次访问此对象时试图去重新编译它们,如果此时有其他session已经把此对象 pin到library cache中,就会因exclusive类型的pin导致出现等待,当有大量的active session并且存在较复杂的dependence时如下表，当CMP_NOTE发生改变时标红列的所有对象都需要重新编译。
OWNER | NAME | TYPE | REFERENCED_NAME |
|---|---|---|---|
MAIN | PKG_DTG_CMG | PACKAGE | EMP_NOTE |
MAIN | PKG_DTG_CHRG | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_MPS_QR | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_FRORZ | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_CAIR | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_DEBT_SLEP | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_REPT | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_CMG | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_ACCT | PACKAGE BODY | EMP_NOTE |
MAIN | PKG_DTG_TRANS | PACKAGE BODY | EMP_NOTE |
上表显示了当CBMAIN.EMP_NOTE结构发生改变后导致上表第一行CBMAIN.PKG_DTG_CMG包失效，而CBMAIN.PKG_DTG_CMG在下表有又有更多的对象依赖该包，这种依赖层层深入，重新编译由CBMAIN.CMP_NOTE表结构改变导致失效对象的时间变得不可控,并且在此期间会阻塞其它试图去访问这些对象的session，最终导致当晚问题发生。
OWNER | NAME | TYPE | REFERENCED_NAME |
|---|---|---|---|
MAIN | PKG_DTG_MPS_OTHER | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_ACCS | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_BILL_QR | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_ACCT | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_CHRG | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_MPS_TRAN | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_MPS_QR | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_FRORZ | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_CHEQ | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_CAIR | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_ACCT | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_DEBT_SLEP | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_VOCH | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_COMM_TRAN | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_REPT | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_TRAN_QR | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_CUST_QR | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_ACCT_PUB | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_ACCT_MDY | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_UNPY_ENTR | PACKAGE BODY | PKG_DTG_CMG |
MAIN | PKG_DTG_MECH | PACKAGE BODY | PKG_DG_CSMG |
MAIN | PKG_DTG_TRAN | PACKAGE BODY | PKG_DG_CSMG |
MAIN | PKG_DTG_CSMG | PACKAGE BODY | PKG_DG_CSMG |
MAIN | PKG_DTG_CHEQ | PACKAGE BODY | PKG_DG_CSMG |
找到问题的原因，就可以理解数据库的行为。只需要等系统的失效对象编译通过，系统就能够恢复正常工作。
回顾这个问题，我们同样可以对用户日常运维提出规范建议：
- 数据库的业务账号由统一的归口管理部门和人员进行管理，杜绝非账号管理人员掌握数据库的业务账号。
- 对于核心业务系统任何变更一定要在停机窗口时间进行操作，应用固定变更场所，避免供应商变更人员使用PL/SQL Developer等工具直连生产系统。
- 对业务核心对象进行变更包括增加字段，删除字段，添加删除权限都应非常谨慎。定期检查检查数据库中无效对象，清理无用的无效对象。
- 进行业务变更期间应有DBA配合保障工作。
这就是来自生产的一次故障处理和排查，通过这样的过程，我们能够看到，在生产环境中一次小的操作，就可能导致严重的性能影响，一个DDL都不应该草率执行，任何变更都应该充分考虑级联的各种影响。
多看多知，这就是实战课堂。