微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

实战课堂:数据库高Library Cache Lock导致Hang的故障分析

2018-05-29 00:00 墨墨

编辑手记:在现实的生产环境中,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


找到问题的原因,就可以理解数据库的行为。只需要等系统的失效对象编译通过,系统就能够恢复正常工作。


回顾这个问题,我们同样可以对用户日常运维提出规范建议:

  1. 数据库的业务账号由统一的归口管理部门和人员进行管理,杜绝非账号管理人员掌握数据库的业务账号。

  2. 对于核心业务系统任何变更一定要在停机窗口时间进行操作,应用固定变更场所,避免供应商变更人员使用PL/SQL Developer等工具直连生产系统。

  3. 对业务核心对象进行变更包括增加字段,删除字段,添加删除权限都应非常谨慎。定期检查检查数据库中无效对象,清理无用的无效对象。

  4. 进行业务变更期间应有DBA配合保障工作。


这就是来自生产的一次故障处理和排查,通过这样的过程,我们能够看到,在生产环境中一次小的操作,就可能导致严重的性能影响,一个DDL都不应该草率执行,任何变更都应该充分考虑级联的各种影响。


多看多知,这就是实战课堂。



资源下载

关注公众号:数据和云(OraNews)回复关键字获取

2018DTCC , 数据库大会PPT

2017DTC,2017 DTC 大会 PPT

DBALIFE ,“DBA 的一天”海报

DBA04 ,DBA 手记4 电子书

122ARCH ,Oracle 12.2体系结构图

2017OOW ,Oracle OpenWorld 资料

PRELECTION ,大讲堂讲师课程资料


 
数据和云 更多文章 招商银行周伟:Fintech数据开放平台之数据库军规和内功修炼(含PPT) 是什么让美国网站拒绝欧洲访问?- GDPR 带来的数据安全思考 修而未复:说说WebLogic那修不完的Java反序列化漏洞 当SQL Server爱上Linux:AVAILABILITY_MODE 和 DataGuard& 前世今生:蚂蚁金服自研数据库OceanBase的道路与思考
猜您喜欢 我们为什么憎恶鸡汤 陈凯歌的新作品《三分钟》,是苹果今年的新春广告 2015 Objective-C 新特性 Python工程师面试题集合