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.

Throughout this book, I will explain the various parts of the report. For now, I will just provide a broad overview.

* The next sections focus on the IO situation and then the non-IO situation. The SQL that was identified during the report interval has its key performance attributes also displayed, providing an understanding of what SQL was involved in the workload, service time, and wait time situation.