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)
