Previous | Table of Contents | Next

Page 493

policy table in this tablespace, having storage parameters of an initial extent of 15MB and a next extent of 10MB. This tablespace has 20MB of total free space, but the maximum size of any free extent is only 10MB; therefore, our creation of the policy table will fail.

FIG.21.2
Tablespace configura-
tion before fragmenta-
tion.

FIG.21.3
Fragmentation in the
tablespace after
dropping one table.

This example shows the problem occurring on a very small scale, with only one object in the tablespace being dropped. Consider a busy real-life development environment, however, where objects are dropped and different-sized objects are created almost continuously. This activity results in non-contiguous bubbles of free space forming all over the tablespace—similar to that depicted in Figure 21.4. The total free space of the tablespace seems quite adequate at first glance, but no large contiguous free space extents are in the tablespace, which severely limits your ability to make use of the free space.

Page 494

FIG.21.4
A completely frag-
mented tablespace.

The Oracle RDBMS does try to help the DBA slightly with space allocation and management. Consider the same scenario above with four tables in the tablespace. Now suppose we drop two of the tables, both of which reside in the "middle" of the tablespace. This gives us a tablespace structure as shown in Figure 21.5. If the same request for a 15MB table is processed, the SMON background process automatically coalesces the two 10MB extents into one 20MB extent, and uses it to fulfill the object creation. SMON also coalesces space automatically, if the PCTINCREASE for the tablespace is set to a non-zero value.

FIG.21.5
Fragmentation in
tablespace after
dropping two tables.

You can also manually coalesce the space in a tablespace with the following command:

alter tablespace armslivedb01_ts coalesce;

The following script creates a view you can use to identify problem segments in your tablespaces. Query this view by tablespace_name to get an idea where space problems might be occurring, as well as to see how fragmented individual objects are in the tablespace.

CREATE OR REPLACE VIEW ts_blocks_v AS
SELECT tablespace_name, block_id, bytes, blocks, `== free space ==' segment_name
FROM dba_free_space
UNION ALL

Page 495

SELECT tablespace_name, block_id, bytes, blocks, segment_name
FROM dba_extents;

Select * from ts_blocks_v;
TABLESPACE_NAME                BLOCK_ID   BYTES      BLOCKS     SEGMENT_NAME
------------------------------ ---------- ---------- ---------- ----------------
PROG_PLAN_IDX_08                    34562  221962240      27095 == free space ==
BMC_SMGT_TS                           339  102088704      12462 == free space ==
DBA_TEST                            42372  533700608      65149 == free space ==
BPW_DATA_01                        111667  133808128      16334 == free space ==
BPW_IDX_01                         155439  299515904      36562 == free space ==
AUDIT_01                            84231      40960          5 SQLAB_COLLECTION
AUDIT_01                                2    1064960        130 TB_225AUDIT
AUDIT_01                             2692     532480         65 TB_237AUDIT
BPW_DATA_01                        105737   41943040       5120 TB_G026CAPACITY
BPW_DATA_01                        105702     286720         35 TB_G016RESOURCE
BPW_DATA_01                        105667     286720         35 TB_G006NSU
BPW_DATA_01                        105602     532480         65
TB_7056SUBST_AUDIT
BPW_DATA_01                        105567     286720         35
TB_64S6MSG_TYPE_CODESET
BPW_DATA_01                        101982   29368320       3585
TB_64R6CAPACITY_CONSTRAINT
BPW_DATA_01                          1092     286720         35 TB_5317INV_RI

Tablespace fragmentation can lead to the following problems:

It was once thought that tablespace fragmentation was also a performance hit, but both disk and RDBMS technology has advanced sufficiently such that only the most badly fragmented tablespaces are likely to cause any measurable performance degradation. If you do suspect fragmentation as a possible culprit in performance problems, it is far more likely that chained or migrated rows are causing problems, rather than tablespace fragmentation.

Dealing with Fragmented Tablespaces

The best way to deal with tablespace fragmentation is to avoid it. This can be done but requires careful planning and administrative overhead. If you are faced with cleaning up fragmented tablespaces, the easiest way is to export the offending objects in the tablespace, drop the objects, and import them back. This not only coalesces your free space, but Export also coalesces your database objects into one extent.

To prevent fragmentation, group objects with similar space and growth characteristics together. Size their extents the same, so that all objects can share extents deallocated or regained from dropping objects. Keep your PCTINCREASE at 0, or if you must have SMON automatically coalesce, at 1. This eliminates extents of all different sizes being created, and then being unusable by future database segments.

Page 496

NOTE
Beginning in Oracle7.3, setting the default PCTINCREASE greater than 0 on a tablespace will cause SMON to automatically coalesce adjacent free extents. Unless your database system absolutely cannot spare the CPU time or disk I/O, you should set this parameter to at
least 1.

Finally, the following script shows you how much free space is available in each tablespace in your database, as well as the size of the largest free extent, and the number of free extents in each tablespace. This is a good place to start when looking for problems—if you see 500MB
of free space in a tablespace, but have a largest free extent of 15MB, you have an obvious
problem.

SELECT tablespace_name, sum(bytes) "Free Bytes", max(bytes) "Largest Extent",
       count(block_id) "# Extents"
FROM dba_free_space
GROUP BY tablespace_name;

Understanding Object Fragmentation

Fragmented tablespaces are a sure way to give any DBA space allocation headaches. The more insidious and easily missed fragmentation problems, however, are at the object level. In object-level fragmentation, the object may look fine and healthy at the tablespace level, having a single extent, but some probing inside those extents can reveal a different story.

A common occurrence is that a DBA sees space allocation problems in an often-modified tablespace, and rebuilds the tablespace and all of its objects. Subsequently, the database performance may increase. This increase in performance is attributed to the tablespace fragmentation being eliminated, but it could very well be the elimination of object-level fragmentation that gives us the performance boost.

One type of fragmentation which goes on generally unnoticed is the fragmentation in the object due to frequent delete activity. When a row is deleted from a block, free space is left in the block. This free space is not used again until the time the block comes back in the free list again. For this to happen, enough rows should be deleted so that the actual amount of space used in the block is lesser than the PCTUSED setting of the table. Once the used space of the block falls below the PCTUSED parameter, then the block is back on the free list and the block can then be used for future inserts and thus optimizing space usage. This kind of fragmentation can leave the database with free space holes which could be detrimental to performance. Other commonly known types of fragmentations are discussed in the next section.

Object fragmentation can lead to the following problems:

Previous | Table of Contents | Next