当前位置: 首页 > Oracle, oracle 11g > 正文

关于read by other session,db file scattered read,db file sequential read等待时间的优化

今天检查某客户的数据库健康状态,发现CPU使用率非常高,而且这个时间点的业务并不是很忙,开发人员也没有对数据库做大的操作。
分析AWR发现占用资源比较大的sql很多都是重复的
Top 5 Timed Events

20150104133328

 

 

 

 

 

 

关于read by other session等待时间,oracle的官方文档的解释是

When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

Oracle说产生此等待事件大部分原因是多次全扫描相同的索引或在同一表上多次全表扫描。
eygle对db file scattered read的解释是:
db file scattered read通常显示与全表扫描相关的等待。当数据库进行全表扫时,基于性能的考虑,数据会分散(scattered)读入Buffer Cache。如果这个等待事件比较显著,可能说明对于某些全表扫描的表,没有创建索引或者没有创建合适的索引。
db file sequential read通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著,可能表示在多表连接中,表的连接顺序存在问题,可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。
在大多数情况下我们说,通过索引可以更为快速的获取记录,所以对于一个编码规范、调整良好的数据库,这个等待很大是很正常的。但是在很多情况下,使用索引并不是最佳的选择,比如读取较大表中大量的数据,全表扫描可能会明显快于索引扫描,所以在开发中我们就应该注意,对于这样的查询应该进行避免使用索引扫描。
由此可见read by other session等待时间的产生很有可能与db file scattered read和db file sequential read有关。
SQL ordered by Elapsed Time

20150104133534

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL ordered by CPU Ti

20150104133701

 

 

 

 

 

 

 

 

 

 

首先想到的是查看算起来的执行计划,先看sql_id为0ygf63rbau963的sql

SQL> set line 1200
SQL>set autotrace traceonly explain
SQL> set timing on
SQL> select * from ( select row_.*, rownum rownum_ from ( select this_.ID as ID3_0_, this_.ART_ID as ART2_3_0_, this_.COM_CONTENT as COM3_3_0_, this_.COM_NAME as COM4_3_0_, this_.COM_TIME as COM5_3_0_, this_.TZZ_ID as TZZ6_3_0_ from ZHCWSQ.TZZ_ARTICLE_COMMENT this_ where this_.ART_ID=662 order by this_.ID desc ) row_ where rownum <=11) where rownum_ >5;
no rows selected

Elapsed: 00:04:23.01

Execution Plan
--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |    11 | 22990 |  9683   (1)|
|   1 |  VIEW                          |                        |    11 | 22990 |  9683   (1)|
|   2 |   COUNT STOPKEY                |                        |       |       |           |
|   3 |    VIEW                        |                        |    12 | 24924 |  9683   (1)|
|   4 |     TABLE ACCESS BY INDEX ROWID| TZZ_ARTICLE_COMMENT    |   810 |  2023K|  9683   (1)|
|   5 |      INDEX FULL SCAN DESCENDING| PK_TZZ_ARTICLE_COMMENT | 10860 |       |    30   (0)|
--------------------------------------------------------------------------------------------

 

看到这个sql走的是索引,但是4分23秒才查完76万条记录的表,这让我想到了db file sequential read等待事件,可能是因为这张表的索引建立的不正确,查看这张表的索引信息

 

SQL> set autotrace off
SQL>selectINDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME from dba_ind_columns where table_name='TZZ_ARTICLE_COMMENT';

INDEX_OWNER  INDEX_NAME               TABLE_OWNE     TABLE_NAME            COLUMN_NAM
-----------  ----------------------   ----------     -----------------     -----------
ZHCWSQ       PK_TZZ_ARTICLE_COMMENT   ZHCWSQ         TZZ_ARTICLE_COMMENT   ID

发现这张表只有ID字段有索引,而查询用到的where条件的字段ART_ID并没有索引,执行计划中走的是INDEX FULL。
通过观察sql_id为7pn5pxb6sdusu的sql也是这种情况。
再看下sql_id为5tt2k7djxub2m的大sql,还是看下索引情况

SQL> select * from  dba_indexes where table_name='TB_LOTTERYSCHEMEINFO';

no rows selected

SQL> select * from user_ind_columns where table_name='TB_LOTTERYSCHEMEINFO';

no rows selected

发现这个表竟然没有索引

本文固定链接: http://www.dbdream.com.cn/2015/01/%e5%85%b3%e4%ba%8eread-by-other-session%ef%bc%8cdb-file-scattered-read%ef%bc%8cdb-file-sequential-read%e7%ad%89%e5%be%85%e6%97%b6%e9%97%b4%e7%9a%84%e4%bc%98%e5%8c%96/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2015年01月04日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 关于read by other session,db file scattered read,db file sequential read等待时间的优化 | 信春哥,系统稳,闭眼上线不回滚!
关键字:

关于read by other session,db file scattered read,db file sequential read等待时间的优化:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter