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. |
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
Once this procedure has finished, you are ready to allow users back onto the database system.
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.
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.
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 fluctuationsalthough 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'
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.