Burleson Consulting wrote the book on proactive Oracle database monitoring and table growth monitoring, a preemptive method to identify and correct anomalies before they effect your database.
 

RPM for Oracle is a proactive Oracle monitoring solution, expert techniques that form a preemptive protection layer to ensure continuous availability of all mission-critical Oracle databases. RPM for Oracle is a complete solution, running on an independent server for complete proactive monitoring of all aspects of Oracle, including the server, disk and network.

The Remote Preemptive Monitor has these components, all designed to provide early-warnings for impending problems.

What makes RPM special?

Unlike "dumb" alert scripts, RPM has additional intelligence to correlate complex interactions.  RPM is a true decision Support System and uses expert system technology to automate the well-structured components of proactive monitoring, but a human expert is required to diagnose and solve an impending problem.

RPM is a complete solution for any Oracle database, measuring all of the important Server and Oracle health metrics.  These are the Remote Preemptive Monitor components:

• RPM AlertPack - RPM has proprietary alert techniques that warn you of impending problems before your end-users are inconvenienced. 
   
• RPM ServerPack - This component of RPM monitors the operating system to warn of server resource problems that may effect Oracle availability.
   
• RPM AuditPack - This component of RPM allows you to get fast and accurate capacity planning details so that you never run short of disk or server resources.
   
• RPM ManagementPack - RPM provides a complete growth report showing important capacity planning details, including total database size and the growth trends of all key tables.
   
• Ion Monitoring - RPM comes with the Ion monitor, a predictive analysis tool to identify hidden performance trends and justify just-in-time database reconfiguration.

Oracle Remote DBA Alert Strategy

BC has developed the worlds most comprehensive Oracle alert strategy to ensure that we know about a database problem the instant that it occurs.  The BEI response center will often locate and correct an Oracle problem before your end-users experience a delay or outage.

Database Server Alerts

BC Remote DBA monitors your server with copyrighted technology to ensure that we know exactly what is happening in the Oracle database environment.

CPU Overload Alert – We monitor for high run queue values and track periods when the Oracle database server is overloaded.  This alert fires when (the CPU run queue > num_cpus) for more than 30 minutes.

RAM Memory Overload Alert – We constantly monitor for RAM memory overloads and provide our clients with complete reports showing time when the Oracle database server experiences shortages of RAM memory.  This alert fires when the server is experiencing real RAM page-in operations, triggered when a high scan rate (sr) is followed by high page in (pi) values.

Network Latency Alert – This alert fires when network latency (as measured by tnsping response time) exceeds the pre-defined threshold.

External Oracle Database Alerts (UNIX)

 The BEI Remote DBA monitor uses leading-edge technology to monitor every component of your database, and we offer the most comprehensive and sophisticated alert monitor in the world.  We are proud that we detect potential problems before they cause a production outage.

Trace and Dump file Alert – We check hourly for any new Oracle trace or dump files, and e-mail the dumps directly to our support center.

Alert log message Alert - This script e-mails any important alert log messages that are found in the alert log.

Low free space in archived redo log directory Alert - If the archived redo log directory become full, our Oracle database will hang up. This alert allows the Oracle Remote DBA to add space before the database hangs.

UNIX mount point space Alert - The script checks all datafile mount points in Oracle, including the UNIX Oracle home directory. Since most databases now use AUTOEXTEND ON, the Remote DBA must be constantly alert for file systems that may not be able to extend. If the free space in any mount point is less than the specified threshold, an e-mail alert will be sent to the Remote DBA.

Internal Oracle Database Alerts

Broken Job alert – This alert will report on any component of dbms_scheduler that has been disabled, or failed to execute at its scheduled time.

Servererror alert – This alert fires when the servererror detects an exceptional error. The servererror trigger code contains a filter to ignore non-critical server errors (e.g. single user application failures).

Replication backlog alert - This alert checks the queues for Oracle Streams for excessive enqueues and disk writes that are associated with Streams queue overloads.  We can also monitor Oracle DataGuard for excessive log transfer times.

Object cannot extend Alert - This report will alert the Oracle Remote DBA whenever an Oracle table or index does not have room to take another extent. This alert is obsolete if you are using tablespaces with AUTOEXTEND ON, but many Remote DBAs still keep this alert because they want to monitor the growth of the database tables and indexes.

Tablespace > 95% free Alert - This report sends an e-mail alert whenever any tablespaces contain less space than specified. Again, this alert is obsolete when using AUTOEXTEND ON, but many Remote DBAs still want to see the available space within each tablespace.

Object > nnn extents Alert - This report is very useful for reporting tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined, an e-mail alert will be sent to the Remote DBA.

Hot File Reads Alert - This script reports on files whose reads are greater than (25 percent or 50 percent or 75 percent) of total reads. This code compares the individual I/O for a file from stats$filestatxs of Remote DBA_hist_filestatxs with the overall I/O for the period specified. When you find a hot file, you may want to place these files in the KEEP pool or stripe them across multiple disks.

Hot File Writes Alert - This script alerts you to files whose write I/Os are greater than (25 percent or 50 percent or 75 percent) of total writes. This information can help the Remote DBA locate files that are consuming more than a normal proportion of I/O writes. You may want to place these files in the KEEP pool or stripe them across multiple disks.

Data Buffer Hit Ratio Alert - This report alerts the Remote DBA to times when the data buffer hit ratio falls below the preset threshold. It is very useful for locating times when decision support type queries are being run, since a large number of large-table full table scans will make the data buffer hit ratio drop. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools because the KEEP pool should always have enough data blocks to cache all table rows, while the RECYCLE pool should get a very low buffer hit ratio. If the data buffer hit ratio is less than 90 percent, you may want to increase db_block_buffers, buffer_pool_keep, or buffer_pool_recycle. Also note that the Oracle8.0 version of this alert is available.

Disk Sorts Alert - If disk sorts are greater than 100/hr, you may want to increase sort_area_size or tune SQL to perform index scans. This report is very useful for monitoring the amount of activity against the TEMP tablespace, and it is also useful for ensuring that sort_area_size is set to an optimal level. As a general rule, increasing sort_area_size will reduce the number of disk sorts, but huge sorts will always need to be performed on disk in the TEMP tablespace.

I/O Wait Alert - This code interrogates to report on any files with an excessive amount of wait activity. If the number of I/O waits appears excessive, the Remote DBA needs to investigate the cause of the waits. High I/O waits on files are commonly associated with buffer busy waits, and may be caused by tables with too few freelists.

Buffer Busy Wait Alert - Whenever you see buffer busy waits, you have a condition where a data block is in the data buffer but is unavailable. This type of contention is usually for a segment header block of a high-level index node block. Adding freelists for the object often corrects these wait conditions.

Redo Log Space Requests Alert - If redo log space requests are greater than 0, you may want to increase the log_buffer init.ora parameter. A high number of redo log space requests indicates a high level of update activity, and the Oracle log buffer is having trouble keeping up with the volume of redo log images.

Chained Row Alert - Table fetch continued rows greater than 3% of row fetches, you may have excessive disk I/O (because of LOB's or PCTFREE set too low). The table fetch continued row can also be triggered by reading data blocks with long columns that exceed the block size. This is common with rows that contain RAW, LONG RAW, NCLOB, CLOB or BLOB datatypes.

Shared Pool Contention Alert - Enqueue deadlocks can indicate contention within the shared pool and locking related problems. Enqueue deadlocks are associated with the deadly embrace condition where one task is locking resources and another task that is holding resources requests a lock on the resources of the first task. To prevent these tasks from waiting forever, Oracle aborts the tasks that requested the lock that caused the deadly embrace.

Full Table Scan Alert - Long-table full table scans are only legitimate when the query requires access to more than 40percent of ordered table rows and more than 7percent of unordered table rows. Excessive large-table full table scans may indicate poorly tuned SQL that is not using an index.

Background Wait Event Alert - This query interrogates Oracle to find events with high waits. When background events experience more than 100 time-outs/hr, you may have a locking problem.

System Waits Alert - This query interrogates the Oracle event structures to locate events where there are excessive waits. If you experience waits on latch free, enqueue, LGWR waits, or buffer busy waits, you need to locate the cause of the contention.

Library Cache Misses Alert - This query interrogates Oracle to look for excessive library cache miss ratios. When the library cache miss ratio is greater than .02, you may want to increase shared_pool_size.

Database Writer Contention alert - This query looks at Oracle for values in summed dirty queue length, write requests, and DBWR checkpoints. When the write request length is greater than 3 or your DBWR checkpoint waits, you need to look at tuning the database writer processes.

Data Dictionary Miss Ratio Alert - This query looks at the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio. This script alerts the Remote DBA to times when requests for data dictionary metadata are high. This problem can sometimes be relieved by increasing the shared_pool_size init.ora parameter.

Data Dictionary Object Alert - This report looks into Oracle to find individual objects that experience a row hit ratio. This report can reveal internal contention with the Oracle data dictionary and times of high dictionary metadata requests. ==   =====

BEI Remote Oracle Remote DBA