Fragmentation of Essbase block storage databases occurs as a result of routine data changes, data loads, and calculations. Essbase has two database statistics that measure database fragmentation: the average clustering ratio and the average fragmentation quotient. Check out this article from the Oracle Customer Engineering & Advocacy Lab for a more thorough description of BSO data fragmentation:
https://blogs.oracle.com/pa/entry/essbase_bso_data_fragmentation
In the recent Essbase version, I have become more dependent on the average fragmentation quotient than the average clustering ratio as a measure of fragmentation because I nearly always multithread database restructures by using RESTRUCTURETHREADS in the Essbase configuration. Because restructures are using multiple threads, I can never get to a fully clustered database, however, multithreaded restructures will significantly reduce the average fragmentation quotient.
You can manually check for fragmentation in an Essbase database using two methods. In Essbase Administration Services (EAS) Console, simply right-click the database you wish to check then select Edit > Properties in the context menu. Select the Statistics tab of the properties window and note the last Blocks statistic displayed, “Average clustering ratio.”
The second method is a single line MAXL command which will output all of the database statistics including the average fragmentation quotient.
query database ‘APPNAME’.’DBNAME’ get dbstats data_block;
The query produces a single row of data with all of the database block properties including the average fragmentation quotient. The image below is a snap of just the two fragmentation properties.
This data can be easily output to a file by using the spool command in a MAXL script as follows:
spool stdout on to ‘E:\Temp\mydbstats.txt’;
query database ‘APPNAME’.’DBNAME’ get dbstats data_block;
When the script is executed, the output looks like this:
I will then run the following command to remove the command from the spooled output so the file is reduced to a very consistent layout.
type E:\Temp\mydbstats.txt | findstr /v MAXL >E:\Temp\essdbstats.txt
And now the output looks like…
The file format is consistent across many versions of Essbase so I can reuse this methodology across nearly all of our support clients. Once the file is output, I can use a variety of tools to check the contents of the file at the precise position of the statistics I wish to monitor. Nearly all commercial monitoring tools have some sort of text file parser which can be leveraged to extract what is needed.
I use a remote monitoring and management tool which can capture the contents of a text file then use substring functions to return the average clustering ratio and average fragmentation quotient as variables:
| Variable | Start Position | Length |
| AvgClusterRatio | 894 | 4 |
| AvgFragQuotient | 912 | 4 |
Finally, I compare these values to thresholds I set for each database and raise alerts in the monitoring tool accordingly.
