Proactively monitor Oracle 9i performance via scripts and queries

Take a closer look at Oracle's performance monitoring tools. Familiarize yourself with the scripts and queries you can use to successfully monitor and tune the performance of your database applications.

With over 250 configuration parameters and thousands of metrics to monitor, it’s no small task for Oracle administrators to monitor the overall health of their Oracle databases. Oracle offers a variety of tools for performance monitoring, but there are quite a few of these as well. To be able to effectively monitor the health of your Oracle database, you’ll need to be familiar with the following scripts and queries:
  • Data Buffer Hit Ratio Alert reports times when the data buffer hit ratio falls below a preset threshold.
  • Redo Log Space Requests Alert can indicate trouble if the number of requests are greater than zero. You may want to increase the log_buffer parameter if that occurs.
  • Shared Pool Contention Alert tells you when there is contention within the shared pool and locking-related problems.
  • System Waits Alert query interrogates the Oracle event structures to locate events where there are excessive waits due to contention.
  • Library Cache Misses Alert query looks for excessive library cache miss ratios. When the library cache miss ratio is greater than .02, you may want to increase shared_pool_size as a remedy.
  • Database Writer Contention Alert looks for values in summed dirty queue length, write requests, and Database Writer Utility (DBWR) checkpoints. When the write request length is greater than three or your DBWR checkpoint waits, you need to look at tuning the database writer processes.
  • Data Dictionary Miss Ratio Alert script will alert you to times when requests for data dictionary metadata are high. You can sometimes relieve the problem by increasing the shared_pool_sizeinit.ora parameter.
  • Data Dictionary Object Alert report can reveal internal contention with the Oracle data dictionary and times of high dictionary metadata requests.

Take a closer look
Let’s take a closer look at how these scripts work. The STATSPACK utility takes time-based Oracle tuning information and records it in over a dozen tables. These table names mirror the v$ internal Oracle views and have names like stats$sysstat and stats$sql_summary. Knowing this, you can write simple Oracle queries that will show you trend-based performance information. You can then take this performance information and feed it into predictive models, such as linear regressions, which will accurately tell you the most appropriate times to change the internal structure of your System Global Area (SGA).

Listing A contains an example of the use of this performance information. This script produces a running total of the library cache miss ratio over time and references the stats$librarycache table.

The output of the script, indicating that you need to schedule additional RAM memory for the shared_pool_size during this period, either via a cron job or dbms_job, appears in Figure A.

Figure A
Output of the library cache miss ratio script

Dynamic performance reconfiguration
Table A gives a high-level view of some of the major events that are used to trigger a dynamic tuning reconfiguration. For the purpose of illustration, I will focus only on the major areas within the SGA appearing in the table.
Table A
RAM Area Too small condition Too large condition
Shared pool Library cache misses No misses
Data buffer cache Hit ratio < 90% Hit ratio > 95%
PGA aggregate High multipass executions 100% optimal executions
Major reconfiguration triggers

Obviously, a high amount of library cache misses indicates that the shared pool is too small, while a data buffer hit ratio of less than 90 percent for any one of Oracle’s seven data buffer pools indicates that you should take memory from other database regions and reallocate it to the data buffers. For sorting activity, you’ll take a look at the percentage of optimal executions within the Program Global Area (PGA), and increase the value of the PGA aggregate target parameter whenever you experience less than 95 percent optimal executions for sorting operations.

While the rules for the data buffer caches and shared pool sizing are straightforward, the new pga_aggregate_target parameter warrants further investigation. As a general rule, you’ll look at changing the value for pga_aggregate_target when the following occurs:
  • Whenever the value of the v$sysstat statistic estimated PGA memory for one-pass exceeds pga_aggregate_target, then you’ll want to increase pga_aggregate_target.
  • Whenever the value of the v$sysstat statistic workarea executions—multipass is greater than 1 percent, the database may benefit from additional RAM memory.

You can overallocate PGA memory and you may consider reducing the value of pga_aggregate_target whenever the value of the v$sysstat row workarea executions—optimal consistently measures 100 percent.

As you can see, the proactive monitoring of an Oracle database can get quite complicated. With hundreds of metrics and parameters to monitor and reset, Oracle tuning can be extremely challenging. But with knowledge of Oracle’s performance measures and major reconfiguration triggers, you can begin to sort things out.

Editor's Picks