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.

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.