[Loney05] Chapter 6. Monitoring Space Usage

Chapter 6. Monitoring Space Usage

A good DBA has a toolset in place to monitor the database, both proactively monitoring various aspects of the database, such as transaction load, security enforcement, space management, and performance monitoring, and effectively reacting to any potentially disastrous system problems. Transaction management, performance tuning, memory management, and database security and auditing are covered in Chapters 7, 8, 9, and 10. In this chapter, we'll address how a DBA can effectively and efficiently manage the disk space used by database objects in the different types of tablespaces: the SYSTEM tablespace, the SYSAUX tablespace, temporary tablespaces, undo tablespaces, and tablespaces of different sizes.

To reduce the amount of time it takes to manages disk space, it is important for the DBA to understand how the applications will be using the database as well as to provide guidance during the design of the database application. Designing and implementing the database application, including tablespace layouts and expected growth of the database, have been covered in Chapters 3, 4, and 5.

In this chapter, we'll also provide some scripts that need not much more than SQL*Plus and the knowledge to interpret the results. These scripts are good for a quick look at the database's health at a given point in time—for example, to see if there is enough disk space to handle a big SQL*Loader job that evening or to diagnose some response-time issues for queries that normally run quickly.

Oracle provides a number of built-in packages to help the busy DBA manage space and diagnose problems. For example, Oracle 10g Segment Advisor helps to determine if a database object has space available for reuse based on how much fragmentation exists in the object. Other features of Oracle, such as Resumable Space Allocation, allow a long-running operation that runs out of disk space to be suspended until the DBA can intervene and allocate enough additional disk space to complete the operation. As a result, the long-running job will not have to be restarted from the beginning.

We'll also cover some of the key data dictionary and dynamic performance views that give us a close look at the structure of the database and a way to optimize space usage. Many of the scripts provided in this chapter use these views.

At the end of this chapter, we'll cover two different methods for automating some of the scripts and Oracle tools: using the Oracle 10g DBMS_SCHEDULER built-in package as well as using the Oracle Enterprise Manager (OEM) infrastructure.

Space usage for tablespaces will be the primary focus in this chapter, along with the objects contained within the tablespaces. Other database files, such as control files and redo log files, take up disk space, but as a percentage of the total space used by a database they are small. We will, however, briefly consider how archived log files are managed because the number of archived log files will increase indefinitely at a pace proportional to how much DML activity occurs in the database. Therefore, a good plan for managing archived log files will help keep disk space usage under control.

6.1. Common Space Management Problems

Space management problems generally fall into one of three categories: running out of space in a regular tablespace, not having enough undo space for long-running queries that need a consistent "before" image of the tables, and insufficient space for temporary segments. Although we may still have some fragmentation issues within a database object such as a table or index, locally managed tablespaces solve the problem of tablespace fragmentation.

We will address each of these three problem areas by using the techniques described in the following sections.

6.1.1. Running Out of Free Space in a Tablespace

If a tablespace is not defined with the AUTOEXTEND attribute, then the total amount of space in all the datafiles that comprise the tablespace limits the amount of data that can be stored in the tablespace. If the AUTOEXTEND attribute is defined, then one or more of the datafiles that comprise the tablespace will grow to accommodate the requests for new segments or the growth of existing segments. Even with the AUTOEXTEND attribute, the amount of space in the tablespace is ultimately limited by the amount of disk space on the physical disk drive or storage group.

The conclusion to be reached here is that we want to monitor the free and used space within a tablespace to detect trends in space usage over time, and as a result be proactive in making sure that enough space is available for future space requests.

6.1.2. Insufficient Space for Temporary Segments

A temporary segment stores intermediate results for database operations such as sorts, index builds, distinct queries, union queries, or any other operation that necessitates a sort/merge operation that cannot be performed in memory. Temporary segments should be allocated in a temporary tablespace, which we introduced in Chapter 1. Under no circumstances should the SYSTEM tablespace be used for temporary segments; when the database is created, a non-SYSTEM tablespace should be specified as a default temporary tablespace for users who are not otherwise assigned a temporary tablespace. If the SYSTEM tablespace is locally managed, a separate temporary tablespace must be defined when the database is created.

When there is not enough space available in the user's default temporary tablespace, and either the tablespace cannot be autoextended or AUTOEXTEND is disabled, the user's query or DML statement fails.

6.1.3. Too Much or Too Little Undo Space Allocated

As of Oracle9i, undo tablespaces have simplified the management of rollback information by managing undo information automatically within the tablespace. The DBA no longer has to define the number and size of the rollback segments for the kinds of activity occurring in the database. As of Oracle 10g, manual rollback management has been deprecated.

Not only does an undo segment allow a rollback of an uncommitted transaction, it provides for read consistency of long-running queries that begin before inserts, updates, and deletes occur on a table. The amount of undo space available for providing read consistency is under the control of the DBA and is specified as the number of seconds that Oracle will attempt to guarantee that "before" image data is available for long-running queries.

As with temporary tablespaces, we want to make sure we have enough space allocated in an undo tablespace for peak demands without allocating more than is needed. As with any tablespace, we can use the AUTOEXTEND option when creating the tablespace to allow for unexpected growth of the tablespace without reserving too much disk space up front.

Undo segment management is discussed in detail in Chapter 7, whereas the tools to help size the undo tablespaces are discussed later in this chapter.

6.1.4. Fragmented Tablespaces and Segments

As of Oracle8i, a tablespace that is locally managed uses bitmaps to keep track of free space, which, in addition to eliminating the contention on the data dictionary, eliminates wasted space because all extents are either the same size (with uniform extent allocation) or are multiples of the smallest size (with autoallocation). For migrating from a dictionary managed tablespace, we will review an example that converts a dictionary managed tablespace to a locally managed tablespace. In a default installation of Oracle 10g using the Database Creation Assistant (DBCA), all tablespaces, including the SYSTEM and SYSAUX tablespaces, are created as locally managed tablespaces.

Creating a tablespace that is locally managed is as easy as adding a clause to the create tablespace statement:

SQL> create tablespace USERS2
  2       datafile 'F:\ORACLE\ORADATA\OEMREP\USERS02.DBF'
  3       size 25M autoextend on next 25M maxsize 100M
  4       extent management local uniform size 4M
  5       segment space management auto;
Tablespace created.

The tablespace will be created with an initial size of 25MB, and it can grow as large as 100MB; extents will be locally managed with a bitmap, and every extent in this tablespace will be exactly 4MB in size. Space within each segment (table or index) will be managed automatically with a bitmap instead of freelists.

Table and index segments may contain a lot of free space due to update and delete statements. As a result, a lot of unused space can be reclaimed by using some of the scripts we provide later in this chapter, as well as by using the Oracle Segment Advisor.