Inordinate IO wait times

From: <oracle-l-bounce_at_freelists.org>
Date: Thu, 15 Sep 2005 09:13:15 -0700
Message-ID: <EF25DB6D87DD1A469C80A312C63C3B4C0477029C@SVR-ORW-EXC-07.mgc.mentorg.com>

 

We are on Oracle 9.2 on solaris. In looking at a trc files (both raw and via tkprof I see IO wait times as high as 1.19 seconds per IO.

                                                      (I put the ,'s in
for easier reading)     ela/1million  cumulative

WAIT	#1:	nam='db	file	sequential	read'	ela=
1,188,929	p1=163	p2=241404	p3=1	1.188929	1.188929
WAIT	#1:	nam='db	file	sequential	read'	ela=
1,183,896	p1=158	p2=347805	p3=1	1.183896	2.372825
WAIT	#1:	nam='db	file	sequential	read'	ela=
1,175,133	p1=163	p2=236918	p3=1	1.175133	3.547958
WAIT	#1:	nam='db	file	sequential	read'	ela=
1,174,606	p1=158	p2=65952	p3=1	1.174606	4.722564
WAIT	#1:	nam='db	file	sequential	read'	ela=
1,170,150	p1=163	p2=244818	p3=1	1.17015	5.892714
WAIT	#1:	nam='db	file	sequential	read'	ela=
1,074,119	p1=164	p2=241569	p3=1	1.074119	6.966833
WAIT	#1:	nam='db	file	sequential	read'	ela=
1,068,852	p1=158	p2=1135	p3=1	1.068852	8.035685

Or from a different tkprof report(same query)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 38.34 66.67 83667 1007156 0 1
------- ------ -------- ---------- ---------- ---------- ----------

---

total 3 38.37 66.70 83667 1007156 0 1

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 127.31 127.38 db file sequential read 83667 1.18 39.54 SQL*Net break/reset to client 1 0.00 0.00

  ---

  ---

The SQLNet messages are due in part ot executing one statement at a time so throw those out.

Any ideas? We are on a SAN. The SAN guy claims this can't be happening.
Thanks,
Jim
--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 15 2005 - 11:15:21 CDT