General Indications

Whether the performance concerns are identified through proactive monitoring or through complaints from end users, a rule of thumb that can be used to determine where to start looking when performance concerns are identified is the following:

1) Low variability in response times = perform application-specific tuning analysis.

In other words, a given query is always too slow, regardless of what time of the day the query is run. In this case, verify that general level measures are acceptable, but concentrate on tuning the application through indexing, optimizer hints, seperation of tables and indexes, modifying certain types of object storage parameters, checking for poorly syntacted SQL, checking for poorly performing client/server data access methods, etc. If the variability is low, the performance concern is likely to originate in some characteristic specific to that particular application, such as additional indexing needed. Note that tuning at the application-specific level can have a tremendous impact on the tuned application. Most notably, indexing can result in groups of queries that execute (on the average) 3-15 times faster than the un-tuned application. In fact, the author has personally seen extremes ranging from queries showing no improvement (or even executing slower than before) to queries that execute over 700 times faster after detailed and creative indexing. Application tuning is unquestionably a skill worth developing to the fullest extent reasonable for the individual.

2) High variability in response times = perform instance-level tuning analysis.

For instance, a given query is quick in the mornings, but takes four times as long to execute in the afternoons. In this case, verify that the application is reasonably well-tuned, then scrutinize general level parameters (both for the database instance and the operating system instance) such as buffer-cache hit ratio, sort area hit ratio, CPU utilization, memory paging rate, and disk workload. This type of performance degradation is more likely to be a function of the user workload, number of users performing transactions or queries, etc., due to contention of concurrent processes for available resources. If the variability is high, the performance concern is likely to be either an instance-level initialization parameter or configuration issue, or one of the hardware resources visible at the operating system level. Luckily, there's essentially only three basic hardware areas to check from the O/S level: CPU, memory, and disk. At the ORACLE instance-level there are many initialization parameters and configuration options that impact performance. Note that tuning on the general level is unlikely to generate huge improvements in individual applications. However, situations where resources are being over-utilized or contention forces operations into a queued state tend to result in exponential performance drops. Corrective actions such as modifying an appropriate initialization parameter, balancing disk I/O, or adding physical memory can remove several hundred percent of variability in response time. A significant percentage of the time, the outcome of general-level analysis will be an initiative to add hardware resources, so careful analysis is necessary to insure that the monies are spent on the resources that are indeed needed to alleviate the performance drop.