Note: Burleson Oracle Consulting is the leader in Oracle Emergency Support and is available to assist with any Oracle database problem, anywhere in the world, anytime.

 

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.       Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.