3-72 IBM Informix OnLine Database Server Administrator’s Guide
Monitor Chunks
Executetbcheck -pr to obtain the chunk information that is stored in the root
dbspacereserved page. Refer to page 2-100 for a detailed description of each
field in the chunk reserved page,PCHUNK.
Executetbcheck -pe to obtain the physical layout of information in the
chunk.The chunk layout is sequential, and the number of pages dedicated to
each table is shown. The following information displays:
■Dbspace name, owner, and number
■Number of chunks in the dbspace
This output is useful for determining the extent of chunk fragmentation. If
OnLineis unable to allocate an extent in a chunk despite an adequate number
of free pages, the chunk might be badly fragmented.
Refer topage 7-43 for further information about the tbcheck -pe output.
Depending on the specific circumstances, you might be able to eliminate
fragmentationby using the ALTER TABLE statement to rebuild the tables. For
this tactic to work, the chunk must contain adequate contiguous space in
which to rebuild each table. In addition, the contiguous space in the chunk
must be the space that OnLine normally allocates to rebuild the table. (That
is, OnLine allocates space for the ALTER TABLE processing from the
beginningof the chunk, looking for blocks of free space that are greater than
orequal to the size specified for the NEXT EXTENT. If the contiguous space is
located near the end of the chunk, OnLine could rebuild the table using
blocks of space that are scattered throughout the chunk.)
Use theALTER TABLE statement on every table in the chunk. Follow these
steps:
1. For each table, drop all the indexes except one.
2. Cluster the remaining index using theALTER TABLE statement.
3. Re-create all the other indexes.
You eliminate the fragmentation in the second step, when you rebuild the
table by rearranging the rows. In the third step, you compact the indexes as
wellbecause the index values are sorted before they are added to the B+ tree.
Youdo not need to drop any of the indexes before you cluster one but, if you
do, theALTER TABLE processing is faster and you gain the benefit of more
compact indexes.