Oracle 10g has some neat features for keeping track of old statistics. Pre-10g It used to be important to backup your statistics before you analyzed a table just in case your plans went crazy. Now Oracle does this for you automatically. There is a great post on Doug’s Oracle Blog that talks about this with some examples. So now one can analyze as needed without fear of not being able to roll back the statistics to the previous value. In a crisis, it might be something to check to see if analyze recently ran and now plans are bad by selecting from tab_stats_history. Then simply back out the stats with dbms_stats.restore_schema_stats to revert back to a known good statistics state. Be sure to check dbms_stats.get_stats_history_retention and make sure you are keeping a long enough record of stats, you can adjust with dbms_stats.alter_stats_history_retention as needed to keep yourself sleeping at night.

Read More

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.

Read More

I was recently involved on a problem for a critical production database that had slow response time for inserts. The batch job inserting was running at full tilt, and adding more processes (concurrency) was not helping. Ah, there must be some contention going on. What was interesting was not the particular technical details of increasing insert performance, but rather, differing perspectives on how to figure out the problem, and how to suggest a fix. The insert process was inserting data into a long raw column of images. v$session_wait showed many sessions waiting on buffer busy waits.

Read More