Checking for extent interleaving

When two or more growing tables share a dbspace, extents from one tblspace can be placed between extents from another tblspace. When this situation occurs, the extents are said to be interleaved. Performance suffers when disk seeks for a table must span more than one extent, particularly for sequential scans.

Interleaving creates gaps between the extents of a table. Interleaved table extents shows gaps between table extents.

Figure 1: Interleaved table extents

This figure is described in the surrounding text.

Try to optimize the table-extent sizes to allocate contiguous disk space, which limits head movement. Also consider placing the tables in separate dbspaces.

Check periodically for extent interleaving by monitoring chunks. Execute oncheck -pe to obtain the physical layout of information in the chunk. The following information appears:
  • Dbspace name and owner
  • Number of chunks in the dbspace
  • Sequential layout of tables and free space in each chunk
  • Number of pages dedicated to each table extent or free space

This output is useful for determining the degree of extent interleaving. If the database server cannot allocate an extent in a chunk despite an adequate number of free pages, the chunk might be badly interleaved.