Previous | Table of Contents | Next

Page 521

From a performance standpoint, keeping Oracle segments' fragmentation low does absolutely no good if the datafiles have been scattered all over a disk drive by the operating system. Methods of defragmenting a filesystem vary widely from operating system to operating system. Windows NT, unlike many UNIX implementations, enables filesystems to be defragmented with the database and system up and running. Several third-party products are on the market to perform this function. On-the-fly defragmenting, as this is called, should be scheduled to run during times with the least amount of database activity. As always, be prepared for disaster by having a recent backup ready.

Understanding Space Manager

Previous sections of this chapter are oriented towards the sizing of the database when the database was designed. They are guiding tips for the DBA so that he or she does not run into trouble due to bad planning at the time of the creation of the database itself. This section covers the steps in terms of space management for the day-to-day running of the database.

The product SPACE MANAGER is described here. The product will have to be installed onto your database and will basically function as a monitoring tool. It captures snapshots of the database daily. These statistics are stored in its repository and will be used subsequently to predict database growth and give early warnings to the DBA in terms of possible actions to be taken in order to avoid unscheduled downtime.

The data stored in the repository enables the DBA to perform an analysis of the database and see how the space has grown in various objects during different periods. Accuracy of prediction and analysis depends on the number of snapshots taken on the database.

Knowing the Features of Space Manager

Space Manager is a UNIX-based space monitoring tool. The basic script is written in the Korn Shell or ksh. The following are some of the main features of Space Manager:

Page 522

The following are some of the main limitations of Space Manager:

Using the Output of Space Manager

Before going into the details of installing and configuring the product for your system, let's look at some sample outputs of the product to appreciate the true power of Space Manager.

The first output shown in the following figure is the free space summary of the tablespaces in the database. The output shows the datafile details of each tablespace, the amount of space allocated to each datafile, the amount of space used in each datafile, and the percentage of space used in each datafile. It also reports the average space usage of the tablespace. This report can be used to get an overall picture of all tablespaces in the database. See Figure 21.13 for more details.

The second output shows the tablespace free space fall trends in the database. Given two space statistics of the database, it reports the fall in the free space of the tablespace in kilobytes in the interval, the free space fall per day in kilobytes, the amount of space allocated to the tablespace at the time the end of the snapshot was taken, and also the size of the greatest extent in the tablespace. It also reports the amount of free space present in the tablespace at the time the end snapshot was taken. For example, Snapshot Number 14 was taken on 18th August and Snapshot Number 24 was taken on 14th of September. The report shows the allocated space at Snapshot Number 24, it shows the size of the greatest extent, and the total free space available at Snapshot 24. It also shows the free space that was used by the tablespace during the two

Previous | Table of Contents | Next