I think you'll agree that ASH is quite a technical achievement and can be very useful during performance firefighting.

Before I delve into ASH internals, I will demonstrate a few ASH-based reports to give you a taste of what you can do with ASH. While OraPub's OSM tool kit contains around a dozen ASH scripts, I will demonstrate only a few. The underlying SQL is very simple and you can download the scripts for free and analyze them. Just keep in mind that what you'll see here is just a fraction of what you can do with ASH. My intention is to show you ASH's flexibility, ease of use, and spot-on ORTA diagnostic power.

Figure 5-21 is a simple instance-level ORTA report, yet it has significant differences that highlight ASH's power. First, notice the initial parameter of 15. This parameter sets how far back in time we want to start reporting. The reporting interval is set to start 15 minutes into the past and end at the present. In addition to the wait event details, ASH's session_state indicates whether the session was waiting for a non-idle event to complete or consuming CPU. This simple column enables us to determine the percentage of time waiting (queue time) and consuming CPU (service time). And because the wait event names are also stored, we can classify the queue time as we wish. As you know, I prefer simple classifications, and those can be seen in Figure 5-21.