Optimizing Essbase Caches

In This Section:

Understanding Essbase Caches

Deciding Whether to Use Cache Memory Locking

Sizing Caches

Fine-Tuning Cache Settings

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

Understanding Essbase Caches

Essbase uses five memory caches to coordinate memory usage:

Table 171. List of Essbase Caches

Cache

Description

Index cache

A buffer in memory that holds index pages. How many index pages are in memory simultaneously depends upon the amount of memory allocated to the cache.

Data file cache

A buffer in memory that holds compressed data files (.pag files). Essbase allocates memory to the data file cache during data load, calculation, and retrieval operations, as needed. The data file cache is used only when direct I/O is in effect.

Data cache

A buffer in memory that holds uncompressed data blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations, as needed.

Calculator cache

A buffer in memory that Essbase uses to create and track data blocks during calculation operations.

Dynamic calculator cache

A buffer in memory that Essbase uses to store all of the blocks needed for a calculation of a Dynamic Calc member in a dense dimension (for example, for a query).

Essbase provides default size settings for each cache; you can adjust the sizes as needed for each database. Appropriate cache size is affected by many factors, including database size, block size, index size, and available server memory. Cache size settings can significantly affect database and general server performance.

The following topics provide information about sizing caches for performance.

Deciding Whether to Use Cache Memory Locking

Before setting cache sizes, you must enable cache memory locking or leave cache memory locking disabled (the default).

The setting for cache memory locking controls whether the memory used for the index cache, data file cache, and data cache are locked into physical memory, giving the Essbase kernel priority use of system RAM.

To use cache memory locking, you must be using direct I/O (buffered I/O is the default I/O access mode), and direct I/O requires a larger index cache size than buffered I/O. See Managing Database Settings.

Locking improves performance for an Essbase database because the system memory manager need not swap the memory used by the caches when swapping the memory used by Essbase Server. By default, cache memory locking is turned off.

Enabling cache memory locking gives the Essbase Kernel priority use of system RAM. If you enable cache memory locking, leave at least one-third of the system RAM available for non-Essbase Kernel use. If you do not want to give the Essbase Kernel priority usage of system RAM, do not enable cache memory locking.

If you are running Essbase on Solaris, run the Bourne shell script, root.sh, before starting Essbase and enabling cache memory locking. This script sets the server to run in Superuser mode so that it can lock memory. See the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

  To enable cache memory locking, use a tool:

Tool

Topic

Location

Administration Services

Enabling Cache Memory Locking

Oracle Essbase Administration Services Online Help

MaxL

alter database enable cache_pinning

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM 26

Oracle Essbase Technical Reference

Sizing Caches

The settings that you should use for each of the caches that you can configure depend on data distribution and the dense/sparse configuration of the database. The maximum combined total size of the caches should equal the amount of available memory after the memory required by Essbase is taken into account.

The needs for each site, even for a particular database, can vary. Depending on the complexity and type of each operation, Essbase allocates as much memory for the data file cache and the data cache as needed. Use the recommended values in this section to estimate enough memory for optimal performance.

If you are using Essbase for the first time, cache sizes are set automatically to the default values discussed in the following sections. Use these topics to find and understand recommendations for each cache size.

Note:

Changes made to cache sizes take effect the next time you start the database.

Sizing the Index Cache

The index is stored in index files on disk. When a database is active, the most recently accessed index pages are held in the index cache. How much of the index can be held in memory simultaneously depends on the amount of memory that you allocate to the index cache.

Note:

The size of index pages is fixed at 8 K to reduce input-output overhead, as well as to simplify database migration.

The effectiveness of the index cache size depends on the nature of the calculation. For example, if you were reloading and recalculating an entire database (such as a database that is refreshed each month), a high index cache size is not helpful, because Essbase is creating blocks rather than searching the index cache for existing blocks during calculation.

Table 172 shows default and recommended settings for the index cache:

Table 172. Index Cache Size Settings

Minimum Value

Default Value

Recommended Value

1024 KB (1,048,576 bytes)

Buffered I/O: 1024 KB (1,048,576 bytes)

Direct I/O: 10,240 KB (10,485,760 bytes)

Combined size of all essn.ind files, if possible; otherwise, as large as possible. Do not set this cache size higher than the total index size, because no performance improvement results. To determine the total index size, see Index Files.

For information about changing the I/O access mode for a database, or about changing the default for all newly created databases, see Understanding Buffered I/O and Direct I/O.

In general, if you are using direct I/O, make the index cache as large as system resources allow. If you are using buffered I/O, make the index cache as small as possible.

For information on testing and fine-tuning cache settings, see Fine-Tuning Cache Settings.

Changing the Index Cache Size

  To set the size of the index cache, use a tool:

Tool

Topic

Location

Administration Services

Setting Cache Sizes

Oracle Essbase Administration Services Online Help

MaxL

alter database set index_cache_size

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM 12

SETDBSTATE

Oracle Essbase Technical Reference

Sizing the Data File Cache

The data file cache holds data files (.pag files) in memory if you are using direct I/O. If you are not using direct I/O, the data file cache is not used. How much of the data within data files can fit into memory simultaneously depends on the amount of memory you allocate to the data file cache.

In general, if you must choose whether to allocate memory to the data cache or to the data file cache, choose the data file cache if you are using direct I/O.

Table 173 shows default and recommended settings for the data file cache:

Table 173. Data File Cache Size Settings

Minimum Value

Default Value

Recommended Value

Direct I/O: 10,240 KB (10,485,760 bytes)

Direct I/O: 32,768 KB (33,554,432 bytes)

Combined size of all essn.pag files, if possible; otherwise, as large as possible.

This cache setting not used if Essbase is set to use buffered I/O.

In general, if you are using direct I/O, make the data file cache as large as system resources allow. If you are using buffered I/O, the data file cache is not used.

For information on testing and fine-tuning cache settings, see Fine-Tuning Cache Settings.

Changing the Data File Cache Size

  To set the size of the data file cache, use a tool:

Tool

Topic

Location

Administration Services

Setting Cache Sizes

Oracle Essbase Administration Services Online Help

MaxL

alter database set data_file_cache_size

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM 27

Oracle Essbase Technical Reference

Sizing the Data Cache

Data blocks reside on physical disk and in memory. The number of blocks that can be held in the data cache simultaneously depends on how much memory you allocate to the data cache.

When a block is requested, Essbase searches the data cache for the block. If Essbase finds the block in the cache, it is accessed immediately. If the block is not found in the cache, Essbase searches the index for the appropriate block number and then uses the index entry of the block to retrieve it from the appropriate data file on disk. Retrieving a requested block from the data cache is faster and therefore improves performance.

In general, if you must choose whether to allocate memory to the data cache or to the data file cache, choose the data file cache if you are using direct I/O.

Table 174 shows default and recommended settings for the data cache:

Table 174. Data Cache Size Settings

Minimum Value

Default Value

Recommended Value

3072 KB (3145728 bytes)

3072 KB (3,145,728 bytes)

0.125 * the value of data file cache size. Increase value if any of these conditions exist:

  • Many concurrent users are accessing different data blocks.

  • Calculation scripts contain functions on sparse ranges, and the functions require all members of a range to be in memory (for example, when using @RANK and @RANGE).

  • For data load, the number of threads specified by the DLTHREADSWRITE setting is high and the expanded block size is large.

In certain cases, you might need to increase the data cache when running concurrent calculations; for example, when concurrent calculations do not share any common blocks and the sparse member with the largest number of children has all its children blocks populated in the database. To calculate the data cache for concurrent calculations, use the following formula:

(Size of big block in bytes) * max(Number of children for a Sparse member) * (Number of concurrent batch calc processes) * 2

If other concurrent operations (such as data loads and queries) take place when running concurrent calculations, increase the data cache even more to accommodate these requests.

Make the data cache as small as possible whether you are using buffered I/O or direct I/O.

For information on testing and fine-tuning cache settings, see Fine-Tuning Cache Settings.

Note:

When running Essbase on 64-bit platforms, optimal data cache and data file cache settings may be larger than 4 GB. Although you cannot specify settings larger than 4 GB in Essbase clients, you can enable larger settings using the MEMSCALINGFACTOR configuration setting. See the Oracle Essbase Technical Reference.

Changing the Data Cache Size

  To set the size of the data cache, use a tool:

Tool

Topic

Location

Administration Services

Setting Cache Sizes

Oracle Essbase Administration Services Online Help

MaxL

alter database set data_cache_size

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATEITEM 5

SETDBSTATE

Oracle Essbase Technical Reference

Sizing the Calculator Cache

Essbase can create a bitmap, whose size is controlled by the size of the calculator cache, to record and track data blocks during a calculation. Determining which blocks exist using the bitmap is faster than accessing the disk to obtain the information, particularly if calculating a database for the first time or calculating a database when the data is sparse.

Essbase uses the calculator cache bitmap if the database has at least two sparse dimensions and either of these conditions is also met:

  • You calculate at least one full sparse dimension.

  • You specify the SET CACHE ALL command in a calculation script.

The best size for the calculator cache depends on the number and density of the sparse dimensions in your outline. Use the following topics to understand the calculator cache bitmap, size the calculator cache, and change the size of the calculator cache (and therefore the largest possible size for the bitmap), if required:

Understanding the Calculator Cache Bitmap

For the calculator cache, Essbase separates sparse dimensions in the database into two groups:

  • Bitmap dimensions: The sparse dimensions from the database outline that Essbase fits into the bitmap until the bitmap is full. Each member combination of the sparse dimensions placed in the bitmap occupies 1 bit of memory, and there must be enough space in the bitmap for every member combination of a sparse dimension for it to be placed in the bitmap.

  • Anchoring dimensions: The remaining one or more sparse dimensions in the database outline that do not fit into the bitmap.

Essbase starts with the first sparse dimension in the database outline and fits as many sparse dimensions as possible into the bitmap. The dimensions that fit are the bitmap dimensions. Essbase stops the process when it cannot fit another complete sparse dimension into the bitmap. Because the calculator cache controls the size of the bitmap, the number of sparse dimensions that can fit in the bitmap depends on the size of the calculator cache (and the number and size of the sparse dimensions).

The remaining sparse dimensions are the anchoring dimensions. For anchoring dimensions, Essbase cannot use the bitmap to determine whether blocks exist.

To see which dimensions are anchoring dimensions and which are bitmap dimensions, use the SET MSG DETAIL calculation command to display bitmap information in the application log.

Carefully order the sparse dimensions in your outline so that as many dimensions as possible can be placed into the bitmap. Start with the dimension that contains the fewest members and continue until the dimension with the most members is last. This order allows more dimensions to fit into the bitmap and results in improved calculation performance.

Note:

The order of sparse dimensions in the outline also affects query performance. See Optimizing Query Performance.

Essbase uses a single bitmap if there are multiple anchoring dimensions or if the calculator cache is not large enough to support multiple bitmaps, and uses multiple bitmaps if there is one anchoring dimension.

A single bitmap has these properties:

  • A single bitmap is used to track child blocks.

  • A single bitmap uses the least memory but is less efficient than multiple bitmaps.

Multiple bitmaps have these properties:

  • Multiple bitmaps are used, one to track child blocks and one to track parent blocks.

  • Multiple bitmaps use more memory but are faster than using a single bitmap. The performance improvement is particularly high when you are calculating the database for the first time.

  • The number of bitmaps used is determined by the maximum number of dependent parents for any members in the anchoring dimension. A member has one dependent parent, unless it has a shared member. For example, consider the Product dimension of the Sample.Basic database. The member Cola (100-10) has one parent, Colas (100). However, Diet Cola (100-20) has two parents, Diet Drinks (Diet) and Colas (100). No members of Product have more than two dependent parents. Therefore, if Product is the anchoring dimension, the maximum dependent parents is two.

Essbase chooses one of three options, as shown in Table 175, for the calculation:

Table 175. Calculator Cache Options

Option

Method

Performance Rating

1

Single anchoring dimension, multiple bitmaps

1

2

Single anchoring dimension, single bitmap

2

3

Multiple anchoring dimensions, single bitmap

3

Essbase chooses the optimal performance method for a database calculation, based on the size of the calculator cache. If the calculator cache size is too small for any of the above options, Essbase does not use a calculator cache. Calculation performance may be significantly impaired.

Enabling parallel calculation may change which calculator cache option is used. See Calculator Cache.

Caution!

If you are calculating the database for the first time, the size of the calculator cache is particularly significant for calculation performance. If possible, ensure that the calculator cache is large enough for Essbase to use the optimal calculator cache option.

Calculating the Calculator Cache Size

The optimum size of the calculator cache depends on the memory that the system has available and on the nature and configuration of the database.

Using the following formula, you can calculate the calculator cache size required for Essbase to choose each of the three options in Table 175, Calculator Cache Options:

Calculator cache = Bitmap size in bytes * Number of bitmaps

where

Bitmap size in bytes = Max ((member combinations on the bitmap dimensions / 8), 4)

and where

Number of bitmaps = Maximum number of dependent parents in the anchoring dimension + 2 constant bitmaps

Note:

The minimum bitmap size is 4 bytes. If (member combinations on the bitmap dimensions/8) is less than 4 bytes, Essbase uses a bitmap size of 4 bytes.

Consider a sample database with five sparse dimensions (S1 to S5), as shown in Table 176:

Table 176. Example: Sample Database with Five Sparse Dimensions

Sparse Dimension

Number of Members

Dependent Parents

S1

20

Not applicable

S2

20

Not applicable

S3

50

Not applicable

S4

50

Not applicable

S5

200

3

Use this sample database for the following sample calculations:

Option 1: Single Anchoring Dimension, Multiple Bitmaps

For this sample calculation, assume the following facts about a database (from Table 175, Calculator Cache Options):

  • Bitmap dimensions: S1, S2, S3, S4

  • Anchoring dimension: S5

  • Dependent parents in anchoring dimension: 3

Perform this calculation:

Bitmap size in bytes = (S1 * S2 * S3 * S4) / 8
                     = (20 * 20 * 50 * 50) / 8
                     = 125,000 bytes

Number of bitmaps = Maximum number of dependent parents in the anchoring dimension
                  = + 
                  = 2 constant bitmaps
                  = 3 + 2
                  = 5

Calculator cache = Bitmap size * Number of bitmaps
                 = 125,000 * 5
                 = 625,000 bytes

For Essbase to use multiple bitmaps for this database with one anchoring dimension, the calculator cache must be 625,000 bytes.

Option 2: Single Anchoring Dimension, Single Bitmap

For this sample calculation, assume the following facts about a database (from Table 175, Calculator Cache Options):

  • Bitmap dimensions: S1, S2, S3, S4

  • Anchoring dimension: S5

  • Dependent parents in anchoring dimension: Not applicable

Perform this calculation:

Bitmap size in bytes = (S1 * S2 * S3 * S4) / 8
                     = (20 * 20 * 50 * 50) / 8
                     = 125,000 bytes

Number of bitmaps = Single bitmap
                  = 1

Calculator cache = Bitmap size * Number of bitmaps
                 = 125,000 * 1
                 = 125,000 bytes

For Essbase to use a single bitmap for this database with one anchoring dimension, the calculator cache must be 125,000 bytes.

Option 3: Multiple Anchoring Dimensions, Single Bitmap

For this sample calculation, assume the following facts about a database (from Table 175, Calculator Cache Options):

  • Bitmap dimensions: S1, S2, S3

  • Anchoring dimensions: S4, S5

  • Dependent parents in anchoring dimensions: Not applicable

Perform this calculation:

Perform this calculation:

Bitmap size in bytes = (S1 * S2 * S3) / 8
                     = (20 * 20 * 50) / 8
                     = 2,500 bytes

Number of bitmaps = Single bitmap
                  = 1

Calculator cache = Bitmap size * Number of bitmaps
                 = 2,500 * 1
                 = 2,500 bytes

For Essbase to use a single bitmap for this database with multiple anchoring dimensions, the calculator cache must be 2,500 bytes.

Choosing a Calculator Cache Size for a Database

Table 177 shows which calculator cache option Essbase uses, depending on the calculator cache size specified:

Table 177. Selection of Calculator Cache Option Based on Calculator Cache Size

Minimum Size Specified

Option Selected

625,000 bytes

Option 1 (provides optimal performance)

125,000 bytes

Option 2

2,500 bytes[1]

Option 3

1 If you specify a calculator cache size of less than 2,500 bytes, Essbase does not use a calculator cache during the calculation. Calculation performance may be significantly impaired.

You can check which calculator cache option Essbase is able to use on a database by using the SET MSG SUMMARY command in a calculation script. Run the following calculation script on the empty database:

SET MSG SUMMARY;
CALC ALL;

Essbase displays the calculator cache setting in the ESSCMD window or in the application log. See SET MSG SUMMARY and SET MSG DETAIL.

The maximum calculator cache size that you can specify is 200,000,000 bytes. The default is 200,000 bytes. The calculator cache size that you choose depends on available memory and the configuration of the database.

Note:

The sizes of the calculator, index, data file, and data caches usually have a greater effect on performance if the database calculation is based more on aggregations and less on formula calculations.

Sizing the Calculator Cache to Calculate the Database for the First Time

If you are calculating the database for the first time, the size of the calculator cache is particularly significant. If possible, ensure that the calculator cache is large enough for Essbase to use the optimal calculator cache option. See Calculating the Calculator Cache Size.

Changing the Calculator Cache with Calculation Scripts

You can use the default calculator cache size, or you can set the size of the calculator cache within a calculation script. If you set the size from a calculation script, the setting is used only for the duration of the calculation script. See the calculation script SET CACHE command and CALCCACHE configuration setting in the Oracle Essbase Technical Reference.

Sizing Dynamic Calculator Caches

Essbase uses a separate dynamic calculator cache for each open database. The DYNCALCCACHEMAXSIZE setting in the essbase.cfg file specifies the maximum size of each dynamic calculator cache on the server. By default, the maximum size is 20 MB. Essbase allocates area in a dynamic calculator cache for data blocks until the maximum memory area specified by the DYNCALCACHEMAXSIZE setting is allocated. See Changing the Dynamic Calculator Cache Size.

Reviewing Dynamic Calculator Cache Usage

For each database, Essbase writes two messages to the application log for each data retrieval:

[Thu Oct 17 11:37:17 2007]Local/Sample///Info(1007125)
The number of Dynamic Calc Non-Store Members = [7 6 0 0 2 ]
[Thu Oct 17 11:37:17 2007]Local/Sample///Info(1007126)
The number of Dynamic Calc Store Members = [0 0 0 0 0 ]

The first message describes the total time required for the retrieval. If a dynamic calculator cache is used, the second message displays the number of blocks calculated within the data calculator cache (DCC = n) and the number of blocks calculated in general memory (non-DCC = n).

Changing the Dynamic Calculator Cache Size

There are five configuration settings that are relevant to dynamic calculator caches. The optimum values for these dynamic calculator cache settings depend on the memory on the server machine, the configuration of all databases on the server machine, and the nature of user queries.

The following description of each configuration setting includes recommendations for how to determine values for your system. To match your site’s unique requirements, you may need to test and adjust the settings.

  • DYNCALCCACHEMAXSIZE: This setting specifies the maximum size Essbase can allocate to each dynamic calculator cache on the server.

    Recommended setting value = C * S * U.

    • C is the value of the appropriate CALCLOCKBLOCK setting in the essbase.cfg file. (The SET LOCKBLOCK command specifies which CALCLOCKBLOCK setting to use.)

    • S is the size of the largest expanded block across all databases on the machine. To calculate the expanded block size, multiply the number of members (including Dynamic Calc members and dynamic time series members) in each dense dimension together for the number of cells in the block, and multiply the number of cells by the size of each member cell, 8 bytes.

      For example, consider the member count in dense dimensions in Sample.Basic (Label Only members are not counted):

      • 19 (Year, with 12 stored members and 7 Dynamic Calc members, including HTD and QTD)

      • 14 (Measures, with 8 stored members and 6 Dynamic Calc members)

      • 4 (Scenario, with 2 stored members and 2 Dynamic Calc members)

      S = 19 * 14 * 4 cells (8 bytes / cell) = 8512 bytes per block

      This number is shown in the application log as the logical block size.

    • U is the maximum number of expected concurrent users on the database that has the largest number of concurrent users.

      Assigning the value 0 (zero) to DYNCALCACHEMAXSIZE tells Essbase not to use dynamic calculator caches.

      By default, the maximum size for this value is 20 MB (20,971,520 bytes).

  • DYNCALCCACHEWAITFORBLK: If Essbase uses all of the area allocated for a dynamic calculator cache, this setting tells Essbase whether to wait until space becomes available in the cache or to immediately write and calculate the blocks in memory outside the dynamic calculator cache. If the dynamic calculator cache is too small, it is possible for multiple threads to be in queue, each thread waiting to calculate its data blocks.

    Recommended setting value = FALSE (default value).

    Before setting to TRUE, try these alternatives:

    • Add physical memory to the server machine

    • Increase the value of DYNCALCCACHEMAXSIZE, test, and repeat until you verify that you cannot use any more memory for the dynamic calculator cache.

  • DYNCALCCACHEBLKTIMEOUT: If Essbase is to wait for available space in the dynamic calculator cache, this setting defines how long it waits.

    Recommended setting value = WT / B.

    • WT is the maximum tolerable wait time for a query; for example, 5 seconds.

    • B is the total number of logical blocks accessed in the largest query.

      To determine the value of B, check the messages in the application log for the largest number of Dyn.Calc.Cache “Big Block Allocs” for a query, as discussed in Reviewing Dynamic Calculator Cache Usage.

  • DYNCALCCACHEBLKRELEASE: If Essbase has waited the specified time and space still is not available in the dynamic calculator cache, this setting tells Essbase whether to write and calculate the blocks immediately outside the dynamic calculator cache or to create space in the dynamic calculator cache by swapping out blocks and temporarily compressing the swapped blocks in a dynamic calculator cache compressed-block buffer.

    Recommended setting value = FALSE (default value).

    Set to TRUE only if you are experiencing severe memory shortage problems.

  • DYNCALCCACHECOMPRBLKBUFSIZE: If Essbase has waited the specified wait time and the DYNCALCCACHEBLKRELEASE setting is TRUE, this setting is the size of the dynamic calculator cache compressed-block buffer.

    Recommended setting value = (C * S) / 2.

    • C is the value of the current CALCLOCKBLOCK setting in the essbase.cfg file. The SET LOCKBLOCK command specifies which CALCLOCKBLOCK configuration setting is current.

    • S is the size of the largest expanded block across all databases on the machine. Calculate S as described for the DYNCALCCACHEMAXSIZE setting.

Note:

After changing any parameter in the essbase.cfg file, you must stop and restart Essbase Server to use the new values.

For information about specific dynamic calculator cache settings, see the Oracle Essbase Technical Reference.

Fine-Tuning Cache Settings

After using a database at your site with typical data, user access, and standard environment (including servers, network, etc.), observe how Essbase performs. It is difficult to predict optimal cache sizes without testing. You may need to adjust your cache settings.

Understanding Cache Settings

The sizes of the index cache and the data file cache (when direct I/O is used) are the most critical Essbase cache settings. In general, the larger these caches, the less swapping activity occurs; however, setting larger cache sizes does not always improve performance. Read this entire section to understand cache size considerations.

Index Cache

The advantages of a large index cache plateau at some point. When the index cache size equals or exceeds the index size (including all index files on all volumes), performance does not improve. However, to account for future growth of the index, you can set the index cache size larger than the current index size. Because the index cache is filled with index pages, for optimum use of storage, set the size of the index cache to be a multiple of the size of the index page (8 KB). See Index Files for an example of estimating index size.

Data File Cache

If possible, set the data file cache to equal the size of the stored data, which is the combined size of all ess*.pag files. Otherwise, the data file cache should be as large as possible. If you want to account for future growth of stored data, you can set the data file cache size larger than the current size of stored data.

Note:

The data file cache is used only if you are using direct I/O.

Data Cache

The data cache should be about 0.125 times the data file cache. However, certain calculations require a larger data cache size. If many concurrent users are accessing different data blocks, this cache should be larger.

In general, if you must choose between allocating memory to the data file cache or allocating it to the data cache, choose the data file cache if you are using direct I/O. If upgrading from a previous version of Essbase, see the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

Checking Cache Hit Ratios

Every cache has a “hit ratio”: the percentage of time that a requested piece of information is available in it. You can check the hit ratio of the index cache, the data cache, and the data file cache to determine whether to increase the cache size.

  To check cache hit ratios, see “Checking Cache Hit Ratios” in Oracle Essbase Administration Services Online Help.

  • The cache hit ratio indicates the percentage of time that a requested piece of information is already in the cache. A higher hit ratio indicates that the data is in the cache more often. This improves performance, because the requested data need not be retrieved from disk for the next process. A hit ratio of 1.0 indicates that every time data is requested, it is found in the cache. This is the maximum performance possible from a cache setting.

  • The Hit Ratio on Index Cache setting indicates the Essbase kernel success rate in locating index information in the index cache without having to retrieve another index page from disk.

  • The Hit Ratio on Data File Cache setting indicates the Essbase kernel success rate in locating data file pages in the data file cache without having to retrieve the data file from disk.

  • The Hit Ratio on Data Cache setting indicates the Essbase success rate in locating data blocks in the data cache without having to retrieve the block from the data file cache.

  • Check memory allocation. Add smaller amounts of memory at a time , if needed, because a smaller increment may have the same benefit as a large one. Large, incremental allocations of memory usually result in very little gain in the hit ratio.

Checking Performance

You can check cache statistics for a database by using the query database MaxL statement with the performance statistics grammar. See Monitoring Performance.

Running Test Calculations

Because calculations are the most processor-intensive operations on a Essbase database, you should run test calculations and examine how various cache sizes affect memory use on Essbase Server.