Oracle Tablespaces – Monitoring

Because tablespaces are not unchanging objects, monitoring tablespaces is an important aspect of the DBA's monitoring responsibilities.

Tablespaces are automatically defragmented by the SMON process.

If you find yourself adding several data files to a single tablespace in a relatively short period of time, it may be wise to extrapolate the growth and then export, drop and recreate the tablespace to the size required to prevent excessive addition of data files.

Spreading data files for large databases across several drives may also be desirable for equalizing disk I/O. This is a database specific question and needs to be answered on a database by database consideration. If you have several large tables that would benefit from being spread across several disks, you might consider placing them in their own tablespaces, then sizing the data files for the tablespaces such that the data contained in the tables is spread.

If you have a single table that contains a gigabyte of data, it may be advisable to spread this file across several platters. To do this, create a table specific tablespace on each of the platters to hold the file, each a fraction of the total size of the table. Therefore, if you want to spread the file across four drives, each data file would be 250 megabytes in size. Then, when you import the table, it will be spread across the four drives. The database will see the table as one contiguous entity, but you will gain I/O speed by having spread the table across the drives available.

You should create each tablespace with a default storage parameter that takes into account the performance critical tables in the application that resides in it. You should also do the best job you can estimating the size requirements for the tables as they are created and only use default storage for minor tables. Ideally, this size estimation should be pushed down to the developers of the applications.

Extents and the Temporary tablespace

The modification to the smon coalescence algorithm that results in tablespaces with a pctincrease of zero not being coalesced was done for the temporary tablespace and rollback tablespace. In both of these tablespaces the default storage parameters should be set up such that each extent is the same size so theoretically leaving the empty extents and not coalescing them should result in better performance since the Oracle engine will simply grab the first extent it sees that is the right size instead of having to create a suitably sized extent. This indicates that multiple extents are not a major concern in the temporary or rollback tablespaces as long as the guideline on standard size extents and pctincrease of zero is followed.

Information on the Auto-extend Option for the Tablespace Datafiles

A common DBA question is where to get information from the data dictionary about the auto-extend options for datafiles. Oracle Support's answer is that the only way to get this information is to query the SYS table FILEXT$. Unfortunately this table looks like this:

Name                              Null?      Type
-------------------------------   --------   ----
FILE#                             NOT NULL   NUMBER
MAXEXTEND                         NOT NULL   NUMBER
INC                               NOT NULL   NUMBER

This structure means that in order to get back to the actual file name and tablespace you need to join to several other tables: FILE$, TS$ and V$DBFILE. We suggest creating a view based on the select below:

CREATE VIEW dba_file_data
AS
  SELECT a.name tablespace,
         a.dflminext min_extents,
         a.dflmaxext max_extents,
         a.dflinit init,
         a.dflincr next,
         a.dflextpct pct_increase,
         d.name datafile,
         b.blocks datafile_size,
         c.maxextend max_extend,
         c.inc ext_incr
    FROM ts$ a, file$ b, filext$ c, v$dbfile d
   WHERE a.ts# = b.ts#
     AND b.file# = c.file#
     AND b.file# = d.file#
/

This script creates the view DBA_FILE_DATA which will look like this when queried:

Name                              Null?      Type
 -------------------------------   --------   ----
 TABLESPACE                        NOT NULL   VARCHAR2(30)
 MIN_EXTENTS                       NOT NULL   NUMBER
 MAX_EXTENTS                       NOT NULL   NUMBER
 INIT                              NOT NULL   NUMBER
 NEXT                              NOT NULL   NUMBER
 PCT_INCREASE                      NOT NULL   NUMBER
 DATAFILE                                     VARCHAR2(257)
 DATAFILE_SIZE                     NOT NULL   NUMBER
 MAX_EXTEND                        NOT NULL   NUMBER
 EXT_INCR                          NOT NULL   NUMBER

The above view is handy if you use the auto-extend options. The script should be run as SYS and a grant made to public and a synonym created for access of the view.