Previous | Table of Contents | Next

Page 512

Once this command is run, the DBA_TABLES view reflects the statistics gathered for the PHONE_LOG table. In DBA_TABLES, there are BLOCKS and EMPTY_BLOCKS columns that indicate the number of blocks used and never used, respectively. Be aware that these columns assume that once a block is used, it will always be used. If a table contains one million rows and then 700,000 are deleted, the BLOCKS and EMPTY_BLOCKS values will not change.

The following SQL statement displays the space used by the PHONE_LOG table as a percent of the space allocated:

SELECT BLOCKS/(BLOCKS+EMPTY_BLOCKS)*100 USED_PCT FROM DBA_TABLES WHERE
     TABLE_NAME='PHONE_LOG';
USED_PCT
----------
 15.397996

Here we can see that although the PHONE_LOG table has 2MB allocated to it, only 15 percent is actually used to hold the table. Unless we expect this table to grow substantially in size over the next year or so, we may want to consider dropping and reloading this table with only 700KB or so allocated to it.

TIP
Although it is necessary to run an individual ANALYZE TABLE command for each table, the job of generating a script to compute statistics for the entire database can be made much easier by using the DBMS_UTILITY.ANALYZE_SCHEMA packaged procedure.

Optimizing Cluster Storage

Cluster check-ups are mostly limited to identifying excessive extents. Optimizing storage for clusters is even more important than tables because their efficiency (or lack thereof) will be seen in more than just one table. You can compute the number of extents for each cluster by using this SQL statement:

     COLUMN SEGMENT_NAME FORMAT A20
     SELECT OWNER, SEGMENT_NAME,COUNT(*) FROM DBA_EXTENTS WHERE
ÂSEGMENT_TYPE='CLUSTER' GROUP BY SEGMENT_NAME,OWNER;

The COUNT(*) column shows the number of extents for each cluster. Ideally, you will want just one extent per cluster. More than a dozen or so may indicate an INITIAL and/or NEXT storage parameter value that is set too low.

Unfortunately, Oracle does not provide us an easy way to determine what percentage of the allocated blocks are being used in a cluster. Although answers to questions like this lurk in the X$ tables, you are advised against relying on these tables unless specifically directed by Oracle.

Your best option is to recalculate the optimum storage parameters as if you intend to recreate a cluster and compare your newly computed optimum values with the cluster's current settings. If these values differ substantially, your best option is probably to re-create the cluster with correct storage parameters. To do so, follow these steps:

Page 513

  1. Perform a logical backup.
  2. Drop the cluster and associated tables.
  3. Recreate the dropped objects with correct storage parameters.
  4. Reload the rows into the dropped tables from the logical backup taken in step 1.

Once this procedure has finished, you are ready to allow users back onto the database system.

Checking Indexes

Like clusters, most DBAs find that a periodic health check simply verifies that the number of extents is within reason. Just as with clusters, we'll use a simple SQL statement for a check-up:

     COLUMN SEGMENT_NAME FORMAT A20
     SELECT OWNER, SEGMENT_NAME,COUNT(*) FROM DBA_EXTENTS WHERE  
ÂSEGMENT_TYPE='INDEX' GROUP BY SEGMENT_NAME,OWNER;

Indexes can be highly sensitive to excessive extent quantity. Try to keep the number of extents down to half a dozen or so for most indexes. Ideally, of course, just one extent will be used.

Watching the Growth of Rollback Segments

Working with rollback segments is discussed in depth in the previous section, "Managing Rollback Segments." From a storage management perspective, however, we are interested in knowing how large our rollback segments are growing. Recall that by specifying an OPTIMAL storage parameter size, a rollback segment may automatically shrink in size after a transaction completes. Oracle does, however, keep a high water mark that indicates how large each rollback segment has grown, how many times it has extended, and how many times it has shrunk, since the instance was started. To see the high water mark (in bytes) for each rollback segment, use this SQL statement:

     SELECT N.NAME, OPTSIZE, HWMSIZE FROM V$ROLLNAME N, V$ROLLSTAT S WHERE  
ÂN.USN=S.USN;

For each rollback segment, we are able to see the optimal size as well as the maximum size (so far) in the OPTSIZE and HWMSIZE columns, respectively. If OPTSIZE is blank, HWMSIZE is the current size of the rollback segments (and no shrinking will occur).

Frequently, we will create a set of rollback segments for production hours and a set of rollback segments for batch jobs (and perhaps a few extra for special job requirements). We will always need enough space in the appropriate tablespaces to contain all of the rollback segments at the optimal size. In addition, enough space must be free in the tablespace(s) to accommodate the HWMSIZE for each set of rollback segments, which could be active at any given time.

For instance, suppose we have RP01, RP02, and RP03 rollback segments used during production hours and RB01, RB02 are used for batch processing during evening hours. The result of the above SQL query is:

Page 514

NAME                              OPTSIZE    HWMSIZE
------------------------------ ---------- ----------
RP01                             10240000   20480000
RP02                             10240000   40960000
RP03                             10240000   10240000
RB01                      102400000  102400000
RB02                       51200000   61440000

The total optimum size for all rollback segments is roughly 180MB (meaning, this is the absolute smallest size the rollback segments will ever be). We assume that batch and production jobs are never run together, so at any given time, either the production or batch rollback segments remain at their optimum size. Therefore, during production, we can conclude that the largest amount of space needed by the rollback segments was 220MB (totaling the HWMSIZE column for production rollback segments and the OPTSIZE column for batch rollback segments). During batch hours, the rollback segments' largest space needed was 190MB (since the instance was last started).

Do not take these calculations literally. Always provide plenty of cushion space beyond what we have calculated to be the maximum needed so far. Remember that these values change once the instance has been restarted.

Managing Temporary Tablespace

Keeping tabs on the temporary tablespaces (where sorts occur) can be a rather tricky proposition. Temporary tablespaces are used exclusively for Oracle's internal needs. Segments are created and destroyed on a fairly routine basis in most installations automatically. Storage management issues with temporary tablespaces are usually limited to knowing the size of the tablespace(s), and the maximum amount of temporary storage used at any one time. The size of the temporary tablespace should be relatively constant and not subject to great fluctuations—although large queries involving sorting, index creation of large tables, and other major transactions can allocate an abnormal amount of temporary segments.

To monitor temporary segments, check for all segments that are of type TEMPORARY in DBA_SEGMENTS. The following query can help:

SELECT owner, segment_name, tablespace_name, bytes, extents
FROM dba_segments
WHERE segment_type = `TEMPORARY'

Administering a Growing Database

Careful monitoring of your database will usually enable you to spend some time carefully planning for future growth. Most databases require a balance of performance, fault tolerance, and ease of maintenance. We need to provide the path for database growth, while simultaneously maintaining acceptable performance levels and ensuring the uptime and integrity of the database and its data.

Previous | Table of Contents | Next