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??

RE: now what??

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 24 Aug 2004 11:04:23 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DE95@usahm018.exmi01.exch.eds.com>


The idea or rebuilding your tablespaces to be locally managed using uniform extents sounds good but I do not know about ASSM. For small objects and/or small extents the overhead seems way too expensive.

IMHO -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Robyn Sent: Tuesday, August 24, 2004 10:33 AM
To: oracle-l_at_freelists.org
Subject: now what??

Gurus,

I am in the midst of living the examples given in several of your books. I spoke to a few of you at the Oracle-l dinner at Hotsos about this situation and the hypothesis has again proven true - adding more cpu to a system without a cpu bottleneck can actually slow it down.

For the last six months or so, I've been running 10046 traces on many of our problem processes, and all of the waits have been related to the sequential and scattered file reads, both in the number of waits and in the durations of the wait time. I've been able to redistribute the waits by working with the optimizer, the statistics and the sql. As a result, our nightly batch runtimes have been reduced by about 30%. However, others were convinced that the problem was really lack of cpu, new boxes were acquired and things have now slowed to a crawl for key user processes. We now have buffer busy waits in addition to the read waits, plus the duration of the various read waits appears to be longer in some cases since the hardware upgrade.

Now that the 2 seconds of 'I was right' enjoyment has faded, I need to put together a plan to fix it. Much of the sql should be rewritten and these file systems are swiss cheese - they've been adding bits of space to dictionary managed tablespaces for years. The databases are very large, 500 and 800 gb's or so. The equipment is not bad: 8 cpu HP-UX boxes with emc storage. The db's are 9.2.0.3 and will soon be patched to 9.2.0.5. (test db has already been patched and several key queries perform better with the patch)

The unix admins want me to break the files into smaller pieces because the drive queue waits are really long. I've been arguing for LMT's with uniform extents and ASSM since I got here anyway. Can rebuilding the storage objects reduce the durations of the sequential and scattered read waits, or should I focus my efforts elsewhere first? I have increased freelists on the objects with buffer busy waits, and the number of BBWs has been reduced, but they are about 1/4 the duration of the read waits so the improvement is minimal.

Advice and comments appreciated ...

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 Tue Aug 24 2004 - 10:08:03 CDT

Original text of this message

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