RE: now what??
- From: <Paula_Stankus@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Aug 2004 10:54:45 -0400
I am definitely an advocate for LMT. However, I have read/heard some =
disturbing things about ASSM. In your mail you mention: "waits have =
been related to
the sequential and scattered file reads". =20
My understanding is that ASSM actually can increase I/O because with the =
bitmaps on each database block handling free lists versus a global free =
list - you run into the issue of data that should be in the same blocks =
(i.e. sequential data) actually more likely to be in different database =
blocks - increasing I/O.
Can anyone shed more light on this issue? Given that Robyn is already =
having I/O issues I don't think ASSM is where he needs to be looking and =
may actually worsen performance.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap
Sent: Tuesday, August 24, 2004 10:45 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: now what??
Robyn,
I'm sorry you lost the battle, but you don't need to lose the war...
Take it one user action at a time, beginning with the ones that are the =
=3D
most
important to the business. It is possible that there's a handful of
inefficient SQL statements out there that are dominantly responsible for =
=3D
the
queueing at your I/O subsystem. If that's the case, and if you can find =
=3D
and
fix them, then you can make the money invested into the CPU upgrade =3D
begin to
pay off.
The 10046 traces can give you the information you need to determine =3D
where
your problem root causes are and how much response time you can save by
addressing them. You're probably going to need to learn to read 10053 =
=3D
traces
to motor more quickly through the SQL optimization work you're going to =
=3D
be
doing.
Good luck, and <ad>let us know if we can help you; we've been =3D
there</ad>.
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 =3D
Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Robyn
Sent: Tuesday, August 24, 2004 9:33 AM
To: oracle-l@xxxxxxxxxxxxx
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.=3D20
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@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
-----------------------------------------------------------------
BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------
Teach CanIt if this mail (ID 8099141) is spam:
Spam: =
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Ds&i=3D8099141&m=3D6fcd4=
4112c87
Not spam: =
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Dn&i=3D8099141&m=3D6fcd4=
4112c87
Forget vote: =
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Df&i=3D8099141&m=3D6fcd4=
4112c87
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS
----------------------------------------------------------------
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:
