Building Your Own Tablespace Monitor Part 2 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 during the 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 second of three articles that describe how to develop an automated tablespace monitoring and notification tool. The initial article, published on www.sswug.org, described the application and its underlying tables. This article will describe the PL/SQL procedure that populates the tablespace readings.
The application has two tables. These are the Tablespace_growth_stats and Tablespace_readings. The application executes the Listing 1 procedure. The procedure performs queries against the Dba_free_space and Dba_data_files tables. It evaluates and inserts several values from the Dba tables into the application tables. The values are:
| |
- |
Schema tablespaces |
| |
- |
Tablespace size |
| |
- |
Tablespace current freespace |
Listing 1 is an autonomous procedure that performs the queries and updates the tables. The procedure is run several times during the day. It purpose is to develop the values for the prediction. The procedure has the following components:
| |
- |
A cursor (a) that computes current freespace for schema tablespaces. |
| |
- |
A cursor (b) that tests whether the tablespace exists in the Tablespace_growth_stats table. |
| |
- |
A cursor (c) that tests whether a reading exists for the tablespace for the current day in the Tablespace_readings table. |
| |
- |
New tablespaces are added to the Tablespace_growth_stats table |
| |
- |
Tablespace readings are evaluated and updated. The smallest tablespace reading is kept for each day. |
| |
- |
The procedure computes the current tablespace size and stores it in the Tablespace_growth_stats table. |
| |
- |
The procedure evaluates readings and determines the largest daily decrease in freespace. |
| |
- |
The procedure evaluates readings and determines the largest weekly decrease in freespace. |
| |
- |
The procedure evaluates readings and determines the largest monthly decrease in freespace. |
Listing 1 - Autonomous block procedure that determines schema tablespaces, tablespace size, and freespace.
/* This script was created by John Palinski October, 2004. Its purpose is to track the
amount of tablespace freespace. The script reads the Dba_free_space daily. It records
the reading and determines the maximum change. The combination of the current freespace
and the maximum amount of change is used to predict when it will be used up.*/
spool /home/tablespace_growth.log
begin
declare
-- This cursor determines all schema tablespaces and and the current amount of freespace
cursor a is select tablespace_name name, sum(bytes)/1000000 size_mb
from dba_free_space
group by tablespace_name;
a_var a%rowtype;
-- This cursor determines whether the tablespace identified by cursor a exists in the
-- Tablespace_growth_stats table
cursor b is select * from tablespace_growth_stats
where tablespace_name = a_var.name;
b_var b%rowtype;
-- This cursor determines whether a tablespace reading record exists in the Tablespace_readings
-- table for the tablespace for the current date.
cursor c is select * from tablespace_readings
where to_char(measurement_date, 'DD-MON-YYYY') = to_char(sysdate, 'DD-MON-YYYY')
and tablespace_name = a_var.name
for update;
c_var c%rowtype;
begin
open a; -- Cursor produces a result set of tablespace freespace
fetch a into a_var;
while a%found loop -- Begins the looping through the tablespace freespace result set
open b; -- Determines whether the tablespace exists in the Tablespace_growth_stats table
fetch b into b_var;
if b%notfound then -- Inserts a record into the Tablespace_growth_stats table
insert into tablespace_growth_stats -- if it does not exist
(tablespace_name, original_freespace_mb, original_measurement_date,
last_measurement_mb, last_measurement_date)
values
(a_var.name, a_var.size_mb, sysdate,
a_var.size_mb, sysdate);
end if;
close b;
open c; -- Determines whether the tablespace reading exists
fetch c into c_var; -- in the Tablespace_readings table for the current date
if c%found then -- If the record exists, the new value is evaluated against
if a_var.size_mb < c_var.measurement_mb then -- the previously record value. If the freespace is smaller
update tablespace_readings -- than the previous reading, it replaces the original value
set measurement_mb = a_var.size_mb,
measurement_mb_orig = a_var.size_mb
where current of c;
update tablespace_growth_stats -- Updates the Tablespace_growth_stats table with the
set last_measurement_mb = a_var.size_mb -- most recent freespace reading
where tablespace_name = a_var.name;
end if;
else
insert into tablespace_readings -- Tablespace reading does not exist for the current day
(tablespace_name, measurement_date, -- Reading record is inserted into the Tablespace_readings
measurement_mb, measurement_mb_orig) -- table
values
(a_var.name, sysdate, a_var.size_mb, a_var.size_mb);
update tablespace_growth_stats
set last_measurement_mb = a_var.size_mb,
last_measurement_date = sysdate
where tablespace_name = a_var.name;
end if;
close c;
fetch a into a_var;
end loop;
close a;
commit;
end;
-- Calculates the largest_daily decrease in free tablespace and places
-- the value into the Tablespace_growth_stats table. The values are not used by
-- the application. They are only made available for reference or future use
declare
cursor a is
select *
from (select tablespace_name, measurement_date, measurement_mb,
lead(measurement_mb, 1)
over (partition by tablespace_name
order by tablespace_name, measurement_date desc) - measurement_mb decrease,
lead(measurement_mb, 7)
over (partition by tablespace_name
order by tablespace_name, measurement_date desc) - measurement_mb weekly_decrease,
lead(measurement_mb, 30)
over (partition by tablespace_name
order by tablespace_name, measurement_date desc) - measurement_mb monthly_decrease,
rank() over (partition by tablespace_name
order by tablespace_name, measurement_date desc) as row_rank
from tablespace_readings)
where row_rank = 1;
a_var a%rowtype;
cursor b is
select * from tablespace_growth_stats
where tablespace_name = a_var.tablespace_name
for update;
b_var b%rowtype;
begin
open a;
fetch a into a_var;
while a%found loop
open b;
fetch b into b_var;
if (a_var.decrease >= 0
and (b_var.largest_daily_decrease is null
or
b_var.largest_daily_decrease < a_var.decrease)) then
update tablespace_growth_stats
set largest_daily_decrease = a_var.decrease,
largest_daily_decrease_date = a_var.measurement_date
where current of b;
end if;
if (a_var.weekly_decrease >= 0
and (b_var.largest_weekly_decrease is null
or
b_var.largest_weekly_decrease < a_var.weekly_decrease)) then
update tablespace_growth_stats
set largest_weekly_decrease = a_var.weekly_decrease,
largest_weekly_decrease_date = a_var.measurement_date
where current of b;
end if;
if (a_var.monthly_decrease >= 0
and (b_var.largest_monthly_decrease is null
or
b_var.largest_monthly_decrease < a_var.monthly_decrease)) then
update tablespace_growth_stats
set largest_monthly_decrease = a_var.monthly_decrease,
largest_monthly_decrease_date = a_var.measurement_date
where current of b;
end if;
close b;
fetch a into a_var;
end loop;
close a;
commit;
end;
-- This is run once a day. The procedure determines the size of the tablespace
-- by summing each tablespace's data files
begin
for a in (select tablespace_name, sum(bytes)/1000000 size_mb -- Determines tablespace size
from dba_data_files
group by tablespace_name)
loop
update tablespace_growth_stats -- Update the Tablespace_growth_stats table
set tablespace_size = a.size_mb -- with the current tablespace size.
where tablespace_name = a.tablespace_name;
end loop;
commit;
end;
-- The following section levels values. If a reading deviates down by 50% of the tablespace,
-- the value is replaced with is replaced by the previous days reading. This corrects for
-- abnormal variations caused by dropping tablespace objects.
begin
for a in (select *
from (select tablespace_name, measurement_date,
tablespace_size, measurement_mb,
lag(measurement_mb, 1)
over (partition by tablespace_name order by measurement_date) old,
measurement_mb - lag(measurement_mb, 1)
over (partition by tablespace_name order by measurement_date) change
from tablespace_readings
join tablespace_growth_stats using (tablespace_name))
where change > (.50 * tablespace_size)) -- Setting determines invalid movement range
loop
update tablespace_readings
set measurement_mb = a.old
where tablespace_name = a.tablespace_name
and measurement_date = a.measurement_date;
end loop;
commit;
end;
end;
/
spool off
exit
You might note the last code block in Listing 1. This code block evaluates the Tablespace_readings freespace values. The code block contains a parameter that affects the calculations. Some schema tablespaces are developmental or highly changing as a result of uploads and deletions. If the statistic grathering application is executed at a time when the tablespace is outside its normal range, a faulty trend line is computed. The referenced code block eliminates any reading that is a 50% change in total tablespace size in a single day. The application assumes that it is not normal to have a 50% reduction in a single day. If you feel this is a faulty assumption you may modify the setting or remove the code block entirely.
The Listing 1 procedure can be defined as an autonomous procedure or as a stored procedure. In this article, it is presented as an autonomous procedure. It is stored in a file called Tablespace_growth.sql.
The next installation task is to set up a scheduler to execute the file periodically during the day. The execution command is dependent upon the operating system. Listing 2 illustrates a typical Unix command file that executes the Tablespace_growth.sql file and sends an e-mail to Outlook notifying the target DBA that the procedure has run.
The Tablespace_growth.cmd file performs the following functions:
| |
- |
Identifies the location of logs (LOGDIR=/home/) |
| |
- |
Identifies the location of the Tablespace_growth.sql file (FILEPATH=/home/) |
| |
- |
Removes previously created log files (rm -f ...) |
| |
- |
Launches SQL*Plus and executes the Tablespace_growth.sql anonymous procedure (sqlplus ...) |
| |
- |
Sends an e-mail if procedures created a log file. (mailx -s ...) |
Listing 2 - Tablespace_growth.cmd Unix command file the launches the statistic collecting application
and sends an e-mail to the DBA.
#!/bin/ksh
#########################################################################################
#
# Name: tablespace_growth.cmd
# Purpose: computes tablespace freespace periodically
#
# Usage: ./tablespace_growth.cmd
#
#########################################################################################
DATE=`date`
LOGDIR=/home/
FILEPATH=/home/
. oraenv instance_name
rm -f ${LOGDIR}tablespace_growth.log
sqlplus oracle_id/password@connect_string @${FILEPATH}tablespace_growth.sql
mailx -s "Tablespace stats collected." realistic@radiks.net < ${LOGDIR}tablespace_growth.log
exit
The last step is to automate the process. If you have a UNIX operating system this can be done using a Crontab job. The job can be scheduled in Crontab by entering the following command:
Crontab -e
This command opens an editor allowing you to enter the jobs. The following are two Crontab statements. The first executes the Table_growth.cmd file at 5:00 PM and the second executes the file at 7:00 AM. The application will only be executed on Monday-Friday of each week. Modify the times to suit. Modify the /home/ path to match the file location.
00 17 * * 1-5 /home/tablespace_growth.cmd > /dev/null
00 07 * * 1-5 /home/tablespace_growth.cmd > /dev/null
At this point you are ready to begin populating the readings 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.
