You can improve Essbase performance with these basic techniques:
Use the following list to identify basic design issues that affect optimization outside this volume:
You can periodically reset your database using the ESSCMD command RESETDB, and then reload it.Even if you reload your database very often, your main database files, .pag files, can grow unless you use RESETDB.
You can customize Essbase for maximum performance, using database settings at the database level:
Table 77 lists each Database > Storage setting in Application Manager or the equivalent in ESSCMD or MaxL that can affect performance, and describes how to adjust each setting.
For information on database settings in Essbase Administration Services, see Essbase Administration Services Online Help.
Before you use the table with Application Manager, use this procedure:
If you plan to use the ESSCMD or MaxL commands instead of Application Manager, see the Technical Reference in the docs directory for instructions.
The MaxL command for all of these is alter database. See the MaxL Language Reference for more information.
Note: If you are migrating your database, see the Essbase Installation Guide for information about the default settings after migration.
Setting |
Application Manager Database > Setting |
ESSCMD Command |
Default Value and Comments |
---|---|---|---|
SETDBSTATEITEM 12 |
10 MB (default), 1 MB (minimum) Recommendation: Combined size of all ESSn.IND files if possible; otherwise, as large as possible. See Sizing the Index Cache. |
||
SETDBSTATEITEM 27 |
32 MB (default), 8 MB (minimum) Recommendation: Combined size of all ESSn.PAG files if possible; otherwise, as large as possible. See Sizing the Data File Cache. |
||
SETDBSTATEITEM 5 |
Recommendation: 0.125 * data file cache size value. However, some calculations require a larger data cache size. In general, if you must choose between allocating memory to the data file cache and allocating memory to the data cache, choose the data file cache. See Sizing the Data Cache. |
||
SETDBSTATEITEM 13 |
|||
SETDBSTATEITEM 26 |
Disabled (default). Use the default if you do not need to give priority usage of the system RAM to the Essbase kernel. Recommendation: If you want to give priority usage of the system RAM to the Essbase kernel, then enable cache memory locking. Be careful to leave at least one third of the system RAM for operations other than the Essbase Kernel. |
||
SETDBSTATEITEM 18 |
See Ensuring Data Integrity, for more information. |
||
SETDBSTATEITEM 21 |
Setting (default or other) ignored unless the isolation level is uncommitted. A zero value means that no synchronization point occurs; Essbase commits all affected blocks at the end of a transaction. If either Commit Blocks or Commit Rows has a non-zero value, a synchronization point occurs when the first threshold is reached. For example, assume Commit Blocks is 10 and Commit Rows is 0. When you load data, a synchronization point occurs after 10 blocks are updated. |
||
SETDBSTATEITEM 22 |
0 (default, no implicit commit); synchronization point occurs at the end of a transaction. Commit Rows setting is ignored unless isolation level is uncommitted |
||
SETDBSTATEITEM 20 |
|||
SETDBSTATEITEM 19 |
|||
SETDBSTATEITEM 23 SETDBSTATEITEM 24 |
If you do not specify a volume name, the Essbase Kernel uses only the volume that ARBORPATH points to and fills the entire volume as needed. Replaces DISKVOLUMES volume_name in essbase.cfg. volume_name is then used only for initial migration. |
||
SETDBSTATEITEM 23 SETDBSTATEITEM 24 |
Unlimited (default). Uses all available space on the specified volume. |
||
SETDBSTATEITEM 23 |
|||
SETDBSTATEITEM 23 |
|||
SETDBSTATEITEM 16 |
Note: In Version 4, this setting is specified as REPTKBYTEBUF in essbase.cfg. |
||
SETDBSTATEITEM 17 |
Note: In Version 4, this setting is specified as REPTKBYTESORTBUF in the essbase.cfg file. |
||
SETDBSTATEITEM 14 SETDBSTATEITEM 15 |
Bitmap compression enabled. (default). In Essbase Application Manager, you use a single setting to choose bitmap compression, RLE (run-length encoding) compression, or no compression. In ESSCMD, you use SETDBSTATEITEM 14 to enable or disable compression and SETDBSTATEITEM 15 to control compression type. |
Fragmentation is unused disk space. Fragmentation is created when Essbase writes a data block to a new location on disk and leaves unused space in the former location of the data block. Block size increases because data from a data load or calculation is appended to the blocks; the blocks must therefore be written to the end of a data file.
The Essbase Kernel merges adjacent fragments into increasingly larger fragments so that unused space is more likely to be re-used.
In some cases, fragmentation cannot be reduced completely. Fragmentation is likely to occur with the following:
If you experience performance slow-downs, you can check to see if there is too much fragmentation of your database, and if there is, you can take steps to reduce the fragmentation:
You can measure fragmentation using the average clustering ratio or average fragmentation quotient statistic from ESSCMD:
In ESSCMD, look at the Average Fragmentation Quotient that is returned when you execute GETDBSTATS command. Use this table to evaluate whether or not the level of fragmentation is likely to be causing performance problems:
Database Size |
Fragmentation Quotient threshold |
---|---|
Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:
The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation. You can use any of the following tools to view the average clustering ratio for a database.
Tool |
Instructions |
For more information |
---|---|---|
You can prevent and remove fragmentation:
Table 77 provides general-purpose information and does not account for the wide variety of configuration possibilities. For more information about performance and server, application, or other settings, see these chapters:
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |