awr性能调优案例

1. 故障概述

2014年10月10日上午9点10分左右开始，营业厅等前端出现访问、业务办理等长时间无响应现象，定位业务访问相关数据库，为中心数据库RAC；通过排查，最终定位为某update语句对一上千万数据量的表进行全表扫描，造成大量的db file scattered read，引发数据库严重的IO性能问题，为造成此次数据库响应缓慢的原因。通过新建索引后，故障消除，数据库恢复正常响应。

云和恩墨工程师协助从数据库层面进行问题分析排查，以下是对于此次故障的分析及总结。

2. 故障分析

观察问题时间段AWR性能负载报告，发现数据库于9点至9点半的半小时采样时间中，DB Time接近10000分钟，DB Time/Elapsed约等于330倍，远超cpu数80，数据库遭遇严重的性能问题。

正常：

 

 

 

异常：

 

 

此时数据库负载中看到，对比正常时间段，逻辑读与物理读有异常的增长：

正常：

 

 

异常：

 

 

且实例效率中，正常应基本大于90%的buffer命中率，大大降低到71%：

 

正常：

 

 

异常：

 

说明内存buffer中的数据进行了大量的换出，侧面反映出数据库实例与磁盘进行数据交互的量大大增加。

 

观察异常时的数据库top10等待事件，排名前两位的等待，占据DB Time超过70%，大量的db time消耗在用户IO以及集群数据请求上。

 

由于该库已经关闭了direct path read，排名第一的db file scattered read说明数据库正处於大量的全表扫描等待中，全表扫描会产生大量的物理读，占用磁盘IO，易造成严重数据库响应等待。

 

查看产生物理读sql排名，排名第一的产生大量物理读的sql为更新语句a0hwv4gh789m9：

 

 

观察产生物理读最多的段对象，为分区表TL_B_IBTRADE，与更新语句操作对象相同

 

 

Sql原文：

UPDATE TL_B_IBTRADE

   SET BIPSTATUS = :1,

       RECONTAG  = :2,

       IBBFEE    = :3,

       IBBFEEDIR = :4,

       IBAFEE    = :5,

       IBAFEEDIR = :6,

       IBSFEE    = :7,

       IBSFEEDIR = :8

 WHERE IBSYSID = :9

 

该表在半小时的采样时间内共执行全表扫104次，是产生大量物理读，占用磁盘IO，造成响应缓慢，而经查证，对于sql条件列IBSYSID，表TL_B_IBTRADE上并无索引，是引致此时全表扫的原因：

 

 

 

最终通过在该字段上建立索引，通过INDEX RANGE SCAN索引扫描进行数据访问，执行cost由2748K降为1。执行效率有了极大的提升，数据库响应恢复正常。

3. 故障总结

本次故障根本原因是由于上千万数据量大表TL_B_IBTRADE的字段IBSYSID上缺少索引，在执行UPDATE语句时对表进行全表扫描，产生大量物理读，严重影响系统IO性能，导致Oracle实例响应缓慢最终hang住。

对于此次故障，是由于前期只集中资源对400M以上大表进行排查，一旦发现全表扫，将重点检查相应字段索引，未能够大范围、全面排查。现经过测试库上sql审核工具的部署并应用，以及通过制定的全表扫、笛卡尔积、索引全扫等sql执行规则，监控发现将于10月14日上线sql并进行优化，保证sql执行效率。此外，改进脚本，对生产库中现有100M以上大表进行排查，建立索引，最大限度消除全表扫描，保证现有系统sql高效执行。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1816636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1816636/