Performance Errors in Oracle Databases
Oracle Tips by Mike Ault
In my 16 years of working with Oracle I have seen
the database grow and change as new features are added and old ones
removed or changed. However, even as things change, they remain the
same in many respects. We still must index properly, set memory
settings correctly and specify proper hardware.
You would think that by now everyone would have a
pretty good idea what an average configuration for an Oracle small,
medium and large database would be, but unfortunately these
classifications also undergo mutation. It wasn’t too long ago that if
you used several hundred megabytes of disks you had a large database.
Now of course large databases are in the terabyte and larger size
range. This makes setting tuning guidelines for Oracle databases
somewhat of a moving target where the rules change almost as fast as
you can derive them.
Given a changing environment for tuning Oracle is the
only constant I will try to present in this paper the major issues I
have found in the last few years while tuning several dozen different
client databases. I will try to present a method or methods for
determining set points that take into account this changing beast that
Major Issues of Concern
From a review of the tuning reports I have generated
over the last couple of years I have come up with a list of 6 major
areas that the Remote Remote DBA or tuning expert needs to pay attention to in an
Non-use of bind variables
Improper Index usage
Improper memory configuration
Improper disk setup
Improper initialization parameter usage
Improper PGA setup
Each of the above areas can have a profound impact on
the ability of an Oracle database to function properly. In the
following sections we will cover how to measure the affect of these on
your system, how to correct them once found and how to prevent them
from being issues in the future. Each of the above can be, and
probably is somewhere at this conference, a full hour topic, so we
have a lot of ground to cover, let’s get started!
SEE CODE DEPOT FOR FULL SCRIPTS
For more information on this topic, I recommend Don
Burleson's latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts:
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.