RE: now what ?? continued ..
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Aug 2004 13:27:46 -0400
Read this carefully lest you think I'm disagreeing with Cary, when in fact
I'm agreeing but warning you to check which workload you need to optimize.
If you're answering an expressed user need for something to be faster,
you'll know the answer to the question, but if you're just playing with the
carburetor to get a little more rubber to burn off the line, then, well,
your choice on this won't really matter.
You mentioned that you're about to process month end. If you need to make
big batch processes where you cycle through a ton of data such that optimal
means reading a lot of big stuff sequentially, then prefetch *probably*
helps you.
If you need to make general OLTP faster, and those longer wait times of
"2.91" cause significant delays in processes folks care about, then there is
a *chance* (depending on the actual dynamics of your subsystem I/O and
cache) that turning off prefetch will help you. Cary described precisely the
dynamic where the prefetch is a cost only operation.
I can't even really quibble with notion that good SQL *normally means*
returning a small number of rows. Most of the time that is true. Only in
situations such as batch periodic processes that inherently must process a
lot of rows is the prefetch likely to help you consistently. So the folks
who didn't want you to make the switch right before month end were probably
exhibiting wisdom. You'll probably know the right answer when you get the
results of those test batch runs. I'm not sure whether that feature is
dynamically alterable, but it is precisely the type of feature that should
be dynamically alterable to allow best utilization of resources for
different workload mixtures in various workshifts and in accomodation of
periodic events. Then you *might* find that turning off prefetch during the
month and turning it on for batch windows was the optimal mix.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap
Sent: Friday, August 27, 2004 11:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: now what ?? continued ..
I had good luck on a project several years ago by disabling the prefetch
mechanism. Especially if your SQL is pretty good (which normally means
you're returning a small number of rows per query, and you're using
indexes), a beneath-the-Oracle-layer prefetch is not a good idea, =
because
most of your reads will be single-block reads from disparate places on =
disk.
Think of the way indexes are stored physically versus how they're =
accessed
logically. Logically "adjacent" blocks (from branch level n to branch =
level
n+1, or from leaf n to leaf n+1) are likely to be physically =
non-adjacent.
So a prefetch is causing your I/O subsystem to do a little extra =
preparatory
work to go get blocks (or "a block") that ages out of your I/O subsystem
cache before it's ever used.
I think this is what you're seeing in your numbers.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New =
Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Robyn
Sent: Friday, August 27, 2004 8:39 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: now what ?? continued ..
Gurus,
I'm still slogging through various trace files, and one thing that has
been consistent both before and after the hardware upgrade is the max
wait time on scattered and sequential reads seemed overly long. EMC
has been in and they say everything looks wonderful - BUT I just found
out yesterday that the system has prefetch =3D 1. Since we're not short
on memory, it seemed like this should be set back to 0.
So, we tested it. Our test warehouse was also set to 1, so I
restarted the database and ran one of the problems queries with a
10046 trace. Here's the key info from the overall totals:
db file sequential read 64 0.01
0.32
direct path read 6 0.00
0.00
db file scattered read 12504 1.12 =
152.61
The admin shut the box down, changed the prefetch setting to 0 and I
repeated the same process with these results:
db file sequential read 64 0.02
0.32
direct path read 6 0.00
0.00
db file scattered read 12504 0.26 =
146.39
I think I'm seeing that the max wait time on the read dropped from
1.12 to .26, which would be a significant improvement, especially
since max wait times on our production box have been recorded as high
as 2.91. However, management is a little hesitant to make any changes
this weekend, since it's a month-end, no change weekend and last weeks
changes didn't go so well. Does anyone have any additional info on
the prefetch kernel parameter for HP-UX 11 that could help us
make/influence a go/no-go decision? I've search various reference
sites, but info on this one was a little thin. We left the test
server in the prefetch =3D 0 mode last night, and I should be able to
pull the timing results for the nightly batch runs soon.
tia ... Robyn
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
