Previous | Table of Contents | Next

Page 515

Database growth usually focuses on segment and tablespace growth. Growing tablespaces is a matter of adding one or more datafile(s) to each tablespace in need of more storage, or resizing datafiles already allocated to the tablespace. Although segments automatically grow, there comes a practical and system limitation, which dictates that we should consolidate all of the segment's data into a single extent. The need for consolidation will depend largely on the data contained in the segment. Lookup tables rarely grow and rarely need attention. Tables containing data, in contrast, may require regular consolidation because they grow on a regular basis.

In this section, we look at the issues surrounding the maintenance of a growing database environment and suggest possible courses of action to ensure the database services you provide can handle the demands placed on them.

Monitoring Database Storage

Remember when your database was first created? You had everything set up just right; space was optimally allocated, no hint of data fragmentation was evident, and your queries ran almost instantly. A few years (months?) later, tablespaces consist of datafiles placed wherever you had space, free disk storage is tight, and performance is barely acceptable; you have to clean up the mess.

If left unchecked, databases can easily grow out of control. In this section, we'll discuss techniques you can use to get a handle on your database and monitor its growth. With this information, you'll have the ability to plan disk allocation and database growth—before your users call to let you know that they're "unable to allocate 64 bytes in tablespace PAYROLL_DATA_01." We'll also look at some useful tuning information that will help you keep your database healthy and responsive.

All of the information contained in the tracking system is stored in the SPACEHIST and PROCDATES tables. SPACEHIST contains the size history of segments in your database while PROCDATES stores every day for which statistics exist. Because SPACEHIST only records changes, you'll need to use a view to make sense of the information. The view V_SPACEHIST provides a look at the data contained in your tracking tables, trended across the time duration you run GETSTATS.SQL on.

The V_SPACEHIST view contains a row for each segment on each date in PROCDATES. V_SPACEHIST is defined as follows:

SAMPLE_DATE           DATE
OWNER                VARCHAR2(20)
SEGMENT_TYPE          VARCHAR2(20)
SEGMENT_NAME           VARCHAR2(20)
TABLESPACE           VARCHAR2(20)
BYTES               INTEGER
EXTENTS               INTEGER
NEXT_EXTENT          INTEGER

Page 516

The following describe the columns for V_SPACEHIST:

If you were to query V_SPACEHIST without a where clause, rows would be returned for every segment you have tracked statistics on, for every day these statistics were collected. Looking at all of the information contained in this view is probably not too useful. Fortunately, we can use SQL's powerful querying capabilities to gather the information we need.

"A picture is worth a thousand words" accurately describes how a graphical representation of data is often much more meaningful than the rows it represents. For instance, if we wanted to know when in the last six months our database grew from 2.7GB to 6GB, we could examine a couple hundred lines from a SQL query or look at a single graph showing the same information, and know instantly when peak growth occurred.

An ODBC connection and spreadsheet software are all the tools we need to build concise yet informative graphs depicting storage usage in our database. As you can see in Figure 21.12, the Microsoft Excel spreadsheet chart shows a period of time when the database grew as a whole. With this information, you might want to drill down to segment-level data, using the V_SPACEHIST view. By graphing the data first, we can instantly identify which period of time we need to focus our investigation on.

Many times, a sudden change in database growth is due to an increase in the number of users or an application change, which results in more data being stored. Once a database is established, DBAs are many times out of the information loop, and your database size might be growing rapidly without you knowing it. By collecting storage statistics regularly, you'll know when irregular growth has occurred and will have the opportunity to take preventative measures before a serious problem develops.

Page 517

FIG.21.12
Graphing the informa-
tion from the
V_SPACEHIST view can
quickly identify times of
peak growth.

Suppose, for example, you run GETSTATS.SQL every night and once a week you would like to see a chart showing growth for your production database over the last six months. You would start with an SQL statement similar to the following:

SELECT sample_date, SUM(bytes) FROM v_spacehist WHERE sample_date > (SYSDATE)-
180
GROUP BY sample_date;

Because the V_SPACEHIST view contains OWNER and TABLESPACE information for each segment it tracks, you can narrow your focus to track growth for specific users or tablespaces. This example provides 90 days of growth information for all segments owned by the user TGASPER:

SELECT sample_date, SUM(bytes) FROM v_spacehist
WHERE owner = `TGASPER' AND  sample_date > (SYSDATE _ 180)
GROUP BY sample_date;

Monitoring growth for a specific tablespace is just as easily accomplished, as shown in this example:

SELECT sample_date, SUM(bytes) FROM v_spacehist
WHERE  tablespace = `USERS' AND sample_date > (SYSDATE _ 180)
GROUP BY sample_date;

Unless you are in a unique environment, your database will grow over time. When we initially design systems, we plan for anticipated future growth. Regular monitoring of your database helps make sure that unanticipated growth doesn't catch you off guard.

Previous | Table of Contents | Next