同样的查询每次都产生大量物理读的调优示例

12月中旬用户反映综合传输网管库上的一个查询影响迟缓，具体现象表现为当多个用户在应用界面上同时点下查询后，结果返回耗时长，影响正常业务的运作。经过初步分析该操作主要的等待事件在db file sequential read上，为了进一步明确问题，我们在系统的高峰时段使用性能报告工具抓取了统计信息，以下为top3等待事件: Event                               Waits   Timeouts   Time (s)   (ms) ---------------------------- ------------ ---------- ---------- ------ enqueue                           542        402      1,406   2595 db file sequential read           446,099          0        391      1 db file scattered read            156,634          0        209      1 可以看到db file sequential read事件仅次于数据库队列事件为主要的数据库性能瓶颈，以下列出缓存占用较高的典型SQL: Selecta.objectid,a.emsalarm_time,a.emsend_time,c.label_cn,c.alias,a.alarm_name,a.alm_devinfo from traph c,alarm_to_traph b,current_alarm a where a.cuid=b.related_alarm_cuid and b.related_traph_cuid=c.cuid and (c.ext_ids=',8,' or c.ext_ids=',9,' or c.ext_ids=',12,' or c.ext_ids=',19,' or c.ext_ids=',25,') and a.emsend_time is null and a.emsalarm_time > to_date('2008-11-19','yyyy-MM-dd') 经过进一步追踪我们发现以上SQL正是应用界面点击查询所做的操作，这就明确了此次优化的主要目的，即分析并尽可能降低该语句所产生的物理读和逻辑读，保证应用的正常运行。

具体分析

该句查询语句涉及到三个表的连接，因为应用设计使用的是基于RULE的优化模式，故执行计划倾向于使用索引来代替全表扫描，在表与表的连接方式上倾向使用嵌套循环即（NESTED LOOP）,具体执行计划如下图： 经过查询缓存池中各个表的具体缓存状况发现，表ALARM_TO_TRAPH上的索引ALARM_TO_TRAPH_INDEX1与索引TRAPH_CUID均已被缓存，而在该执行计划中需要做全表扫描操作的CURRENT_ALARM则只有部分块被缓存，充分说明了引起物理读需求的正是对CURRENT_ALARM的全表扫描操作，为了进一步证实这一点，我们做了一次10046事件的trace,该事件可以记录SQL语句执行过程中详细的等待事件以及相关信息,在trace中发现大量db file sequential read等待时间，摘录如下： 以上记录中p1,p2对应了数据文件顺序读具体发生的文件号和块号，经过查询确定以上相关文件号和数据块号具对应于表CURRENT_ALARM,至此本次性能问题的主要原因已十分明确，即CURRENT_ALARM表未被完全缓存，引起问题的可能有两种：1.表中存在大量的chained rows即链式行;2.数据库高速缓存在系统高峰时段存在瓶颈。 对于前一种可能性，我们首先分析了表的结构，CURRENT_ALARM表包括96个列，其中包括大量varchar2(255)类型的长列，理论单行长度可能达到14K字节，的确可能引起严重的链式行，为了进一步证实，我们对该表做了一次链式行扫描，操作如下： ANALYZE TABLE CURRENT_ALARM  LIST CHAINED ROWS INTO chained_rows; 该分析操作会将表上的链式行记录到临时表chained_rows中，查询chained_rows发现没有记录，说明表CURRENT_ALARM上没有链式行的问题。其后我们通过函数计算CURRENT_ALARM表上的行长度，发现最长的一行占用1367个字节，就目前来说仍不至于发生严重的链式行问题。 经过以上分析问题已经定位到了高峰时段数据库高速缓存的使用状况上了，通过在早晨，上班高峰时段以及下班时段的进一步观察，我们发现在早晨上班之前，buffer_cache中大约有600个free块可以立即使用，在该时段做上述查询不存在物理读的操作，而在上班高峰时段free块的数量下降到个位数乃至为零，即需要加载数据块时没有可以立即使用的空闲块，同时因为其他查询更为活跃，CURRNET_ALARM表上的数据块在读入后迅速被刷出，没有被缓存住以便于下次的查询，导致了性能问题的最终产生。

总结

由于数据库高速缓存在高峰时段没有空闲块导致需要全表扫描的表未被完全缓存，进而在多用户同时查询的情况下产生大量的物理读等待，影响了正常业务的运行。针对该问题提出以下建议：

• 增大SGA总量，增大数据库高速缓存即DB_CACHE_SIZE的空间，并扩展高速缓存的keep池，将表CURRENT_ALARM保存在keep池中，保证不被刷出。
• 定期分析CURRENT_ALARM表的行扩展情况，从而避免可能的链式行的产生。
• 在可能的情况下，为上述查询语句加上执行计划暗示即ALL_ROWS的hit,使其在连接方式上倾向于使用哈希连接，可以降低三分之一的逻辑读。
• 增大数据库使用内存总量的同时可能引发CPU的使用量有一定上升，需要密切关注主机的负载情况。

[gview file="http://youyus.com/wp-content/uploads/resource/BUFFPOOL.pdf"]