|
Sybase, Schmybase, Oracle, Schmoracle -- the
concepts are still the same. Developers create tables and indexes and then
write SQL, thinking that the RDBMS is at fault if performance doesn't match
expectations.
They have to understand that the structures they
have created or the queries they have written may simply be inefficient,
expending too much work. I don't know how to measure that in Sybase, but
I'm reasonably sure that there must be a way.
I used to joke that I could get
Oracle ERP/Apps to run on a Palm Pilot if I were permitted to
really tune the SQL. The work performed by an application is not an
immutable monolith, especially with the Oracle RDBMS and all of the performance
statistics it keeps. It is very much susceptible to
improvement.
First, they must make a reasonable attempt to *fix*
the problem (by making SQL more efficient). If that doesn't work,
then they should *accomodate* the problem by buying more hardware,
increasing buffer sizes, etc. The key with the latter approach is to
realize that you haven't fixed anything, only accomodated it by throwing
resources at it.
Pop quiz: Think of a parent with a spoiled
child who is making a scene in public. How do you quiet the child?
:-)
----- Original Message -----
Sent: Monday, March 03, 2003 2:28
PM
Subject: RE: Big SGA.......
one
little piece of information..(considered critical
probably:-) )
There isn't an opportunity to use statspack... The
current application is running on sybase:-)
I do
have other teams researching the questions you mention. its a real fun
project...
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)
|
