Improving Essbase Performance

In This Section:

Recognizing Basic Design Issues That Affect Optimization

Resetting Databases to Improve Performance

Using Database Settings to Customize for Maximum Performance

Eliminating and Measuring Fragmentation

Enabling Windows 4 GB Tuning (4GT)

Implementing 64-bit Essbase

Finding Additional Optimization Information

Recognizing Basic Design Issues That Affect Optimization

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

Resetting Databases to Improve Performance

You can periodically reset a database and then reload it. Even if you reload a database very often, the main database (.pag) files can grow unless you reset the database.

  To reset a database, use a tool:

Tool

Topic

Location

MaxL

alter database appname.dbname reset

Oracle Essbase Technical Reference

ESSCMD

RESETDB

Oracle Essbase Technical Reference

Using Database Settings to Customize for Maximum Performance

You can customize Essbase for maximum performance, using database settings at the database level in Administration Services, ESSCMD, or MaxL.

Note:

If you are migrating a database, see the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide for information about the default settings after migration.

The following sections list performance settings and describe how to adjust them.

Database Cache Settings

Table 164 describes database cache settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD:

Table 164. Database Cache Settings

Setting

Topic

Location

Index cache size

Sizing the Index Cache

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname set index_cache_size n

  • ESSCMD: SETDBSTATEITEM 12

Data file cache size

Sizing the Data File Cache

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname set data_file_cache_size n

  • ESSCMD: SETDBSTATEITEM 27

Data cache size

Sizing the Data Cache

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname set data_cache_size n

  • ESSCMD: SETDBSTATEITEM 5

Index page size

Fixed size

N/A

Cache memory locking

Deciding Whether to Use Cache Memory Locking

  • Administration Services: Database Properties window, Caches tab

  • MaxL:

    alter database appname.dbname enable cache_pinning

  • ESSCMD: SETDBSTATEITEM 26

See “Setting Database Properties” in Oracle Essbase Administration Services Online Help.

Database Disk Volumes Settings

Table 165 describes database disk volume settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD:

Table 165. Database Disk Volume Settings

Setting

Topic

Location

Volume name

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD:

    SETDBSTATEITEM 23

    SETDBSTATEITEM 24

Partition size

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD:

    SETDBSTATEITEM 23

    SETDBSTATEITEM 24

File type

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD: SETDBSTATEITEM 23

Maximum file size

Specifying Disk Volumes

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname set disk volume

  • ESSCMD: SETDBSTATEITEM 23

See “Setting Disk Volumes” in Oracle Essbase Administration Services Online Help.

Database Transaction Control Settings

Table 166 describes database transaction control settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD:

Table 166. Database Transaction Control Settings

Setting

Topic

Location

Isolation level

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable committed_mode

  • ESSCMD: SETDBSTATEITEM 18

Commit Blocks

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable committed_mode

    and

    alter database appname.dbname set implicit_commit after n blocks

  • ESSCMD: SETDBSTATEITEM 21

Commit Rows

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable committed_mode

    and

    alter database appname.dbname set implicit_commit after n rows

  • ESSCMD: SETDBSTATEITEM 22

Wait for write access to locked data block

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname set lock_timeout

  • ESSCMD: SETDBSTATEITEM 20

Pre-image access

Understanding Isolation Levels

  • Administration Services: Database Properties window, Transactions tab

  • MaxL:

    alter database appname.dbname enable pre_image_access

  • ESSCMD: SETDBSTATEITEM 19

See “Setting Data Integrity Options” in Oracle Essbase Administration Services Online Help.

Miscellaneous Database Settings

Table 167 describes miscellaneous database settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD:

Table 167. Miscellaneous Database Settings

Setting

Topic

Location

Retrieval buffer size

Setting the Retrieval Buffer Size

  • Administration Services: Database Properties window, General tab

  • MaxL:

    alter database appname.dbname set retrieve_buffer_size n

  • ESSCMD SETDBSTATEITEM 16

Retrieval sort buffer size

Setting the Retrieval Sort Buffer Size

  • Administration Services: Database Properties window, General tab

  • MaxL:

    alter database appname.dbname set retrieve_sort_buffer_size n

  • ESSCMD: SETDBSTATEITEM 17

Data compression

Data Compression

  • Administration Services: Database Properties window, Storage tab

  • MaxL:

    alter database appname.dbname enable compression

    and

    alter database appname.dbname set compression type

  • ESSCMD:

    SETDBSTATEITEM 14

    SETDBSTATEITEM 15

Maximum memory for trigger definitions

Understanding Triggers Definitions

MaxL:

create or replace trigger, alter trigger display trigger, and drop trigger

See “Setting Database Properties” in Oracle Essbase Administration Services Online Help.

Eliminating and Measuring Fragmentation

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 more likely will be reused.

In some cases, fragmentation cannot be reduced completely. Fragmentation is likely to occur in the following situations:

  • Read/write databases that users are constantly updating with data

  • Databases that execute calculations around the clock

  • Databases that frequently update and recalculate dense members

  • Data loads that are poorly designed

  • Databases that contain a significant number of Dynamic Calc and Store members

  • Databases that use an isolation level of uncommitted access with commit block set to zero

If you experience performance slowdowns, check to see if there is too much fragmentation of the database; if there is, you can take steps to reduce it.

Measuring Fragmentation

You can measure fragmentation using the average fragmentation quotient statistic or average clustering ratio.

Using the average fragmentation quotient:

In ESSCMD, look at the Average Fragmentation Quotient that is returned when you execute the GETDBSTATS command. Use the information in Table 168 to evaluate whether the level likely will cause performance problems:

Table 168. Measuring Fragmentation Thresholds Using the Average Fragmentation Quotient

Database Size

Fragmentation Quotient Threshold

Small (<200 MB)

60% or greater

Medium (<2 GB)

40% or greater

Large (>2 GB)

30% or greater

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

  • The reported value of the Fragmentation Quotient is more accurate when no other write transactions run on the database.

  • For databases less than 50 MB using the Direct I/O access mode, the fragmentation quotient tends to be high. A high fragmentation quotient does not necessarily indicate a need to reduce fragmentation, because the free space is created in 8 MB chunks, and all of it might not get used immediately.

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.

  To view the average clustering ratio for a database, use a tool:

Tool

Topic

Location

Administration Services

Viewing Fragmentation Statistics

Oracle Essbase Administration Services Online Help

ESSCMD

GETDBSTATS

Oracle Essbase Technical Reference

.

Preventing or Removing Fragmentation

You can prevent and remove fragmentation:

  • To prevent fragmentation, optimize data loads by sorting load records based on sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members, see Grouping Sparse Member Combinations.

  • To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file. See the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.

  • To remove fragmentation, force a dense restructure of the database. See Types of Database Restructuring.

Enabling Windows 4 GB Tuning (4GT)

Essbase supports Microsoft 4 GB Tuning (4GT). 4GT enables users with extremely large databases to take advantage of a larger address space to improve performance.

Enabling the Windows 4GT feature may benefit users if the Essbase installation has the following characteristics:

  • Essbase is configured to use direct I/O. Enabling 4GT on Essbase installations configured for buffered I/O is not recommended.

  • The index and data caches are sized correctly, and Essbase performance is consistently improved by increasing the data file cache, but further increases are bounded by the previous 2 GB addressability limitation. For information about setting cache values, see Optimizing Essbase Caches.

For additional information about the Microsoft Windows 4GT feature, see http:\\www.microsoft.com.

Implementing 64-bit Essbase

Because processes on 64-bit have greatly increased memory addressability over 32-bit processes, the 64-bit edition of Essbase can handle larger outlines and cache sizes than 32-bit Essbase. In computing environments that support it, implementing 64-bit Essbase can improve the performance of existing applications and can sustain much larger applications. For information on platform support for 64-bit Essbase, see the Oracle Hyperion Enterprise Performance Management System Certification Matrix (http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html).

On 64-bit Essbase, you can set cache sizes larger than the existing 32-bit limits. In Essbase clients, the maximum values you can set for the data cache, data file cache, and index cache is 4 GB. You can enable larger values for the data cache and data file cache using the MEMSCALINGFACTOR configuration setting. See the Oracle Essbase Technical Reference.

The maximum thread settings are higher for 64-bit Essbase than for 32-bit Essbase. Table 169 lists the maximum thread settings for each. For information about changing thread settings, see the Oracle Essbase Technical Reference. For information about multi-threading, see Multithreading.

Table 169. Maximum Thread Settings for 32-bit and 64-bit Essbase

Setting

32-bit Maximum

64-bit Maximum

AGENTTHREADS

500

1024

SERVERTHREADS

500

1024

DLTHREADSPREPARE

16

32

DLTHREADSWRITE

16

32

Default retrieval buffer settings for 64-bit Essbase are higher than for 32-bit Essbase. Table 170 lists the default retrieval buffer settings for each. See Changing Buffer Size.

Table 170. Default Retrieval Buffer Settings for 32-bit and 64-bit Essbase

Setting

32-bit Default

64-bit Default

Retrieval Buffer

10 KB

20 KB

Retrieval Sort Buffer

10 KB

20 KB

Note:

Because of internal data structure size changes, 64-bit Essbase requires a larger retrieval sort buffer size than 32-bit Essbase. If you encounter the error, "Sort buffer limit of [x] rows have been exceeded" (where x is the current maximum number of rows allowed for the current buffer size), increase the retrieval sort buffer size by a factor of two.

Finding Additional Optimization Information

Using Database Settings to Customize for Maximum Performance 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: