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: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 27 Aug 2004 10:31:00 -0500
Message-ID: <022101c48c4a$e4534ef0$6701a8c0@CVMLAP02>


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

-----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
-----------------------------------------------------------------
Received on Fri Aug 27 2004 - 11:58:27 CDT

Original text of this message

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