DBA Tips Archive for Oracle


Managing Extents

by Jeff Hunter, Sr. Database Administrator


  1. Overview
  2. Choosing an Extent Size
  3. What About the Number of Extents?
  4. Extent Management with Dictionary Managed Tablespaces
  5. Extent Management with Locally Managed Tablespaces


Discussing the issue of managing extents has been one of the more difficult subjects to approach as a DBA. Accompanied by strong opinion and misleading articles, this topic is often left as a stalemate. Questions surrounding this issue include, how many extents should be considered too many? Should all extents in a tablespace be the same size? What is a good size for an extent? This article is a modest attempt at unraveling some of the truth when it comes to managing extents within your database.

Choosing an Extent Size

When the question comes up about the size of an extent, it should be know that the size of an extent can matter in performance, but not greatly. What the DBA should be concerned with though, is that all extents should be a multiple of the multiblock read size. (multiply the values for db_file_multiblock_read_count and db_block_size in the init.ora file to determine this size). If set incorrectly, when a full table or index scan is performed, an extra multiread block read will be required to read only the last remaining blocks of each extent. This happens because multiblock reads in Oracle never span extent boundaries, even if the extents happen to be contiguous. This behavior is also consistent within locally managed tablespaces.

What About the Number of Extents?

Just as popular as extent sizes are within the DBA community, so is that of how many extents should exist within a segment. Many DBAs are averse to allowing more than a few extents in any segment with the belief that such "fragmentation" degrades performance. This although is a myth that was first seen within the Oracle documentation for the export utility in version 3. The truth is, that having multiple extents within a segment, within reason of course, will not impact performance, especially when the extents are sized correctly. Claims have even been made that extents can be allowed to grow, even well into the thousands without a considerable impact on performance. It is my belief though that this is a bit too generous and care should be given to segments that have more than several thousand extents, as we will see shortly.

Extent Management with Dictionary Managed Tablespaces

By declaring a tablespace as DICTIONARY managed, you are specifying that extent management for segments in this tablespace will be managed using the dictionary tables sys.fet$ and sys.uet$. Oracle updates these tables in the data dictionary whenever an extent is allocated, or freed for reuse. This is the default in Oracle8i when no extent management clause is used in the CREATE TABLESPACE statement.

The sys.fet$ table is clustered in the C_TS# cluster. Because it is created without a SIZE clause, one block will be reserved in the cluster for each tablespace. Although, if a tablespace has more free extents than can be contained in a single cluster block, then cluster block chaining will occur which can significantly impact performance on the data dictionary and space management transactions in particular. Unfortunately, chaining in this cluster cannot be repaired without recreating the entire database. Preferably, the number of free extents in a tablespace should never be greater than can be recorded in the primary cluster block for that tablespace, which is about 500 free extents for a database with an 8K database block size.

Used extents, on the other hand, are recorded in the data dictionary table sys.uet$, which is clustered in the C_FILE#_BLOCK# cluster. Unlike the C_TS# cluster, C_FILE#_BLOCK# is sized on the assumption that segments will have an average of just 4 or 5 extents each. Unless your data dictionary was specifically customized prior to database creation to allow for more used extents per segment, then creating segments with thousands of extents (like mentioned in the previous section) will cause excessive cluster block chaining in this cluster. The major dilemma with an excessive number of used and/or free extents is that they can misrepresent the operations of the dictionary cache LRU mechanism. Extents should therefore not be allowed to grow into the thousands, not because of the impact of full table scans, but rather the performance of the data dictionary and dictionary cache.

Extent Management with Locally Managed Tablespaces

A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle simply changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information). This is the default in Oracle9i. If COMPATIBLE is set to 9.0.0, then the default extent management for any new tablespace is locally managed in Oracle9i. If COMPATIBLE is less than 9.0.0, then the default extent management for any new tablespace is dictionary managed in Oracle9i.

While free space is represented in a bitmap within the tablespace, used extents are only recorded in the extent map in the segment header block of each segment, and if necessary, in additional extent map blocks within the segment.

Keep in mind though, that this information is not cached in the dictionary cache. It must be obtained from the database block every time that it is required, and if those blocks are not in the buffer cache, that involves I/O and potentially lots of it. Take for example a query against DBA_EXTENTS. This query would be required to read every segment header and every additional extent map block in the entire database. It is for this reason that it is recommended that the number of extents per segment in locally managed tablespaces be limited to the number of rows that can be contained in the extent map with the segment header block. This would be approximately - (db_block_size / 16) - 7. For a database with a db block size of 8K, the above formula would be 505 extents.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 05-Oct-2002 00:00:00 EDT
Page Count: 27675