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

1. Free space tracking, which is measured using the Average Fragmentation Quotient statistic.
2. 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.