The Old Fashion of Oracle Performance Optimization

Some say you need to know what life was like in the old days before you can really appreciate the life you now have. This is also true in the world of Oracle performance optimization. Early versions of Oracle did not offer a reliable method to identify performance bottlenecks. Performance optimization was a difficult and complicated task. Everyone used cache hit ratios as the yardstick to monitor database performance. To fully appreciate the OWI tuning methodology, you must be aware of the problems and limitations of the hit ratio ‚ based tuning method. For many of us, this is a trip down memory lane, but for those of you who didn ‚ t grow up in the ratios-based tuning era, you may embrace this as a piece of your predecessor ‚ s history.

Since the beginning of Oracle RDBMS, Oracle DBAs were taught to tune the database and instance by watching a few ratio numbers . The idea was to keep all database elements operating within acceptable ranges or limits. Some of the memorable ratios are the buffer cache hit ratio, library cache hit/miss ratio (Oracle7.0), and latch get/ miss ratio. Who can forget these commandments ?

• Thou shalt keep thy buffer cache hit ratio in the upper 90 percentile.

• Thy data dictionary misses must be under 10 percent at all times, and thy library cache shall not covet thy data dictionary ‚ it shall have its own ratios.

• Thy SQL area gethitratio and pinhitratio must also be in the 90 percentile at all times. Furthermore, the ratio of reloads to pins must not be more than 1 percent. If thy ratios are bad, thou shalt increase the shared pool size but thou shalt not steal the memory from the buffer cache. And while you are adding memory to the shared pool, throw some memory at the buffer cache also. It will increase the cache-hit ratio.

• Thy willing-to-wait latch hit ratios shalt be close to 1. If not, thou may increase the SPIN_COUNT but thou must be careful not to kill thy CPUs. And so on.

Lost yet? We are. Life can be much simpler, not to mention better.