Building Your Own Tablespace Monitor Part 1 of 3
By John Adolph Palinski
A DBA's sleep and time is precious. DBA's don't want to be awaken at night by an operator when a tablespace has been filled. Its possible for a DBA to check the freespace on tablespaces each day, but due to the many DBA tasks its very easy to overlook this check. A DBA really needs an automated tool that checks tablespace usage, predicts that a problem can occur, and notifies the DBA of a potential problem. Such a tool will save the DBA's rest and free the DBA from a task during the the work day. This is the first of three articles that will describe how to develop an automated tablespace monitoring and notification tool.
The tablespace monitoring application discussed in this article collects the freespace value from the Dba_free_space table. The application collects and evaluates this value several times a day. The smallest daily freespace value is stored in a Tablespace_readings table. A trend line based upon the last thirty days of readings is calculated and used to predict when the tablespace will be entirely used. To be especially cautious, a trend line based upon two standard deviations is used for the prediction. Two standard deviations is chosen because ninety-five percent of normal variations are expected to fall within two standard deviations of the computed trend line. The application also discards extreme deviations that may randomly occur for various reasons. The idea of the application is to give the DBA a modifiable tool that will predict problems and avoid as much as possible faulty predictions.
Notification is performed via e-mail. This helps the DBA by not requiring the DBA to remember to review the results. The DBA simply needs to look at e-mail to identify potential sleep disturbing products. Since the DBA likely reviews e-mail, this is not an additional task. Of course, graphs and reports can be made available for the DBA to review tablespace statistics. Below is a Discoverer Web report showing the various trend and tablespace lines. The graph illustrates that a tablespace problem will occur within the next week. The situation illustrated by the Web report will cause the application to launch an e-mail to the DBA. After receiving the e-mail notification, the DBA can review a similar graph or other report confirming the need for additional data files.
The below graph is important in understanding the prediction tool. Below are important points:
| |
- |
The X scale is time and is in reverse chronologic order. The predicted days and trend points are on the left and actual are on the right. Reverse chronologic order was chosen for easier readability. |
| |
- |
The graph has a dark green horizontal line. This line represents current tablespace size. |
| |
- |
The aqua line represents actual and predictive values. This graph was produced on Nov. 1, 2004. The aqual line values on the right are actual readings. |
| |
- |
One of the actual values is a 0 used reading. The reading was taken on a date the tablespace was empty. The reading is an aberration and is discarded by the prediction model. The predicted values are the left side of the line. Notice that it has the same general slope as the actual readings. If the aberration was kept, the trend would be downward. For this reason, the prediction model discards widely swinging values. |
| |
- |
The normal green line shows the trend line plus two standard deviations. This line is used to identify potential problems. |
Reviewing the graph, notice that the two standard deviation trend line crosses the tablespace line in early November. This is a prediction that the tablespace may run out of space in the next several days . The calculations correctly identified a tablespace problem. This graph illustrates an acutal condiction. As a result of the e-mail warning, a data file was added to the tablespace saving the DBA a late night call.
Figure 1 - Discoverer report and graph indicating a freespace problem
The following are the particulars of the application:
| - |
Prediction is based upon linear regression using the formula y=a+bx. |
| - |
This formula produces a trend line based upon the last thirty days of actual freespace readings. |
| - |
The trend line computes the next fourteen days of free space. The expected free space trend value is subtracted from the current tablespace size value in order to predict the expected tablespace used |
| - |
A second trend line is also computed based upon standard deviations. |
| - |
A standard deviation denotes a normal range of value movement. Standard deviations are used to identify a range in which normal values can be expected. The second trend line is based upon 2 standard deviations. It is expected that 95% of all possible values will fall within the two standard deviation range. The trend line based upon two positive standard deviations is used to predict a problem. |
| - |
A message will be sent indicating a problem if the following occurs:
1. The predicted tablespace usage based upon two standard deviations moves above the actual tablespace size value.
and
2. The slope of the trend line is increasing
and
3. Free space falls below the 5% of the tablespace size.
and
4. Free space falls below 400 meg |
| - |
The application drops some historical values from the computations. If freespace is greater than 95% of the tablespace, the value is not used in determining the trend. It is believed values in this range are due to abnormalities. |
| - |
The application will send a minimum of two e-mails a day to the DBA. The application collects statistics and performs its evaluation twice a day. An e-mail is sent specifying the application has run. A potential third and fourth e-mail can be sent if a problem is predicted. The application is not run on Saturday and Sunday. |
The first step in developing the application is to create the tables. The application uses two tables:
| |
Tablespace_growth_stats - |
This table contains an entry for each tablespace. It records the current tablespace size, the last freespace reading date, the current freespace, the largest daily decrease in freespace, the largest weekly decrease in freespace, and the largest monthly decrease in freespace. |
| |
Tablespace_readings - |
This table contains a daily tablespace freespace reading. |
The application can reside on any Oracle ID. However, the ID must have access to the following views:
| |
Dba_free_space |
| |
Dba_table_files |
The first step in installing the application is to create the two tables using the following the Listing 1 script. The file contains two Create Table statments. The first creates the Tablespace_growth_stats table. The second creates the Tablespace_readings table.
/* This script was created by John Palinski October, 2004. The script
creates tables that are used to store tablespace readings.
The purpose of the database
is to track the amount of tablespace freespace
and to support predictive analysis. The application
is to monitor tablespace and predict when the
tablespace may be used up.
The following scripts create the growth tables*/
spool /tablespace_create.log
create the tablespace_growth_stats
(tablespace_name varchar2(30) primary key,
original_freespace_mb number,
original_measurement_date date,
last_measurement_mb number,
last_measurement_date date,
last_week_measurement number,
last_month_measurement number,
largest_daily_increase number,
largest_weekly_increase number,
largest_monthly_increase number);
create table tablespace_readings
(tablespace_name varchar2(30),
measurement_date date,
measurement_mb number,
primary key (tablespace_name, measurement_date));
spool off
At this point you are ready to begin populating the application tables. This procedure will be further discussed in Part 2 of this article.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training and consulting services. One of his passions is to help customers set up logical business models to improve information efficiency and effectiveness. He may be contacted at www.oracle-trainer.com.
If you have any thoughts on this article or have ideas for new articles, contact me at realistic@radiks.net.
