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. |
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
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;