Essbase BSO Data Fragmentation
- by Ann Donahue
Essbase BSO Data Fragmentation
Data fragmentation naturally occurs in Essbase Block Storage (BSO) databases where there are a lot of end user data updates, incremental data loads, many lock and send, and/or many calculations executed. If an Essbase database starts to experience performance slow-downs, this is an indication that there may be too much fragmentation. See Chapter 54 Improving Essbase Performance in the Essbase DBA Guide for more details on measuring and eliminating fragmentation: http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/daprcset.html
Fragmentation is likely to occur in the following situations:
Read/write databases that users are constantly updating data
Databases that execute calculations around the clock
Databases that frequently update and recalculate dense members
Data loads that are poorly designed
Databases that contain a significant number of Dynamic Calc and Store members
Databases that use an isolation level of uncommitted access with commit block set to zero
There are two types of data block fragmentation
Free space tracking, which is measured using the Average Fragmentation Quotient statistic.
Block order on disk, which is measured using the Average Cluster Ratio statistic.
Average Fragmentation Quotient
The Average Fragmentation Quotient ratio measures free space in a given database. As you update and calculate data, empty spaces occur when a block can no longer fit in its original space and will either append at the end of the file or fit in another empty space that is large enough. These empty spaces take up space in the .PAG files. The higher the number the more empty spaces you have, therefore, the bigger the .PAG file and the longer it takes to traverse through the .PAG file to get to a particular record. An Average Fragmentation Quotient value of 3.174765 means the database is 3% fragmented with free space.
Average Cluster Ratio
Average Cluster Ratio describes the order the blocks actually exist in the database. An Average Cluster Ratio number of 1 means all the blocks are ordered in the correct sequence in the order of the Outline. As you load data and calculate data blocks, the sequence can start to be out of order. This is because when you write to a block it may not be able to place back in the exact same spot in the database that it existed before. The lower this number the more out of order it becomes and the more it affects performance. An Average Cluster Ratio value of 1 means no fragmentation. Any value lower than 1 i.e. 0.01032828 means the data blocks are getting further out of order from the outline order.
Eliminating Data Block Fragmentation
Both types of data block fragmentation can be removed by doing a dense restructure or export/clear/import of the data.
There are two types of dense restructure:
1. Implicit Restructures
Implicit dense restructure happens when outline changes are done using EAS Outline Editor or Dimension Build. Essbase restructures create new .PAG files restructuring the data blocks in the .PAG files. When Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks.
Empty blocks are NOT removed with implicit restructures.
2. Explicit Restructures
Explicit dense restructure happens when a manual initiation of the database restructure is executed.
An explicit dense restructure is a full restructure which comprises of a dense restructure as outlined above plus the removal of empty blocks
Empty Blocks vs. Fragmentation
The existence of empty blocks is not considered fragmentation. Empty blocks can be created through calc scripts or formulas. An empty block will add to an existing database block count and will be included in the block counts of the database properties. There are no statistics for empty blocks. The only way to determine if empty blocks exist in an Essbase database is to record your current block count, export the entire database, clear the database then import the exported data. If the block count decreased, the difference is the number of empty blocks that had existed in the database.
