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 104. Essbase Caches

Cache

Description

Index cache

A buffer in memory that holds index pages. How many index pages are in memory at one time 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 at one time 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 105 shows default and recommended settings for the index cache:

    Table 105. 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 at one time 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 106 shows default and recommended settings for the data file cache:

      Table 106. 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 at one time 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 107 shows default and recommended settings for the data cache:

        Table 107. 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 for the calculation:

          Table 108. Options for calculator cache

          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 108, Options for calculator cache .

          Calculator cache

          =

          Bitmap size in bytes * Number of bitmaps

          Bitmap size in bytes

          =

          Max ((member combinations on the bitmap dimensions / 8), 4)

          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):

          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 108, Options for calculator cache ):

          • 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 108, Options for calculator cache ):

          • 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 108, Options for calculator cache ):

          • Bitmap dimensions: S1, S2, S3

          • Anchoring dimensions: S4, S5

          • Dependent parents in anchoring dimensions: Not applicable

          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

          The following table shows which calculator cache option Essbase uses, depending on the calculator cache size specified:

          Minimum Size Specified

          Option Selected

          625,000 bytes

          Option 1 (provides optimal performance)

          125,000 bytes

          Option 2

          2,500 bytes

          Option 3

          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 the memory is available 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

          Five configuration file settings 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.

          Table 109 describes each setting and 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.

          Table 109. essbase.cfg Settings for Dynamic Calculator Caches

          DYNCALCCACHEMAXSIZE

          Description

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

          Recommended Setting

          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:

            - 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)

            Note: Label Only members are not counted.

            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

          Description

          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

          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

          Description

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

          Recommended Setting

          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

          Description

          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

          Recommended setting value = FALSE (default value).

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

          DYNCALCCACHECOMPRBLKBUFSIZE

          Description

          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

          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.