Database Fragmentation
Fragmentation is produced when Essbase writes a modified data block to a new location on disk and leaves unused space in the former location of the data block, as shown on the slide. (Modified blocks are larger because data from a data load or calculation is added to the blocks.)
Measuring Database Fragmentation
Average clustering ratio:
- An inverse measure of data block “scatter”
- Higher average clustering ratio means lower fragmentation
Managing Database Fragmentation : Two management strategies that are not mutually exclusive
- Prevention strategy
– Recognize contributing factors
– Optimize data loads
- Reduction strategy
– Export and reload
– Dense database restructure
Preventing Database Fragmentation: Tactics to help prevent database fragmentation
- Recognizing contributing factors
– Read/Write/Calculation frequency
– Data loads
– Calc and Store
– Isolation level
- Optimizing data loads
– Fragmentation effect
– Load time effect
Reducing Database Fragmentation: Strategies to reduce database fragmentation
- Exporting and reloading
- Performing a full database restructure
Options for Restructuring
- Outline only—Restructures the outline only; does not restructure the index and data files (least time-consuming)
- Sparse—Restructures and creates a new index.
- Full—Restructures blocks and creates new index and data files (most time consuming but removes database fragmentation)
