Improving Essbase Performance

You can improve Essbase performance with these basic techniques:

Using Basic Design for Optimization

Use the following list to identify basic design issues that affect optimization outside this volume:

Reset Database to Increase Performance

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.

Database Settings Quick-Reference Table

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:

  1. Start Essbase.
  2. Start Application Manager.
  3. From Application Manager, select the Server > Connect menu command to connect to the appropriate server.
  4. Selected the application and database whose performance you wish to improve.
  5. Selected Database > Settings and display the Settings dialog box.

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.

Table 77: Database Settings and Defaults  

Setting
Application Manager Database > Setting
ESSCMD Command
Default Value and Comments

Index cache size

Storage

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.

Data file cache size

Storage

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.

Data cache size

Storage

SETDBSTATEITEM 5 

3 MB (default and minimum)

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.

Index page size

Storage

SETDBSTATEITEM 13 

Fixed size is 8 KB. You cannot change the index page size.

Cache memory locking

Storage

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.

Isolation level

Transaction

SETDBSTATEITEM 18 

Uncommitted (default).

See Ensuring Data Integrity, for more information.

Commit Blocks

Transaction

SETDBSTATEITEM 21 

3,000 (default).

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.

Commit Rows

Transaction

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

See the note under Commit Blocks.

Wait for write access to locked data block

Transaction

SETDBSTATEITEM 20 

Indefinite wait in Application Manager or -1 in ESSCMD.

Setting ignored unless the isolation level is committed.

Pre-image access

Transaction

SETDBSTATEITEM 19 

Enabled. (default).

Setting ignored unless the isolation level is committed.

Disk Volumes: volume name

Storage

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.

Disk volumes: partition size

Storage

SETDBSTATEITEM 23 
SETDBSTATEITEM 24 

Unlimited (default). Uses all available space on the specified volume.

Disk volumes: file  type

Storage

SETDBSTATEITEM 23 

Index and data (default).

Disk volumes: maximum file size

Storage

SETDBSTATEITEM 23 

2 GB (default).

Retrieval buffer size

General

SETDBSTATEITEM 16 

10,240 B or, 10 KB.

Note: In Version 4, this setting is specified as REPTKBYTEBUF in essbase.cfg.

Retrieval sort buffer size

General

SETDBSTATEITEM 17 

10,240 B or, 10 KB.

Note: In Version 4, this setting is specified as REPTKBYTESORTBUF in the essbase.cfg file.

Data compression

Storage

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.



Eliminating Fragmentation

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:

Measuring Fragmentation

You can measure fragmentation using the average clustering ratio or average fragmentation quotient statistic from ESSCMD:

Using the Average Fragmentation Quotient

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

Small ( up to 200 Mb )

60% or higher

Medium ( up to 2 Gb )

40% or higher

Large ( greater than 2Gb )

30% or higher



Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:

Using the Average Clustering Ratio

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

Application Manager

Select Database > Information and click the Statistics tab

Essbase Application Manager Online Help

Administration Services

Click the Statistics tab on the Database Properties window

Essbase Administration Services Online Help

ESSCMD

GETDBSTATS

Technical Reference in the docs directory



Preventing or Removing Fragmentation

You can prevent and remove fragmentation:

Finding Other Optimization Information

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