As I introduce these two reports here, and explore their secrets in Chapter 5, pay close attention to where the same information can be retrieved from the tools you currently use. I have discovered that nearly all Oracle diagnostic tools and products have the information you need to perform an ORTA. It's just that some products make it easier than others.

In the OSM tool kit, by default, the categories are bogus (events usually not relevant to performance analysis), ior (IO read events), iow (IO write events), and other (everything else). Part of the tool kit installation routine runs a separate script (event_type.sql) that loads a reference table containing each wait event and its associated category. When I run response time-based reports, I simply reference this table to appropriately categorize the time. If you want to add a category or change a wait event's category, just modify the event_type.sql script and rerun it. From that point on, all the OSM ORTA reports will be based on your preferred categorization scheme. This flexibility also allows you to easily categorize new wait events or fix misclassifications.

Figures 2-15, 2-16, and 2-17 show the output from OraPub's instance-level (what I usually call system-level) interval ORTA report. The report is based on the rtsysx.sql script, which captures instance-wide response-time details for a specific time interval. As Figure 2-15 shows, the script starts by taking a snapshot of instance-level statistics (v$sysstat and v$sys_time_model) and instance-level wait-event statistics (v$system_event). During the 120-second interval, the script wakes up every 10 seconds, queries the active SQL from v$session, and stores the currently running SQL_ID. At the end of the report duration, another statistics snapshot is taken, the differences in time calculated, and the report produced. Nearly all this information can be gathered from a Statspack or AWR report. The benefit of using the rtsysx.sql script is the output is formatted for a quick ORTA.