The love of Data, Database Engineering, Architecture, Entrepreneurship, and other assorted bits

Performance Tuning: Be Proactive

09 June 2006

I see many articles out there talking about performance tuning using the Oracle waits interface. While I am not contending that technique; I believe it to be sound, and use this technique every day. What I am saying, is performance tuning in this manner, or the ‘traditional’ manner, is inherently reactive. Reactive performance tuning has it’s place, but many time some proactive performance tuning can help reduce the amount of reactive performance tuning a DBA needs to do. By it’s very definition, if one is reacting to some performance problem, it’s most likely it’s impacting someone, and perhaps your company is losing money.

Don’t wait for a performance problem, solve them before they occur.In order to be proactive with performance tuning, one needs a system, a plan, a technique. Proactive performance tuning is all about the trends a given database, or a suite of databases exhibit. One must develop a system in order to observe performance telemetry over time, and notice larger trends that don’t show up in the detailed wait interfaces when queried at performance-problem time. I gave a very simple example of this technique using the Oracle Statspack package in my previous post. Using that technique one could keep a running report showing the relative amount of work that a database is doing over time. How is this proactive performance tuning?It might seem simple, but if one knows the relative work ‘profile’ for a given database, then one can perform proactive tuning measures to AVOID problems. For instance, reschedule batch jobs, move backup windows, or even just communicate with your business about good times for promotions. It should also be noted, that such a system will also allow a DBA to observe the improvements that a given change makes on the trend as well. This is both good in terms of knowning when you are done tuning, but also to illustrate those tuning successes to yourself, and your management.

While it’s good to have good control of your reactive tuning efforts, augmenting those skills with a proactive tuning ‘system’ might just help you to use your reactive skills a bit less, and provide more value to your organization.