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

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

now what ?? continued ..

From: Robyn <robyn.sands_at_gmail.com>
Date: Fri, 27 Aug 2004 09:39:21 -0400
Message-ID: <ece8554c0408270639b8bae72@mail.gmail.com>


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 = 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 = 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
Received on Fri Aug 27 2004 - 09:10:31 CDT

Original text of this message

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