Re: db file sequential/scattered read - physical or logical io or both?
Date: Mon, 14 Jun 2010 06:34:16 +0200
Message-Id: <78943934-6383-4576-9131-4DE40F714339_at_gmail.com>
Oracle calls sequential and scattered reads 'physical' as these calls to get blocks leave the Oracle program. Oracle cannot know anything about the 'external' layers, like filesystem cache or even cache within a storage array or disk. You can call it a description which code path within Oracle was used.
Your distinction with the limit of 5 ms is artificial and must be set up regarding the system your Instance is running on. It might be right on your system. With such a distinction you can only say something like 'more than x% of all answers of 5 ms or less are from a cache'. Keep this in mind if you compare the numbers to real counts e.g. on filesystem cache hit.
You did not tell us anything about your OS, Hardware and where the datafiles / Blocks reside. So it's hard to tell if yur lmits of 5 and 10 ms are reasonable.
best regards,
Martin
> I suppose what I'm trying to say here is that if the same wait event
> is issued regardless of a physical or logical io request, how can I
> determine if the io subsystem is returning blocks at s reasonable
> service time. Assuming an idle system other than the report, would
> times under 5ms be cache hits and times over be physical. Further
> anything over 10ms would be a bad service time ? appreciate any
> input on this
> Thanks
>
> On Mon, 14 Jun 2010 06:31 SGT Oracle Dba Wannabe wrote:
>
>> Hi,
>> So I have an idle system, where I run a sql report. The report
>> takes an hour to run. I look at the awr report (30 min interval),
>> and see the io wait events for sequential and scattered reads. The
>> first has an avg wait time of 7ms the second 10ms. These waits as I
>> understand it are physical io requests - correct? The p1 and p2
>> parameters point to file and block numbers so I guess that makes
>> sense. Anyway I rerun the same report, look at the new awr and now
>> see the same wait events, only with much smaller wait times. Which
>> means data was read from cache - if that's the case why are the
>> same wait events issued? it seems a bit confusing that way.
>> Thanks
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jun 13 2010 - 23:34:16 CDT