走主键索引的查询sql变慢的有关问题

走主键索引的查询sql变慢的问题

问题描述
数据库hang，大量latch: cache buffers chains异常等待事件 业务慢，主机CPU达到100%

EventWaitsTime(s)Avg wait (ms)% DB timeWait Class644,380321,72049947.191,324,320173,15213125.40 109,560 16.0720,2814,2062070.62254,6203,612140.53SQL> select FILE_NAME,file_id from dba_data_files where file_id in(1,4);FILE_NAME FILE_ID---------------------------------------------- +DATA/hrdb/datafile/system.374.788282825 1+DATA/hrdb/datafile/undotbs2.378.788282973 4SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE 4 = FILE_ID AND 155524 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE 4 = FILE_ID AND 155526 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;SEGMENT_TYPE OWNER||'.'||SEGMENT_NAME------------------ ----------------------------------------TYPE2 UNDO SYS._SYSSMU20_2938845397$SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE 4 = FILE_ID AND 155525 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;SEGMENT_TYPE OWNER||'.'||SEGMENT_NAME------------------ ----------------------------------------TYPE2 UNDO SYS._SYSSMU20_2938845397$SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE 4 = FILE_ID AND 155524 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;SEGMENT_TYPE OWNER||'.'||SEGMENT_NAME------------------ ----------------------------------------TYPE2 UNDO SYS._SYSSMU20_2938845397$

总结：原因就很明显了，当批量删除数据时，如果另一个会话需要读写该表时，由于没有提交，为了保持数据的一致性，需要读取undo数据库进行数据库的还原操作。而这些读的操作往往是之前逻辑读的几十倍甚至上千倍，如果修改数据的事务长时间没有提交，会严重影响其它对改表的语句的性能。

对于大事务，特别是更新或DELETE数千万记录的大事务，在生产系统上尽量避免单条SQL一次性做。这造成的影响特别大，比如：

事务可能意外中断，回滚时间很长，事务恢复时过高的并行度可能引起负载增加。表中大量的行长时间被锁住。如果事务意外中断，长时间的回滚（恢复）过程中，可能严重影响SQL性能（因为查询时需要回滚块）。事务还未提交时，影响SQL性能，比如本文中提到的情况。消耗过多UNDO空间。对于DELETE大事务，有些版本的oracle在空闲空间查找上会有问题，导致在INSERT数据时，查找空间导致过长的时间。对于RAC数据库，由于一致性读的代价更大，所以大事务的危害更大。参考文档
http://www.laoxiong.net/full-table-scan-but-lots-of-db-file-sequential-read%E4%B8%80%E4%BE%8B.html

查看更多 下一篇