Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: now what??
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
Sent: Tuesday, August 24, 2004 10:45 AM
To: oracle-l_at_freelists.org
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_at_freelists.org =3D
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Robyn
Sent: Tuesday, August 24, 2004 9: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.=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
BEGIN-ANTISPAM-VOTING-LINKS
![]() |
![]() |