Previous | Table of Contents | Next

Page 523

snapshots (Used Interval Spc) and the amount of free space used per day. To elaborate, tablespace ARMSLIVEIDX02_TS_TS has 1024000KB of space allocated, the size of the largest extent being 94210KB, the total amount of free space is 245534KB, the amount of free space used in the interval is 199680KB, and the space usage per day is 7453KB. This report is based on statistics captured from dba_free_space view (see Figure 21.14).

FIG.21.13
Tablespace usage
summary report.

FIG.21.14
Tablespace free space
trends.

The third output shows the space growth of individual objects in the database. Facility is provided to filter only the schema of interest. The output shows details of the number of extents currently used by the object in kilobytes and the number of extents the object has grown by during the two snapshots. It also shows the amount of space used by the object during the End Snapshot time and, most useful, it reports the space used by the object per day.

This information is valuable for administrators who are interested in knowing the growth profile of objects in the database. This output, in my opinion, is the most useful of the lot as it reports the space or kilobytes captured per day by the object, which is extremely useful in predicting growth of a particular object. The space per day can also be made accurate by increasing the snapshot interval so that some peaks in space capturing of the object are ironed out and a more average or approximately accurate statistic is obtained. See Figure 21.15 for more details.

Page 524

FIG.21.15
Object growth
monitoring.

The fourth output reports the predictions of space growth of the tablespaces. Given two snapshot intervals from which the prediction is to be derived, it computes the average growth rate of every object in the tablespace using statistics captured between the two snapshots. It estimates the current free space available in the tablespace using information from Oracle system tables and predicts the number of days by which the tablespace will become full. The interval space growth used value can be tallied with the Object Space Growth Report to find out which object in the tablespace has actually used the space in the interval.

Assumptions made in the prediction tool are that the space available in the tablespaces is contiguous and every block of free space in the tablespace will be filled subsequently by the objects in the tablespace. For a badly fragmented tablespace, this may not be the best tool to predict the space in the tablespace. I suggest that the tablespace be defragmented first, before relying on the predictions for such tablespaces. This utility is the other best and useful tool within Space Manager. See Figure 21.16 for more details.

FIG.21.16
Number of days in which
tablespace will become
full.

The fifth report is the space warnings report (see Figure 21.17). It reports on all objects in each tablespace whose next extent is larger than the maximum extent size in the tablespace. If such an object extents to capture another extent, it reports an unable to extent error on the object and possibly can cause the functioning of the database to halt. For example, in Figure 21.17, the table mailsort has a next extent size set to 5MB, and the size of the maximum extent in the tablespace ARMSLIVEDB06_TS in which it resides is only 3.02MB. Therefore, this object will surely face a problem when it tries to extent.

Page 525

FIG.21.17
Space warnings.

Configuring and Using Space Manager

The steps for installing and using Space Manager are explained here. Because Space Manager is completely modular, it can be very easily customized for providing other useful statistical reports from its repository, as required by the user.

Understanding the Modules in Space ManagerThis section gives a brief overview of the various scripts that Space Manager is comprised of, their names, and a short description of their individual functionality. The reader is encouraged to examine these scripts for possible customization needs. The Space Manager repository contains a wealth of space statistics; a creative reader should be able to exploit this to obtain even more information.

Previous | Table of Contents | Next