During an Oracle Performance Tuning engagement in the past, I learned a valuable lesson about validating the information that you are given during a database evaluation.
About ten years ago, a client called me with a serious problem. They had just completed development on a new Oracle application at a large pharmaceutical company and the new system had been in production for a week or two.
They could not understand why performance had degraded so rapidly during that period and the system was almost unusable by the time that I became involved .
The developers had been working in the development environment with similar data volumes on a system that was a mirror of production, and yet the performance on the production machine was very poor and was getting worse by the day. They called us in to help them find out what was wrong.
Of course, one of the first things that I asked about was the gathering of database statistics. Stale statistics can cause very poor Oracle query performance and the fact that the performance was getting worse daily was a pretty good indication of bad statistics.
I asked the DBA whether statistics were up to date and he assured me that they were. He said that he ran a job daily to collect statistics and that he had confirmed that it was running.
With that assurance, I went about seeking other causes for the problems. I checked the SGA size, index usage, and other factors that can lead to poor Oracle Database Performance.
After some time, I decided to check the stats myself because things just were not adding up. I found by looking in the Oracle Data Dictionary that on one of the key tables, the statistics had never been taken!
With this information, I asked the DBA again to confirm that stats were being collected. He confirmed that they were and even showed me the log file of the job that runs each night to collect statistics. This was before Oracle had the dba_stats package and he was using the Analyze command to collect statistics.
He was correct, there was an Analyze command that was running every night. However, the command that he was running included the ‘validate structure’ option. When this option is used, the Analyze command does just that, it validates the structure of the object, but does not collect optimizer statistics.
We removed the ‘validate structure’ option, reran the job to gather statistics, and the system performance returned to the expected level.
So the DBA answered in good faith that the stats were being collected because he thought they were. But they were not because he was using the wrong command.
This story illustrates the importance when doing a database performance review, or a database security audit, that even though you trust the persons responsible for the system, you must verify the results yourself.
