Essbase Tuning Guide For Planning Applications
The following are basic guidelines for Essbase tuning that are intended to cover the use of Essbase with Planning applications. There is a great deal more that can be customized and tuned in Essbase that is not covered here. See the official Essbase documentation for more details.
To improve performance go through each of the suggested optimizations below and then test to see where the most progress is made. Always remember to back up your Essbase data before making any changes.
1. Block size:
The recommended block size is between 8Kb and 100Kb. One way to alter block size is to change sparse dimensions to dense or vice versa. Changing a dense dimension to sparse will reduce block size and increase the overall number of blocks. See the Essbase Database Administrator’s Guide and Planning documentation for more information on setting dimensions to be dense or sparse.
2. Virtual memory:
Recommended virtual memory setting (for Windows systems): 2 to 3 times the RAM available. 1.5 times the RAM on older systems.
Minimum: 1 Mb
Default: 10 Mb
Recommended: combined size of all ESS*.IND files if possible; otherwise as large as possible given the available RAM.
The .IND files are found on the Essbase server under \app\<appname>\<dbname>. The size of the .IND files are also visible from the “Storage” tab of the database properties in the EAS console.
Data File Cache:
Minimum: 8 Mb
Default: 32 Mb
Recommended: combined size of all ESS*.PAG files if possible; otherwise as large as possible given the available RAM, up to a maximum of 2Gb.
The .PAG files are found on the Essbase server under \app\<appname>\<dbname>. The size of the .PAG files are also visible from the “Storage” tab of the database properties in the EAS console.
The Data File Cache is not used if the database is buffered rather than direct I/O (Check the “Storage” tab). Since all Planning databases are buffered, and most customers use buffered for native Essbase applications too, this cache setting is usually not relevant. The Data Cache is the setting that matters in most cases (see below).
Minimum: 3 Mb
Default: 3 Mb
Recommended: 0.125 x combined size of all ESS*.PAG files, if possible, otherwise as large as possible given the available RAM.
Note: a useful indication of the health of the caches can be gained by looking at the “Hit ratio” for the cache on the Statistics tab in EAS. 1.0 is the best possible ratio, lower means lower performance.
4. Disk space:
The recommended disk space is a minimum of double the combined total of all .IND and .PAG files. You need double the space because you have to have room for a restructure, which will require twice the usual storage space whilst it is ongoing.