So many times, I see people get really confused about how to attack an Oracle performance problem, resulting in thoughts that look like this:
I don’t understand why my program is so slow. The Oracle wait interface says it’s just not waiting on anything. ?
The confusion begins with the name "wait event." I wish Oracle hadn't called them that. I wish instead of WAIT in the extended SQL trace output, they had used the token SYSCALL. Ok, that's seven bytes of trace data instead of just four, so maybe OS instead of WAIT. I wish that they had called
v$session_wait either
v$session_syscall or
v$session_os .
Here's why. First, realize that an Oracle "wait event" is basically the instrumentation for one operating system subroutine call ("syscall"). For example, the Oracle event called
db file sequential read: that's instrumentation for a
pread call on our Linux box. On the same system, a
db file scattered read covers a sequence of two syscalls:
_llseek and
readv (that's one reason why I said
basically at the beginning of this paragraph). The event called
enqueue: that's a
semtimedop call.
Second, the word
wait is easy to misinterpret. To the Oracle kernel developer who wrote the word
WAIT into the Oracle source code, the word connoted the duration that the code path he was writing would have to "wait" for some syscall to return. But to an end-user or performance analyst, the word
wait has lots of other meanings, too, like (to name just two):
- How long the user has to wait for a task to complete (this is R in the R = S + W equation from queueing theory).
- How long the user's task queues for service on a specific resource (this is W in the R = S + W equation from queueing theory).
The problem is that, as obvious and useful as these two definitions seem, neither one of them means what the word
wait means in an Oracle context, which is:
wait n. In an Oracle context, the approximate response time of what is usually a single operating system call (syscall) executed by an Oracle kernel process.
That's a problem. It's a big problem when people try to stick Oracle
wait times into the
W slot of mathematical queueing models. Because they're not
W values; they're
R values. (But they're not the same
R values as in #1 above.)
But that's a digression from a much more important point: I think the word
wait simply confuses people into thinking that response time is something different than what it really is. Response time is simply how long it takes to execute a given code path.
To understand response time, you have to understand code path.
This is actually the core tenet that divides people who "tune" into two categories: people who look at code path, and people who look at system resources.
Here's an example of what code path really looks like, for an Oracle process:
begin prepare (dbcall)
execute Oracle kernel code path (mostly CPU)
maybe make a syscall or two (e.g., "latch: library cache")
maybe even make recursive prepare, execute, or fetch calls (e.g., view resolution)
end prepare
maybe make a syscall or two (e.g., "SQL*Net message...")
begin execute (another dbcall)
execute Oracle kernel code path
maybe make some syscalls (e.g., "db file sequential read" for updates)
end execute
maybe make a syscall or two
begin fetch (another dbcall)
execute Oracle kernel code path (acquire latches, visit the buffer cache, ...)
maybe make some syscalls (e.g., "db file...read")
end fetch
make a syscall or two
The trick is, you can't see this whole picture when you look at
v$whatever within Oracle. You have to look at a lot of
v$whatevers and do a lot of work reconciling what you find, to come up with anything close to a coherent picture of your code path.
But when you look at the Oracle code path, do you see how the syscalls just kind of blend in with the dbcalls? It's because they're all
calls, and they all take time. It's
non-orthogonal thinking to call syscalls something other than what they really are: just subroutine calls to another layer in the software stack. Calling all syscalls
waits diminishes the one distinction that I think really actually
is important; that's the distinction between syscalls that occur
within dbcalls and the syscalls that occur
between dbcalls.
It's the reason I like
extended SQL trace data so much: it lets me look at my code path without having to spend a bunch of
extra time trying to compose several different perspectives of performance into a coherent view. The coherent view I want is right there in one place, laid out sequentially for me to look at, and that coherent view fits what the business needs to be looking at, as in...
Scene 1:
- Business person: Our TPS Report is slow.
- Oracle person: Yes, our system has a lot of waits. We're working on it.
- (Later...) Oracle person: Great news! The problem with the waits has been solved.
- Business person: Er, but the TSP Report is still slow.
Scene 2:
- Business person: Our TPS Report is slow.
- Oracle person: I'll look at it.
- (Later...) Oracle person: I figured out your problem. The TPS Report was doing something stupid that it didn't need to do. It doesn't anymore.
- Business person: Thanks; I noticed. It runs in, like, only a couple seconds now.
