A New Performance Philosophy

While Oracle Database 10g has introduced major changes in many areas, there has been significant improvement in the area of performance monitoring and tuning. Indeed, the philosophy in this RDBMS version has been one of automation, a philosophy that has been fully implemented in this important component.

Traditionally, the Oracle RDBMS has not suffered from nonavailability of detailed performance statistics. Rather, users have lacked both a consistent method of easily exposing and using these performance statistics, as well as tools that analyzed these statistics and provided sane advice. Although the aptly named Oracle Wait Interface has existed since Oracle 7.0, only after the turn of the century has it become popular as a method used for tuning. Since then, however, a number of Oracle-provided and third partydeveloped tools have tried to fill the gaps and attempted to distill these statistics into meaningful information that can be used to tune the database.

Performance Analyst

"Performance analyst" is a special role that has the responsibility of tuning the database and the application code in order to obtain better performance from the system as a whole. In most cases, the Database Administrator (DBA) is tasked with this role, because this person usually deals with the internals of the database. It is not necessary, however, that the DBA alone should fill this role. It can be equally well handled by anyone who has adequate knowledge, the right privileges to get to the variety of performance statistics that the Oracle Database 10g can provide, and more importantly, the persistence to go after the problem at hand. Knowledge of SQL, PL/SQL, and the database is a must for this performance analyst. Some knowledge of operating-system performance characteristics and the underlying hardware, although not essential, can be of great help.

We make constant reference to the Oracle Wait Interface and Wait events in this section, and hence it is necessary for you to understand what we are talking about. Essentially, the Oracle Wait Interface is a method of identifying database performance issues by viewing, monitoring, and analyzing Wait events, with the purpose of reducing or removing the waiting portion of an Oracle process. An active Oracle process moves through many stages in its life. At any point of time, it is in any one of the following states:

• Starting up or shutting down

• Executing on the CPU

• Waiting in the CPU queue to execute

• Waiting for an event external to the process to complete

It is this waiting for the external event that the Oracle Wait Interface describes. The Oracle kernel can expose the event for which a process is waiting at a point in time, and thus the term Wait event. These events include waiting for disk or network I/O to complete, waiting for a shared resource such as a lock or latch that is being used by another process to be released, and so on. Each of these Wait events is named, and the total time waited and number of times the process waited on that particular event is tracked and exposed via a variety of internal V$ views. Understanding and quantifying these Wait events thus becomes key to performance analysis and remediation.

In Oracle Database 10g, the Wait events are classified as well, which helps a performance analyst to quickly assess the significance of various events without knowing lower-level details. The number of Wait events has also grown significantly in Oracle Database 10g. Essentially, it means that more and more sections of the kernel code have been instrumented to record the event as the session passes through it. In fact, while Oracle Version 7.3.4 had about 100 Wait events, Oracle 8i recorded over 200 such events and Oracle 9i doubled that number to 400. Ultimately, Oracle Database 10g Release 1 records over 800 Wait events!

The Oracle Wait Interface has been the subject of many books, articles, and discussions. We will not go into details here. But as we said before, we will make reference to these events. A complete list of events is detailed in Appendix C, "Oracle Wait Events," of the Oracle Database 10g Reference Manual. This section of the manual is also not new; it has been available since Oracle Version 7.0.

What's in a Name?

The term Oracle Wait Interface has never been officially recognized by Oracle. It is, however, generally well known and widely accepted in the Oracle user community, and hence we will use it. A search of the technical notes at Oracle's MetaLink site throws up lots of links for the term "Wait event," but not a single reference to the "Wait Interface." The OWI, as it is affectionately known in its short form, actually evolved from another acronym, YAPP, which stands for "Yet Another Performance Profiling" methodology, but that is entirely another story! Whatever you call it, remember that it is based on analyzing the Oracle Wait events and using this information to detect and correct performance problems.

Performance-Management Tools

Before looking at what Oracle Database 10g provides, it is wise to look at what these Oracle-provided and third-party tools lacked so that you can understand why they did not fully serve their purpose. The Oracle-provided STATSPACK is one such tool. It is free and is relatively well documented. It is also widely used, and hence we have chosen this standard tool to critique the previous philosophy so that you can appreciate the new. In any case, most other tools provided a GUI-based view of the very same performance data that STATSPACK also used.

STATSPACK Shortfalls

First of all, STATSPACK had to be installed and configured manually. The DBA had to first install STATSPACK in the database and then set up scripts or jobs to collect snapshots of performance data at an appropriate interval. The DBA also had to manage the maintenance of STATSPACK data, performing purges to keep the amount of data in check. The DBA then had to manually generate reports using the Oracle-supplied SPREPORT script, which determined and listed the differences in statistics collected between two snapshots. A database restart between snapshots rendered the statistics for that pair meaningless, because they are essentially snapshots of counters that get reset during the restart eventand the DBA was supposed to keep track of this! The DBA then had the most difficult task of all: making sense of the reams of figures that this report generated.

As well, there was no standardization of the snapshot schedule, and this resulted in many DBAs performing snapshots for intervals that essentially rendered the information meaningless. For example, if snapshots were performed once in the morning at start of business day, and again at the end of the business day, peaks in usage during the day could not be determined from the collected snapshotsthe peak values would be lost due to the law of averages, rendering the whole STATSPACK collection virtually useless. To top it all, there was no GUI interface to STATSPACK dataeither for management or for interpretation.

STATSPACK also cannot determine the negative effects on the overall performance caused by a single offending session or even a small number of such sessions because the system statistics are collected globally and averaged out over both active and inactive sessions. For example, in a STATSPACK snapshot, a total wait time of 100 seconds accumulated by 100 connected sessions would be calculated to have an average wait time of 100 seconds /100 sessions = 1 second of wait time per session. This is obviously untrue and provides an incorrect picture of the problem. In fact, you may not even notice that there was a problem.

Although SQL statements that violated some preconfigured limits could be captured via STATSPACK, there were no in-built tools that connected them back to performance problems and issues. There was also no way to perform a comparison between multiple occurrences of a particular high-usage SQL so that you could determine what occurred between two snapshots. Thus, there was no way of tying a performance issue back to a particular SQL statement.

Finally, although no performance analysis can be considered complete without due consideration of operating-system performance statistics, STATSPACK does not collect or store this information.

Extending STATSPACK

It is possible to extend STATSPACK to collect, store, and report additional information by adding to the various tables and SQL code. This is neither standardized nor documented, however, and hence adds to the confusion.

Oracle Corporation seems to have recognized these shortfalls and prevented them by taking the following actions:

• Installing the Automatic Workload Repository (AWR) collector, the Automatic Database Diagnostic Monitor (ADDM), and other advisors by default during database installation

• Offloading collection, management, and processing of performance-related data to two separate Oracle background processes, named MMON and MMNL. This includes capturing both Oracle-related as well as operating-system performance statistics

• Providing the ability to capture performance baseline figures and alert against violations to these baselines

• Providing advisors, such as the SQL Tuning Advisor and SQL Access Advisor, that operate against this captured data

• Providing both a GUI interface from OEM as well as API calls from SQL to this data

Another major weakness in the previous versions was the lack of history information with regard to performance statistics. Apart from the STATSPACK snapshot information that was collected periodically, there was no way to determine what occurred in the immediate past. For example, suppose that a user is suddenly facing a performance issue in a form that normally performs well. She calls you to say that for the past five minutes, the response from this form has slowed considerably. In previous versions, there was no way for you to determine what occurred. To track down this issue, the DBA would have to either attempt to recreate it in a controlled environment with extended SQL tracing turned on or resort to high-overhead monitoring tools that sampled session information with very short time periods.

In Oracle Database 10g, however, this information is automatically available for all connected and active sessions in the Active Session History (ASH). The Oracle kernel snapshots session information, stores it in a temporary circular buffer within the System Global Area (SGA), and exposes this via the V$ACTIVE_SESSION_HISTORY and other views. Using ASH, a DBA can perform an on-the-spot analysis, without having to resort to the other complicated means described previously.

Another major change in Oracle Database 10g was the introduction of the Time and Wait model. The response time of a database action that produces meaningful work in the database consists of two components: service time and wait time. These can be summarized using the following formula:

Response Time = Service Time + Wait Time

Service time is the time spent by the process in the CPU, usually clocked as the user time from the operating system, and wait time is time spent by the process waiting for specific resources to be available before continuing its processing. Examples of wait time include the time spent waiting for I/O operations to complete, or even the time spent by one process waiting for a conflicting row lock to be released by another process. Thus, to reduce the response time and hence increase the performance of database actions, either the service time or the wait time (or a combination of both) must be reduced. In order to do this, you must be able to drill down into both the service and wait times and determine which components of these two has contributed most to the timings.

Service time is exposed in the V$SYSSTAT view at the database level and in the V$SESSTAT view at the individual session level. In a similar way, wait time is exposed in the V$SYSTEM_EVENT view at the database level and in the V$SESSION_EVENT view at the individual session level.

Service Time in STATSPACK

Oracle Corporation seems to have recognized the importance of figuring the service time in STATSPACK. As compared to the Oracle 8i version or even the Oracle 9i Release 1 version, the Oracle 9i Release 2 version of a STATSPACK report includes DB Time in the renamed "Top 5 Timed Events" section. The earlier versions listed only the Wait events and titled the section "Top 5 Wait Events."

In earlier versions, the service and wait times were not classified; thus, it was not an easy task to determine what contributed most to the issue at hand. The Oracle Wait Interface was able to expose some of the components of the wait time, but the service time (or CPU time) was not well understood or exposed. Oracle rectified this problem with the introduction of the Time and Wait model, described in the "Time and Wait Model" section later in this chapter. Essentially, using this model, a performance analyst could easily classify and thus isolate the various components of service and wait time, leading to intuitive and quicker performance-problem diagnosis and rectification.

Thus, out of the box, Oracle Database 10g provides preconfigured, easily accessible tools and advisors that deal with all aspects of performance. When understood and used properly, they provide a well-structured and automated performance toolkit. Although we will discuss AWR, ADDM, and the advisors in great detail in subsequent chapters, we have mentioned them here so that we can provide an overall sense of where they fit and what they can contribute to the whole. In essence, Oracle Database 10g automates, standardizes, and simplifies many actions that were previously done manually for performance monitoring and tuning. This frees the performance analyst from carrying out mundane tasks, allowing them to instead concentrate on more complex ones, and enables the less skilled to come up to speed quickly on this important but often ill-understood area of database operations.