Oracle-L: RE: db file sequential read

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 19 Nov 2002 08:39:58 -0800
Message-ID: <F001.00506C34.20021119083958@fatcity.com>

Charlie,

First task I'd suggest is to make sure your world isn't being complicated by an "imperfect report." If you mean that a single wait for "db file sequential read" (or "db file scattered read," which is probably really what you're seeing because you mentioned reads of an index) consumes from 3 to 15 seconds, then I would suggest the following course of action:

Make sure that the report on this statistic isn't being tricked by examining V$SESSION_WAIT.SECONDS_IN_WAIT. I'd bet money that this is what's happening. If your report uses this column, then it is highly likely that you're not actually waiting very long for physical I/O at all. The problem is that SECONDS_IN_WAIT keeps growing in 3-second increments long after an event completes, until the next event begins.

LIO processing isn't an instrumented "Oracle kernel event." So if you're doing lots of LIOs after each read (which is common with poorly optimized SQL), then SECONDS_IN_WAIT makes it look like you're waiting for I/O; but you're not. Make sure you check the value of V$SESSION_WAIT.STATE. If the value is anything other than 'WAITING', then the I/O completed in WAIT_TIME units of time (centiseconds in Oracle7 or 8; microseconds in Oracle9).

SECONDS_IN_WAIT will tell you to within plus-or-minus 3 seconds how long since that I/O *began*, but that's not what you want to know. If this SECONDS_IN_WAIT misunderstanding is a problem in your report, then you actually have evidence of inefficient SQL that does too many LIOs. See "Why you should focus on LIOs instead of PIOs" at www.hotsos.com/catalog for more information.

2. If you really *are* experiencing multi-second I/O call durations, then it's probably because your application is demanding more I/O calls per second than your I/O subsystem has capacity to handle. You can relieve the problem in several ways. The most powerful way is to eliminate demand by optimizing SQL (see the LIO paper reference listed above). It's also possible to buy more disks and balance your data more uniformly across a larger number of drives. This is usually both a weaker and more expensive method than LIO (and consequent PIO) reduction.

Good luck.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas

-----Original Message-----
Charlie_Mengler_at_HomeDepot.com
Sent: Tuesday, November 19, 2002 8:54 AM To: Multiple recipients of list ORACLE-L

I'm search of perfection in an imperfect world. This problem involves a V7.3.4.5 DB on V2.6 Solaris.

As part of our nightwork batch processing, a bunch of reports are run against the DB.
I have a DBMS_JOB which reports when processes are waiting for events like
"db file sequential read". I typically get an email showing about 2 dozen
sessions
with wait times of 3 - 15 seconds all of which are doing one block reads against
P_INVLOC; which is the Primary Key for the Inventory_Location table. This
index
has INITRANS=31 & FREELISTS=31. This is a decent sized index at about 128MB.

I'm open for suggestion WRT what else can & could be done to eliminate or
reduce these waits.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Charlie_Mengler_at_HomeDepot.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------