Monitor Tablespace Tips

The DBA needs to monitor more than just users. Tablespaces also require watching, because they are not unchanging objects. They are subject to becoming filled and/or fragmented. The Oracle Administrator toolbar provides for a GUI-based monitoring of tablespaces via the Storage Manager (see Figure 11.4). Unfortunately, it provides no report output. Luckily, it is a fairly easy thing to monitor tablespaces using the V$ and DB_ views. Look at Figure 11.5, which shows the DBA_ views that relate to tablespaces, as we examine a script or two that provide us with information we can put our hands on.

Figure 11.4 Oracle Enterprise Manager Storage Manager screen.

Figure 11.5 DBA_TABLESPACES view cluster.

Monitoring Tablespace Freespace and Fragmentation

Let's begin by examining a report that covers two critical parameters, available space and fragmentation. The OEM GUI includes a tablespace map feature that gives this information graphically and can be printed in a report format; this is shown in Figure 11.6. An example of the OEM tablespace analysis report is shown in Figure 11.7. For a manual report, see Source 11.14.

Figure 11.6 OEM tablespace map.

Figure 11.7 OEM tablespace analysis report.

SOURCE 11.14 A report on tablespace space usage and fragmentation.

rem
rem Name:     free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: this report uses the free_space2 view
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132
COLUMN tablespace      HEADING Name             FORMAT a30
COLUMN files           HEADING '#Files'         FORMAT 9,999
COLUMN pieces          HEADING 'Frag'           FORMAT 9,999
COLUMN free_bytes      HEADING 'Free|Byte'      FORMAT 9,999,999,999
COLUMN free_blocks     HEADING 'Free|Blk'       FORMAT 999,999
COLUMN largest_bytes   HEADING 'Biggest|Bytes'  FORMAT 9,999,999,999
COLUMN largest_blks    HEADING 'Biggest|Blks'   FORMAT 999,999
COLUMN ratio           HEADING 'Percent'        FORMAT 999.999
COLUMN average_fsfi    HEADING 'Average|FSFI'   FORMAT 999.999
START title132 "FREE SPACE REPORT"
DEFINE 1 = report_output/&&db/free_spc
SPOOL &1
SELECT
       tablespace,
       COUNT(*) files,
       SUM(pieces) pieces,
       SUM(free_bytes) free_bytes,
       SUM(free_blocks) free_blocks,
       SUM(largest_bytes) largest_bytes,
       SUM(largest_blks) largest_blks,
       SUM(largest_bytes)/sum(free_bytes)*100 ratio,
       SUM(fsfi)/COUNT(*) average_fsfi
FROM
SEE CODE DEPOT FOR FULL SCRIPT
       free_space
GROUP BY
       tablespace;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEED ON
SET FLUSH ON
SET VERIFY ON
SET PAGES 22 LINES 80
PAUSE Press Enter to continue

The report in Source 11.14 uses the view FREE_SPACE, which is based on the DBA_ view DBA_FREE_SPACE. This view is shown in Source 11.15. The freespace report is shown in Listing 11.13.

SOURCE 11.15 Freespace view listing.

rem
rem Name:     free_space_view.sql
rem
rem FUNCTION: Create free_space view for use by freespc reports
rem
CREATE VIEW free_space
    (tablespace, file_id, pieces, free_bytes, free_blocks,
     largest_bytes,largest_blks, fsfi) AS
SELECT tablespace_name, file_id, COUNT(*),
    SUM(bytes), SUM(blocks),
    MAX(bytes), MAX(blocks),
    SQRT(MAX(blocks)/SUM(blocks))*(100/SQRT(SQRT(COUNT(blocks))))
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id, relative_fno;

Date: 10/14/01                                                                      Page:   1
Time: 12:41 PM                     FREE SPACE REPORT                           DBAUTIL
                                   galinux1 database 

                                      Free     Free      Biggest  Biggest  Average
Name            #Files   Frag         Byte      Blk        Bytes     Blks  Percent    
FSFI
--------------- ------ ------ ------------ -------- ------------ -------- -------- ---CWMLITE              1      1   20,905,984    2,552   20,905,984    2,552  100.000  100.000
DBAUTIL_DATA         1      1    5,939,200      725    5,939,200      725  100.000 
100.000
DBAUTIL_INDEX        1      1    9,625,600    1,175    9,625,600    1,175  100.000 
100.000
DRSYS                1      1   12,845,056    1,568   12,845,056    1,568  100.000 
100.000
EXAMPLE              1      1      262,144       32      262,144       32  100.000 
100.000
INDX                 1      1   26,148,864    3,192   26,148,864    3,192  100.000 
100.000
SYSTEM               1      1      540,672       66      540,672       66  100.000 
100.000
TOOLS                1      3    8,192,000    1,000    7,274,496      888   88.800  
71.602
UNDOTBS              1      4  208,338,944   25,432  114,098,176   13,928   54.766  
52.329
UNDO_TBS2            1      1   19,595,264    2,392   19,595,264    2,392  100.000 
100.000
UNDO_TBS3            1      1   19,595,264    2,392   19,595,264    2,392  100.000 
100.000
USERS                1      1   25,952,256    3,168   25,952,256    3,168  100.000 
100.000

In an ideal situation, the tablespace data file(s) will show one extent (there will be one line in the report for each tablespace data file), and the biggest area will match the free area. In most cases, if the tablespace has been used for any length of time, there will be several extents, and the free area (which corresponds to total freespace in the tablespace) and the biggest area (which corresponds to the biggest area of contiguous free space) will not be equal.

If, after a coalesce, the number of extents isn't large, say, fewer than 20, and the mismatch between the two sizes is small, say, less than 10 percent difference between biggest and free, then there is probably nothing to worry about. If, on the other hand, either of these values is exceeded, the DBA should consider using the defragmentation methods described earlier. This report will not cover temporary tablespaces that are created as CREATE TEMPORARY TABLESPACE using tempfiles. 

Under Oracle8, Oracle8i, and Oracle9i, the tablespaces will be automatically defragmented by the SMON process if the value for the default storage parameter PCTINCREASE is set to greater than 0. The Free Space Fragmentation Index (FSFI) tells how much the freespace in a tablespace is fragmented--and comes to us by way of the Oracle DBA Handbook, by Kevin Loney (Oracle Press, 1994). A high value is good (with 100 the best); a low value is bad.

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 re-create the tablespace to the size required to prevent excessive addition of data files. Though Oracle suggests using autoextend data  files for tablespaces with expected growth, I still prefer manual control of tablespace growth so I am not surprised due to a runaway insert or other database mishap that causes the tablespace to grow unpredictably. However, turning on autoextend for a stable production environment may be the way to go if you understand the way the tablespaces are likely to grow. 

Spreading data files for large databases across several drives may be desirable for equalizing disk I/O. This is, however, a database-specific question, which has to be answered on a case-by-case basis.

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. For instance, 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 in Oracle7, create a table-specific tablespace on each of the platters that will hold the file, with each tablespace a fraction of the total size of the table; that is, 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 treat the table as one contiguous entity, but you will gain I/O speed by having spread the table across the available drives. Under Oracle8, Oracle8i, and Oracle9i, this can be accomplished with table partitioning, which allows a single table to be spread, by value range, across several files. Of course with RAID 1, RAID01/10, or RAID5, the spreading is done quasi-automatically so you would partition only for use of partition elimination or the benefits of parallel query and independent partition maintenance.

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 default to the default storage for minor tables. Ideally, this size estimation should be pushed down to the developers of the applications.

To avoid fragmentation issues altogether, you can use the fixed-size extent model for your tablespaces. The fixed-size extent model states that, for any tablespace, all objects in that tablespace will have initial extent sizes that are multiples of a fixed default value, and next extent sizes that are equal to the default extent value for the tablespace. In the fixed-extent-size model, you have several tablespaces-- usually a small extent, medium extent, and large extent--each sized according to the needs of your application. By using the fixed-size-extent model, any extents that are released due to table or index maintenance will be reusable by any other object in the table. By allowing freed-extent reuse, fragmentation is no longer a concern in fixed-extent-size model tablespaces.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".