Skip to main content


How to Monitor Essbase Database Fragmentation

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:

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.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Cris Dunn

Cris Dunn is the manager of Perficient's EPM SupportNet practice which provides direct support for applications and infrastructure surrounding many organizations' EPM software implementations. He is also an Oracle University certified instructor and teaches everything around Essbase as well as the EPM installation and configuration classes. When he is not working, you will find Cris engaged in His church serving as the treasurer, musician, nursery worker, and, when necessary, toilet scrubber.

More from this Author

Follow Us