Origins of Performance Concerns

In general, within a two-tier or multi-tier client/server environment, there are essentially three areas where a performance concern can originate:

1) Network. There are two characteristics of networks that can contribute to performance concerns: bandwidth and latency. Bandwidth (ie. the size of the pipe) tends to be fairly easy to monitor, as is identifying insufficient bandwidth. For database applications, network latency (ie. when you turn on the water, how long does it take to start coming out the other end of the pipe) can have as profound an effect on performance as bandwidth, if not moreso. SQL*Net/NET8 and other database middlewares perform many calls and acknowledgements between the client and server. If there are significant delays between each small burst of information, each interchange simply has to wait for the other to respond. Delays due to latency can be very difficult to detect. It should be noted that latency-related delays can be the true root cause of a performance concern, or simply a symptom of poorly-written application code, inefficient client data access methods, or inefficient database middleware. Further, an application's response times may be acceptable over a LAN, but unacceptable over a WAN (where bandwidths tend to be smaller and latencies higher). For any particular network infrastructure, tools are typically available to monitor bandwidth and latency, although this topic is beyond the scope of this site. One method by which to gain insight is to compare a particular application with other similar applications. If two applications that perform similar work on the same database server over the same network path using the same database middleware have large differences in response time, then suspect the application; if the applications both perform slowly, then suspect the network. Although there are exceptions to every rule, most organizations with a reasonably modern network infrastructure (such as 10 or 100 Mbit Ethernet or other comparable topologies) will typically find that network bandwidth and latency may be symptoms, but are not the root source of a performance concern.

2) Client application. Intelligently-engineered multi-tiered client/server applications will at least attempt to keep the majority of the network traffic between the application server and database server rather than between the application server and the client. Two-tiered applications do not have as many options, but can still shift as much processing to the server-side as possible by leveraging database-level logic stored in constraints, triggers, stored procedures, packages, and functions. Whether two-tiered or multi-tiered, placing application logic in database-level program units tends to improve performance, as well as occasionally eliminating re-deployment of client-side applications. As mentioned above, other factors influencing client-side application performance include the language and development kit, the data access methods and components, the client-side database middleware, and the efficiency of the application code logic. An application that populates a 12 month by 3 year spreadsheet-like grid by issuing a query for each cell in the grid (ie. 36 individual queries) obviously will not perform as well as if it populated the 12 x 3 grid by issuing a single query. Likewise, note that certain client-side data access methods may favor performing operations such as multi-table joins on the client-side unless explicitly instructed to simply pass an unmodified SQL statement directly to the database. The following rule of thumb should be followed: an application should bring back approximately a screenful of rows and columns on any particular query, since the typical user can only view one screen at a time. If much more than a "screenful" is being returned for summary on the client, then re-construct the query to summarize the rows on the database, thus returning a smaller number of rows. Dozens of application development tools are commercially available today, and specific discussion of any of those tools is well beyond the scope of this site. However, do not assume that the client portion of an application (whether custom-written or commercially purchased) does not contribute to a performance concern until it's contribution has been reasonably disproven or, at least, questioned. Again, comparing the offending application with a very similar application should help by providing insight. In general, one will find that a significant percentage of performance concerns do indeed originate in the application.

3) Database server. By far, the greater percentage of performance concerns will originate on the database server. The DBA must become adept at analyzing and tuning the database from a general perspective as well as tuning specific applications. Instance and operating system tuning impact individual applications, and application tuning impacts general level measures. These two perspectives are inter-linked in such a manner that they are nearly impossible to seperate. However, the remainder of this section will attempt to segregate the techniques into specific categories while still indicating how these categories interact with one another.