2017-8-11 db file sequential read占用很大可能存在的问题

        一直有个疑问就是db file sequential read时间占比很大说明什么问题？是说明单块读很多吗？进而说明索引读取很多，很慢。今天看一个数据库报告我想知道原因了。

先是用户反馈系统慢，看了下数据库报告，确实很多SQL慢，绝大部分时间都在等IO。

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
8,150.41	7	1,164.34	3.79	2.20	96.55	8u2m1ng26kw97	sql@etl2 (TNS V1-V3)	SELECT /*+PARALLEL(A, 4)*/ REP...
6,772.46	180	37.62	3.15	2.60	94.93	5mmdkpwgcj8z8	JDBC Thin Client	SELECT LOG_ID, OPERATE_ID, SES...
5,644.39	5,187	1.09	2.62	0.64	97.79	frn84nm7x1vyw	JDBC Thin Client	insert into GG_RU_DONE_TASK(...
4,760.19	3	1,586.73	2.21	2.71	4.72	a46qtf7mcfw2x		DECLARE job BINARY_INTEGER := ...
3,914.76	25	156.59	1.82	61.36	0.00	cyu84huv4ntbu	JDBC Thin Client	SELECT * FROM(SELECT * FROM (S...
3,651.15	11	331.92	1.70	0.44	82.84	6yyah6n2b8bsj	JDBC Thin Client	SELECT * FROM(SELECT * FROM (S...
3,540.59	13	272.35	1.65	0.28	99.91	5xpfarm9xs2c5	JDBC Thin Client	SELECT * FROM(SELECT INNER_TAB...
3,363.38	6	560.56	1.56	0.17	0.05	2ksh26dcx3s92	JDBC Thin Client	SELECT COUNT(1) FROM (SELECT A...
3,170.97	202	15.70	1.47	14.49	58.19	247r6166sx4b8	JDBC Thin Client	SELECT tsname TABLESPACE_NAME,...
2,921.16	25	116.85	1.36	0.16	98.32	afc6rq8zcpg5w	JDBC Thin Client	select R.*, B.REVOKE_BACK_FLAG...
2,809.50	176	15.96	1.31	15.46	55.00	52wfqwjr8hu4g	JDBC Thin Client	SELECT D.TABLESPACE_NAME, NVL(...
2,584.49	309	8.36	1.20	2.58	93.38	3yw7aqajdbj3k	JDBC Thin Client	INSERT INTO GG_FEEDER_JHDX_DLD...
2,420.65	10	242.07	1.12	0.30	99.20	0xv678h7b01sc	JDBC Thin Client	SELECT * FROM(SELECT * FROM (S...
2,419.89	4,986	0.49	1.12	1.11	95.82	144s8bh50zbp2	JDBC Thin Client	insert into GG_RU_TRANS_TRA...
2,371.45	11	215.59	1.10	0.12	99.08	9dyjcpt6zadk8	JDBC Thin Client	SELECT DONE.CUR_NODE_ID, DO

看到top10的等待时间最多的是db file sequential read

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
db file sequential read	12,234,438	84.4K	7	39.2	User I/O
DB CPU		29K		13.5
direct path read	680,770	18K	26	8.4	User I/O
log file sync	182,196	13.4K	74	6.2	Commit

数据库是RAC，3个小时总共产生3T多的IO，这个实例产生了1.3T的IO，已经是极限了。

Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	20354	10-8 -17 09:00:16	1682	32.5	2
End Snap:	20357	10-8 -17 12:00:45	1709	32.3	2
Elapsed:		180.48 (mins)
DB Time:		3,586.25 (mins)

Function Name	Reads: Data	Reqs per sec	Data per sec	Writes: Data	Reqs per sec	Data per sec	Waits: Count	Avg Tm(ms)
Direct Reads	844.7G	250.85	79.875M	3.8G	1.53	.361M	0
RMAN	306.6G	29.07	28.995M	4M	0.02	0M	930	2.77
Buffer Cache Reads	120.6G	1209.85	11.403M	0M	0.00	0M	12.5M	7.20
Direct Writes	10.4G	1.00	.987M	35.7G	27.04	3.375M	0
Others	27.2G	86.02	2.575M	8.3G	8.94	.782M	940.6K	1.67
DBWR	5M	0.03	0M	21G	170.29	1.989M	324	1.19
LGWR	4M	0.03	0M	12.3G	101.33	1.163M	548.5K	5.69
TOTAL:	1.3T	1576.85	123.835	81.1G	309.14	7.671M	13.9M	6.77

找到了产生Direct Reads的数据段，问题也解决。不过解开了之前的疑惑，为什么有这么多的SQL在等IO，一种可能性是数据缓冲区小了，这个可以通过SGA advisor可以验证，还有一种可能，IO太忙了。