Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: now what ?? continued ..

RE: now what ?? continued ..

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 27 Aug 2004 13:27:46 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKCEHGFEAA.mwf@rsiz.com>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap Sent: Friday, August 27, 2004 11:31 AM
To: oracle-l_at_freelists.org
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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Robyn
Sent: Friday, August 27, 2004 8:39 AM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Aug 27 2004 - 13:34:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US