Re: db file sequential/scattered read - physical or logical io or both?

  • From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
  • To: oracledbawannabe@xxxxxxxxx
  • Date: Mon, 14 Jun 2010 11:26:46 -0500

The way Oracle names things in its instrumentation causes a lot of
misconceptions and frustrations. One common mistake people make is to skip
straight over the operating system software layer in their minds when they
think about Oracle's instrumentation results. That happens, for example,
when people get trapped into thinking with terms like "physical read" when
they're looking numbers produced by the Oracle kernel.

To understand Oracle, some of the most important documentation you need to
read is your operating system's manual pages. When the Oracle kernel code
reports that just executed a 'db file scattered read' call, what it actually
just did was execute a *readv* syscall (actually an *_llseek* and a
*readv*call on one 10g test I recorded). You don't have to believe me:
you can see
it for yourself by using *strace* (*truss*, DTrace, *sctrace*, etc.) on *
your* system. Likewise, Oracle when reports a 'db file sequential read',
you'll probably find that it has just executed a *pread* call.

The important thing about knowing this is that syscalls like *readv* and *
pread* are *thoroughly* documented for free. Just use Google, or type "man
readv" to a shell prompt on a *nix system with the manual pages installed.
You can even study the source code for those calls if you want to. Once you
understand what those calls do, then you can begin to think clearly about
Oracle performance at the hardware level.

The best advice I ever received about learning Oracle performance is this:

The Oracle kernel is just a big C program.

You don't have to guess about how Oracle works in matters like this: you can
measure it with standard operating system tools (like *strace*, which I've
mentioned). And you don't have to trust what people write and say about
Oracle. That is vital, because much of what even the best-intended people
write is imprecise, misleading, and in some cases just downright wrong. It's
been that way for decades, and some recent examples posted at suggest that it's going to be that way
for decades yet to come.

Cary Millsap
Method R Corporation

On Sun, Jun 13, 2010 at 5:31 PM, Oracle Dba Wannabe <
oracledbawannabe@xxxxxxxxx> 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
> --

Other related posts: