Previous | Table of Contents | Next

Page 509

     SELECT TABLESPACE_NAME, SUM(BYTES) FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

TABLESPACE SUM(BYTES)
---------- ----------
RBS          29343744
SYSTEM       11552768
TEMP         10483712
USERS         9439232

Knowing the largest free extent in each tablespace helps us make sure that any of the segments can grow by at least one extent. Even though tablespace USERS may have 9MB of free space, it may be split in two pieces, each only 4MB. This is important to know because there may be a table with a NEXT extent value of 5MB. If this is so, Oracle would fail to allocate the additional extent, because there are no free extents of at least 5MB available. Again, we turn to the DBA_FREE_SPACE table, except now we're looking for the largest free extent, as shown in this example:

     SELECT TABLESPACE_NAME, MAX(BYTES) FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME;

TABLESPACE MAX(BYTES)
---------- ----------
RBS          20969472
SYSTEM        4096000
TEMP         10483712
USERS         9439232

By looking at the output from the last two SQL statements, we can see that the USERS tablespace does indeed have all 9MB in one piece. Notice though that the SYSTEM tablespace's largest free extent is only 4MB, yet it has 11MB free in total.

Now we need to look at all of the segments contained in the tablespaces. Recall that the NEXT storage parameter always indicates the size of the next extent a segment will ask for. At the very least, we should make sure that any one segment can grow by at least one extent. This at least minimizes the chance that we are in immanent danger of running out of space. We'll want to look at the DBA_SEGMENTS table because it considers all segments in a tablespace no matter what type they are. This example lists any segments (along with additional information) that cannot grow at least one extent:

     COLUMN SEGMENT_NAME FORMAT A30
     SELECT SEGMENT_NAME,SEGMENT_TYPE,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,
NEXT_EXTENT,PCT_INCREASE FROM DBA_SEGMENTS S
WHERE NEXT_EXTENT > (SELECT MAX(BYTES) FROM DBA_FREE_SPACE F WHERE
               TABLESPACE_NAME = S.TABLESPACE_NAME);
SEGMENT_NAME                   SEGMENT_TYPE      OWNER
------------------------------ ---------------- ------------------------------
TABLESPACE INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- -------------- ----------- ------------
TEST1                          TABLE             TGASPER
USERS              512000    41943040           50

Page 510

In this example, we can see that a table TEST1 in the TGASPER schema would not be able to allocate another extent because the NEXT_EXTENT parameter is set to 40MB, and from our previous example, we know that the USERS tablespace's largest extent is 9MB. Because 40MB is also larger than the amount of free space in the tablespace, we would need to add more space to the tablespace to allow TEST1 to grow. Even if enough free space exists in aggregate, you would still probably add more space to allow TEST1 to grow by 40MB or change the NEXT_EXTENT parameter in TEST1 to fit within the largest free extent size.

CAUTION
Exercise caution when setting the PCTINCREASE parameter. In the above example, we see that the PCTINCREASE value is set to 50. This means that each successive extent will be 50 percent larger than the last. Your segments can, quite quickly, try to allocate obnoxiously large extents. Proper planning will usually allow you to keep PCTINCREASE set to a fairly low value. Many sites' policies demand that PCTINCREASE be set to 0 for all segments.
If your site does insist on PCTINCREASE being set to 0, you will almost always want to set the default PCTINCREASE value for the tablespaces to 1. In Oracle versions 7.3 and later, SMON will automatically coalesce adjacent free segments when this parameter is not 0.

Tablespace fragmentation can become a serious problem, particularly in development or test systems where different segments are frequently created and dropped. We previously discussed that this activity causes tablespace fragmentation by creating bubbles of space, caught in tablespaces that are too small to be used by most segments.

You should regularly evaluate the degree to which your tablespaces are fragmented. Excessive fragmentation prevents segments from growing because all the free space is broken up into small and unusable pieces. As a temporary solution, you can add more space to a tablespace. This opens up a large single extent, which at least allows segments to grow and keep your database operational. Regular defragmentation is recommended for optimum performance and disc space efficiency.

Although storage manager is not a replacement for all of the information gathering discussed here, it does offer a quick way to check allocated and total free space in a tablespace.

Once you have started storage manager, expand the Tablespaces tree entry by double-clicking it. You'll see all of your tablespaces shown in the right-hand window along with graphs indicating the size of each tablespace and the total amount of space free. Figure 21.11 shows an example of this information.

Remember that the free space shown is a total of all free extents in the tablespace. If storage manager indicates that tablespace USERS has roughly 197MB free, this does not necessarily mean that all 197MB are contiguous.

Page 511

FIG.21.11
Storage Manager
providing quick
tablespace storage
information.

Checking on Tables

Our check-up on a database table involves assessing the total allocated size as well as the space actually used. We'll also have an interest in the number of extents allocated by the table. One or two extents with a low space used to allocated ratio would imply that the INITIAL and NEXT extent values may be set too high. Dozens of extents may indicate that the NEXT extent parameter is set too low.

We can determine the total allocated space of a table by totaling the sizes of all related extents. Extent information can be found in the DBA_EXTENTS table. This example demonstrates how you can easily compute the allocated space of each table in a given tablespace (USERS for this example):

     SELECT TABLESPACE_NAME,SEGMENT_NAME,SUM(BYTES),COUNT(*) EXT_QUAN
FROM DBA_EXTENTS
          WHERE TABLESPACE_NAME='USERS' AND SEGMENT_TYPE='TABLE'
GROUP BY TABLESPACE_NAME,SEGMENT_NAME;
TABLESPACE SEGMENT_NAME                   SUM(BYTES)   EXT_QUAN
---------- ------------------------------ ---------- ----------
USERS      EMP                                102400          1
USERS      DEPT                                10240          1
USERS      INOUT_LOG                         4096000        100
USERS      PHONE_LOG                    2048000            1
USERS      HOURS                             1024000          2

We can see for each table (in the SEGMENT_NAME column) the total amount of allocated space (in bytes) as well as the quantity of extents (in the EXT_QUAN column) allocated for the table. Notice that the INOUT_LOG table has allocated 4MB in 100 extents. This implies that the NEXT storage parameter is set too low.

Our last evaluation of tables focuses on determining a space used-to-allocated ratio. Recall that Oracle allocates space according to the values of the INITIAL and NEXT storage parameters. Therefore, you cannot be certain that a table actually needs all the space allocated to it. Be aware that the ratio we calculate is only an estimate, but it is still very useful for computing the actual space used by a table.

Oracle provides a method for computing table statistics through the ANALYZE TABLE command. For each table in your database, you should regularly compute statistics. Use commands similar to this example that analyzes the PHONE_LOG table:

ANALYZE TABLE PHONE_LOG COMPUTE STATISTICS;

Previous | Table of Contents | Next