关于read by other session，db file scattered read，db file sequential read等待时间的优化（上）

今天检查彩民村的数据库健康状态，发现CPU使用率非常高，而且这个时间点的业务并不是很忙，开发人员也没有对数据库做大的操作。

分析AWR发现占用资源比较大的sql很多都是重复的

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		20,956		51.4
read by other session	32,208,585	3,680	0	9.0	User I/O
db file scattered read	32,013,721	2,214	0	5.4	User I/O
db file sequential read	42,658,212	1,100	0	2.7	User I/O
latch: cache buffers chains	945,865	955	1	2.3	Concurrency

关于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

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
10,512	4,238	691	15.21	25.77	0ygf63rbau963	JDBC Thin Client	select * from ( select this_.I...
6,709	2,678	461	14.55	16.44	d4504x8nd5ap7	JDBC Thin Client	select * from ( select this_.I...
2,588	406	50	51.75	6.34	7pn5pxb6sdusu	JDBC Thin Client	select * from ( select row_.*,...
2,351	1,133	134	17.54	5.76	9k9dcs1zqk18r	JDBC Thin Client	select this_.ID as ID3_0_, th...
2,130	1,600	2,842	0.75	5.22	5tt2k7djxub2m	[e-mail] (TNS V1-V3)	select t.unionorderid as seria...
1,792	1,345	2,429	0.74	4.39	8vrb5n1wrbmpj	[e-mail] (TNS V1-V3)	select t.unionorderid as seria...
1,725	1,409	4,129	0.42	4.23	cd03f062f97s4	[e-mail] (TNS V1-V3)	select ceil(count(unionorderid...
1,017	827	2,429	0.42	2.49	1h25h74888fnp	[e-mail] (TNS V1-V3)	select ceil(count(unionorderid...

 

SQL ordered by CPU Ti

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
4,238	10,512	691	6.13	25.77	0ygf63rbau963	JDBC Thin Client	select * from ( select this_.I...
2,678	6,709	461	5.81	16.44	d4504x8nd5ap7	JDBC Thin Client	select * from ( select this_.I...
1,600	2,130	2,842	0.56	5.22	5tt2k7djxub2m	[e-mail] (TNS V1-V3)	select t.unionorderid as seria...
1,409	1,725	4,129	0.34	4.23	cd03f062f97s4	[e-mail] (TNS V1-V3)	select ceil(count(unionorderid...
1,345	1,792	2,429	0.55	4.39	8vrb5n1wrbmpj	[e-mail] (TNS V1-V3)	select t.unionorderid as seria...
1,133	2,351	134	8.46	5.76	9k9dcs1zqk18r	JDBC Thin Client	select this_.ID as ID3_0_, th...

 

SQL ordered by Elapsed Time

SQL ordered by CPU Time

 

0ygf63rbau963

select * 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_.TZZ_ID=:1 order by this_.ID desc ) where rownum <= :2

 

7pn5pxb6sdusu

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=:1 order by this_.ID desc ) row_ where rownum <= :2) where rownum_ > :3