Document Type | Technical Information
Category | Administration
Applicable Product Versions | 6FS06, 7FS02
Document Number | TADTI034
Overview
This document explains the AUTO_COALESCE function and the alternative measures when this function is disabled.
AUTO_COALESCE is a feature that periodically cleans up empty space within indexes to reduce the overall index size. In other words, it efficiently manages index space through an internal mechanism that merges multiple empty blocks into a single block.
Method
Check the AUTO_COALESCE and AUTO_COALESCE_INTERVAL parameter values using the example below.
SQL> select name, value, dflt_value from v$parameters where name = 'AUTO_COALESCE'; NAME VALUE DFLT_VALUE -------------------- -------------------- -------------------- AUTO_COALESCE 2 2 1 row selected.
Options
- 0 : Do not use the index coalescing feature
- 1 : Perform coalescing only on LOB indexes
- 2 : Perform coalescing on all indexes (default)
SQL> select name, value, dflt_value from v$parameters where name = 'AUTO_COALESCE_INTERVAL'; NAME VALUE DFLT_VALUE ------------------------------ -------------------- -------------------- AUTO_COALESCE_INTERVAL 100 100 1 row selected.
Options
AUTO_COALESCE_INTERVAL indicates the frequency at which the index coalescing operation is performed.
- Default: 100 (10 seconds)
- Range: 0 ~ 3600000 (0.1 second ~ 100 hours)
NoteIf AUTO_COALESCE is disabled during operation, performance degradation may occur during index scans due to fragmented index blocks. Additionally, in environments with heavy DML operations, the storage space used by indexes can continuously increase.As an alternative, it is necessary to periodically perform manual COALESCE or INDEX REBUILD operations.