Waiting 5 years (15,654,536,681cs) on "db file scatttered read" during a 1-hour interval

From: Denys van Kempen <dvkempen_at_gmail.com>
Date: Thu, 21 Apr 2005 14:47:28 +0200
Message-ID: <f424bce90504210547681b37c4@mail.gmail.com>

List,
Statspack is sorting suspicious numbers (v 8.1.7.4 <http://8.1.7.4>) for an 1-hour interval:

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn

-------------------- ------------ ---------- ---------------- --------- 

---

db file scattered re 1,430,190 0 15,654,536,681 109458 19.9 If my arithmetics are right (15,654,536,681/100/60/60/24/365) Oracle waited 5 years on this event for this interval.

Oracle is running on a 6-CPU server and used about 6500 second CPU. Statistic Total per Second per Trans

--------------------------- ------------------ -------------- --------------

CPU used by this session 647,923 178.2 9.0   I/O stats give me an average of almost 60 seconds for a read call with 2.6million calls issued.  

File IO Stats for DB: FGMGP Instance: FGMGP Snaps: 5273 -5274
->ordered by Tablespace, File

Tablespace Filename

---

---

Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- --------- ------- ------------ -------- ----------

---

DATA /oracle4_02/oradata/FGMGP/data01FGMGP.dbf 2,666,758 733 58,702.6 2.6 13,455 4 4,082 4.9 /oracle4_02/oradata/FGMGP/data02FGMGP.dbf 93 0 11.4 4.7 3 0 2 10.0

If you query the STATS$SYSTEM_EVENT table on the event 'db_file scattered read' for the two snapshost and substract them you get the same values. There seems to be a preferrence for 39.000.000 or 4.51 days

    date total_waits B2-B1 4/7/05 8:22 AM 171743584783 4/7/05 9:23 AM

171743646743 620 4/7/05 10:23 AM 175655761254 39121145 4/7/05 11:24 AM 
175655821396 601 4/7/05 12:24 PM 175655873567 522 4/7/05 1:25 PM 
175655927593 540 4/7/05 2:26 PM 175655996976 694 4/7/05 3:26 PM 191310533657 

156545367 4/7/05 4:27 PM 195224563808 39140302 4/7/05 5:27 PM 195224596976 332 4/7/05 6:28 PM 195224615250 183 4/7/05 7:29 PM 195224629607 144

   4/12/05 8:27 AM 238470576676 4/12/05 9:28 AM 242425269237 39546926 4/12/05 10:28 AM 246380475160 39552059 4/12/05 11:29 AM 246380617022 1419 4/12/05 12:30 PM 250336569219 39559522 4/12/05 1:31 PM 250336678671 1095 4/12/05 2:31 PM 254293378201 39566995 4/12/05 3:32 PM 258250399475 39570213 4/12/05 4:32 PM 258250491110 916 4/12/05 5:33 PM 258250616196 1251 4/12/05 6:34 PM 258250641577 254 4/12/05 7:34 PM 262208979635 39583381

 Known issue?

Thanks,

Denys van Kempen

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 21 2005 - 08:51:38 CDT