Oracle 911 - Emergency Oracle Support
by Donald K. Burleson
I work in a worldwide Oracle emergency support center (www.remote-dba.net)
where I receive calls from panicked clients all over the
world, each experiencing a serious loss of performance on
their mission-critical Oracle databases. I have worked on
systems that shut down entire factories due to poor
performance, leaving thousands of workers sitting on their
hands waiting for me to do something magical to restart
their assembly line. I have worked with hospital image
delivery systems when patients were literally in surgery,
with their doctors waiting for the information they needed
to save lives. I have worked on financial systems for which
downtime is measured in tens of thousands of dollars per
minute.
Oracle Silver Bullets
Contrary to the pontifications of theoreticians and
ivory-tower academics, there are many silver bullets for
Oracle performance tuning. By silver bullet, I mean a small
set of commands that quickly relieves an acute performance
bottleneck. Some of these techniques of just-in-time tuning
have been codified in Oracle10g with the Automatic Memory
Management (AMM) facility, in which the SGA regions are
changed dynamically to meet changing demands in processing.
Let’s take a close look at the real-world silver bullets that
I have encountered over the past 12 years of emergency Oracle
support. The following stories are true, verifiable accounts
(I have witnesses!) of Oracle databases in which a fast fix
was used to relieve an acute performance problem:
-
Fix missing CBO statistics
-
Replace an obsolete statistics gathering method
-
Initialize missing Oracle instance parameters
-
Add missing indexes
-
Implement
cursor_sharing=force
-
Implement the KEEP pool for small-table full-scanned tables
-
Change the CBO optimizer parameters
-
Add additional SGA RAM
-
Employ materialized views
-
Create bitmap indexes
-
Add freelists
-
Windows Oracle issues
To
those scientist DBAs who demand “proofs” that these methods
worked, you are not going to find them in this article. Most
of the production systems that I tune have hundreds of
segments and thousands of concurrent users. Trying to test a
hypothesis on a large running database is like trying to tune
your car while it’s flying down the freeway at 75 miles per
hour. It is impossible to reproduce the conditions of a
complex performance breakdown, and the emergency support DBA
is forced to rely on experience and anecdotal evidence to
guide their actions. (If you want proof that these techniques
work, just send me an email. I have lots of witnesses!)
Anyway, let’s examine each of these silver bullets and see how
a well-placed silver-bullet can save the day.
If you like Oracle tuning, you might enjoy my latest book
“Oracle Tuning: The Definitive Reference” by Rampant TechPress.
It’s only $41.95(I don’t think it is right to charge a fortune
for books!) and you can buy it right now at this link:
http://www.rampant-books.com/book_ 2005_1_awr_proactive_tuning.htm
