The Challenges Of Tuning

Tuning of information systems is one of the most challenging tasks and the true improvement of performance is an elusive goal. This is likewise true within an Oracle database environment and the application code which executes within that environment. Among the factors which complicate the tuning effort are these:

• Isolating the problem
• Improving response time
• Identifying external factors
• Addressing application and logical database design

Isolating The Problem

The first challenge is to isolate exactly where the performance problem lies. For example, in the case of a SQL statement which executes poorly, the performance of the statement may be only a symptom, not the actual problem. The underlying problem may lie within the overall database configuration or operation. Or it may be the PL/SQL application in which the SQL statement is found. Conversely, one may begin with a focus on the database or a PL/SQL program unit only to find that an embedded SQL statement is the true source of the problem.

Furthermore, SQL statement performance cannot be separated from the performance of the database in general. This often indicates that a collaborative approach to performance tuning is required. Database, system and network administrators typically address performance issues which arise from database configuration, host system configuration and network design. Senior database developers generally address issues related to SQL statement construction and database-resident program design using PL/SQL and Java. And both groups must work together in the diagnosis effort.

In the comments which follow, we will discuss further how these various interrelated factors can affect database and application performance.

About Response Time

In the context of SQL statement execution, response time is the time to receive a response from the database for a SQL statement, as the response perceived by the user or the application. The ultimate goal of a tuning effort will primarily seek to improve the response time.

Identifying External Factors

Other, more distant, external factors can also have a dramatic impact on performance. One of the most important is the hardware and software environment within the overall systems infrastructure. Just as a SQL statement is affected by the database environment in which it executes, on an even larger level the entire database installation is itself affected by the execution environment.

Therefore, tuning, configuring, troubleshooting the overall systems architecture is also critical. Similarly, adding additional hardware resources may be a necessary step.

Database installations, in turn, exist within a systems area (SAN), local area (LAN) or wide area network (WAN). Network design and configuration is another important factor. This is especially true with modern 3-tier and n-tier application environments. In such cases SQL statements may be issued from application servers, clients and users may be connected to web and HTTP servers, and so on.

Application & Logical Database Design

One of the most fundamental problems which can contribute to poor systems performance is poor application design. This could be the design of the logic within the application modules or the logical data model upon which the database application is based. It is nearly impossible to tune an application when its design is poor. Thus, the best database design and the most expensive hardware resources can all be squandered by poor application design.