|
Please start using STATSPACK now to gather and keep
statistics. You are certainly going to need "before" and "after"
statistics to analyze.
Some questions:
- Why does the development group think that I/O is
the problem? Have they been gathering data? Have you seen
it? Do you concur that their data proves that I/O is a performance
problem belonging to the Oracle database?
- Let's assume that there is an I/O problem.
There are two ways to address I/O (as stated in the YAPP report of www.oraperf.com): reduce the *cost*
per I/O request or reduce the *number* of I/O requests. The former
implies getting a better/faster I/O subsystem, redistributing I/O load to
different volumes, etc. Not trivial. The latter implies improving
the Buffer Cache Hit Ratio (BCHR) by increasing the size of the Buffer Cache
or it implies making queries more efficient, so that they simply don't issue
so many I/O requests (either to the Buffer Cache or to the
disk).
Gathering STATSPACK data and searching for the SQL
statements generating the largest number of "physical I/O" requests might be
illuminating for the developers. If you work with them on a one-by-one
basis on tuning each of these SQL statements, you might see dramatic
improvements in performance.
Suggest to them that *after* you are confident that
there are no tunable SQL statements, then you might consider increasing the size
of the Buffer Cache. Doing so is a last resort, not a first
response. This is because doing so does not fix the real problem, it only
accomodates the real problem, which is inefficient SQL.
Hope this helps...
-Tim
----- Original Message -----
Sent: Monday, March 03, 2003 10:59
AM
Subject: Big SGA.......
hey folks..
Hoping
for a little feedback and opinion please. Having a discussion with the
development group ...
The development
group is thinking that a VERY LARGE SGA would solve some of their I/O
problems. For example, they believe that a SGA consisting of over 8GB of db
block buffers would resolve their multitude of issues. I feel that they
open another can of worms with something such as this.. And granted-there
hasn't really been an infrastructure evaluation-and the SA group is currently
performing that review of the environment.
One could suggest
that they could "cache" some very large tables in the SGA; but there seems to
be some sense of a down side to this.. Could you all provide some input on
"Extremely large SGA's"? In the area of 8GB or so.. BUT, most of
this would be the database blocks. Would you all be so kinds to provide your
thoughts please?
TIA
Greg Loughmiller
Sr Manager -
Enterprise Data Architecture
gloughmiller (IPS)
678.893.3217 (office)
| 