O'Reilly logo

Oracle Internals: An Introduction by Steve Adams

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Wait Statistics

The Oracle wait statistics are pure gold—but not to be overvalued. Many types of performance problems are easy to identify from the wait statistics. If Oracle is waiting extensively for resources such as latches, free cache buffers, enqueue locks, and so on, then the wait statistics can both identify and quantify the problem. With experience, you may also be able to use the wait statistics to identify network and disk performance problems. The wait statistics also provide valuable feedback on attempts to resolve such problems.

But if your application is doing more parsing, or more disk I/O than necessary for its workload, then the wait statistics cannot help you. They will appear to give your instance a clean bill of health, and rightly so. The wait statistics are only able to reveal inefficiencies at the database server level and below. So they are silent about application-level performance problems that increase the load on the database server but do not cause it to work inefficiently.

However, you should already have addressed all the application performance issues before considering database server tuning in detail. If so, the wait statistics can have full value for database server tuning. But they can only have full value if the waits are timed.

Timed Statistics

Waits are timed if and only if the TIMED_STATISTICS parameter is set to TRUE. Let me endorse what others have said before, that the overhead of timed statistics is negligible. If you need to convince yourself, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required