I’ve always been a huge fan of
Cary Millsap’s “Method-R” technique for
Optimizing Oracle Performance via detailed analysis of all of the business process wait states. It is fundamentally sound since it’s based on genuine business concerns and detailed analysis of a mission critical business processes’ overall execution time (and not simply just the database waits or ratios). But while many people like this approach, few have found the tools to support this technique. But Toad offers many screens for DB Wait analysis. Did you really expect me to say otherwise?
Let’s examine a few (most assume that you own the DB Admin module or bundle).
If we want to begin an end trace collection for a particular database session in order to create database trace files, then we simply use Toad’s Session Browser as shown below. Remember, all of the trace files will be on the database server – and we can retrieve them using Toad’s FTP Screen.
Figure 1: Toad's Session Browser
Once you have the trace file, you can then explore it using Toad’s Trace File Browser as shown below. Note that this screen offers a link to the FTP screen for convenience.
Figure 2: Toad's Trace File Browser
I could go on as there are numerous other screens to support this database wait centric optimization technique. But then the real question arises – “What does this all mean?” We’re all not wait event experts like Cary Millsap, Tom Kyte, Mike Ault and many other Oracle experts out there. That’s where Toad makes life easy once again – as all the most common wait events have pop-up windows to explain why they generally occur and how to possibly fix them. These pop-up help screens are very easy to locate as they always shows up as a blue underlined hyperlink on wait event screens. So when you double-click on them, you get help such as this.
Figure 3: Wait Event Explanation Screen
Below are screen snapshots of just some of the many other places where you’ll find this wait event explanation feature in Toad. Remember to simply look for database wait event information (i.e. tabs) on a particular screen, and then double click on those marked with a blue underlined hyperlink. Now we all can look like wait event know it alls J
Figure 4: Wait's on Trace File Browser
Figure 5: Wait's on the Session Browser, Sessions
Figure 6: Wait's on the Session Browser, Waits #1
Figure 7: Wait's on the Session Browser, Waits #2
Figure 8: Wait's on the Session Browser, Waits #3