Monitoring

There are essentially two areas that benefit from monitoring on a regular basis: the database instance performance measurements, and the operating system performance measurements. There are an overwhelming variety of performance monitoring tools available on the market for either of these functions, but this article will discuss two of particular interest and simplicity: the ORACLE Utlbstat.sql/Utlestat.sql scripts for instance monitoring and the NT Monitor service for O/S-level monitoring.

ORACLE Instance Monitoring using the Bstat/Estat scripts

The UTLBSTAT.SQL and UTLESTAT.SQL scripts can be found in the <ORACLE_HOME>\RDBMSx\ADMIN folder. Running the BSTAT script will create a series of tables, and record a snapshot of information from the ORACLE instance's dynamic performance views. Running the ESTAT script will record a second snapshot, generate a report showing the delta of the performance measures over that period of time, and drop the tables created by the BSTAT script. (Please see the examples under the scripting article for a minor modification that causes these tables to be created on a non-SYSTEM tablespace.) This generated report will be a simple text file containing a wealth of information. Using this report, instance level parameters needing modification can be identified, file I/O balancing can be scrutinized, etc. Since this information is so comprehensive, it is strongly suggested to get an occassional perusal by ORACLE Support: some of the more obscure measurements may generate instant recommendations from technical analyst's who analyze these types of reports on a regular basis. Alternatively, the DBA can learn exactly what all of the measurements found in the report mean and what the target values should be, but keep in mind that these measurements and target values change over time and with each release. Most of the measurements will contain header section comments indicating what ranges of values are desirable, and (in some cases) suggestions on what parameters or structures to modify to obtain values in a desirable range. Note that the TIMED_STATISTICS initialization parameter must be set to TRUE for these measurements to be available. Turning TIMED_STATISTICS on requires an instance shutdown/restart and results in a minor level of increased overhead. In the author's opinion, the information available with TIMED_STATISTICS turned on more than offsets the associated increase in overhead. (In practicality, any monitoring tool or technique will always increase overhead to some degree.) To maximize availability (by avoiding the instance shutdown/restarts), it is perfectly acceptable to leave TIMED_STATISTICS turned on during normal operation, in the author's opinion.

If TIMED_STATISTICS is indeed left in the TRUE setting, then it is suggested that the resulting available information be exploited to it's fullest degree by automating the collection. No additional overhead is incurred by running the BSTAT/ESTAT scripts (other than creation and dropping of the necessary tables), so a nightly scheduled job that runs ESTAT, renames the report with a date string, then runs BSTAT can provide a collection of performance and usage information from which long-term growth and usage trends can be determined. (If the report is not copied or renamed in some manner, it will be overwritten each time ESTAT is run.)

Please see the example of this operation in the scripting article in this section. Please see the performance section for more information on instance-level tuning.

Operating System Monitoring using the MONITOR service

As mentioned in a different section, NT Performance Monitor is a very effective tool for collecting performance information at the operating system level. This collection of O/S performance data can be automated just as easily as collection of database instance measurements. The MONITOR service is included in the NT Resource Kit, and is essentially a non-graphical, small footprint service version of the NT Performance Monitor. In fact, the data collection configuration for the MONITOR service is set up and saved as a Performance Work Space file using the GUI Performance Monitor, then registered as the configuration file for the MONITOR service. The DATALOG.EXE application is installed as a service by the MONITOR.EXE command (and can, in fact, even be done remotely). As well, the MONITOR command is used to locally or remotely start, stop, and register or switch the configuration file for the MONITOR service. Recalling the performance section of this web-site, the most useful information to collect will be the objects and counters detailing overall System -> %CPU utilization, memory -> memory paging, and logical disk -> transfers/sec and sec/transfer. (Please see the performance section for more detail.) Running the MONITOR service generates a minor overhead, but the requirements are not particularly expensive considering the information that becomes available for analysis, especially if the polling interval is set to one or more minutes. The data that is collected can then be analyzed offline using Performance Monitor, or exported into a spreadsheet for analysis.

The performance logs generated by the MONITOR service can grow quite large over time. It is suggested that a nightly (or other appropriate interval) scheduled job be constructed that stops the MONITOR service, renames the performance log with a date string (perhaps even compresses it), then restarts the MONITOR service. Alternatively, a scheduled job could start the MONITOR service at a specified time, and submit a job to stop the MONITOR service and "date-name" the performance log several hours into the future. Regularly collecting this information is suggested, though, as this allows usage and growth profiles to be constructed that allow anticipation of future resource requirements.

Please see the example of this operation in the scripting area of this section. Also, see the performance section for information on operating system tuning.