26 March 2007

Why Wait?

Yes I spent 10 minutes trying to come up with a clever "wait"-based title and that's all I could come up with. I even Googled for "wait" to find some inspiration.

And now to tell you why "wait" ... the Oracle 10g wait interface!!!!!11 Feel my excitement. I just recently read some articles by Robin Schumacher about the 10g wait interface to analyze possible reasons for why a query might take so long. Is it waiting for disk I/O? Is it waiting for CPU? Well Oracle 10g provides some great views to see exactly what is waiting on what. The Schumacher articles are "Response Time Analysis Made Easy in Oracle Database 10g" and "Exploring the Oracle Database 10g Release 1 Wait Interface". The latter has the "only applies to Release 1" disclaimer, so caveat emptor. The articles do overlap a bit, so perhaps the first article will suit you enough.

I have to admit that a lot of it has left my brain already. I'll have to take timeouts and do random checks. I did run the query to get database wait time and CPU time ratios. Let's just say that my production database was nowhere near the great ratios that Robin was seeing. Hopefully some of the adjustments I plan to put in next month will bump things in my favor.

No comments:

Post a Comment