1. Configuration Reference for Oracle Essbase
  2. Essbase Configuration Settings
  3. Config Settings List
  4. MAXFORMULACACHESIZE

MAXFORMULACACHESIZE

The MAXFORMULACACHESIZE configuration setting enables you to set the maximum size of the formula cache Essbase should make available for calculating members with formulas. You can use this setting to limit how much memory may be used by a single query.

MAXFORMULACACHESIZE configuration applies to aggregate storage (ASO) databases and block storage (BSO) databases in hybrid mode.

Syntax

MAXFORMULACACHESIZE [appname [dbname]] n

  • appname—Optional. To set the cache size maximum for a specific application, specifiy the application name.

  • dbname—Optional. To set the cache size maximum for a specific database (cube), specifiy the cube name. If dbname is specified, appname must also be specified.

  • n—An integer that specifies the number of kilobytes (KB) to set as the maximum cache size to be made available for calculating members with formulas. The default is 1024 KB for aggregate storage cubes, and 102400 KB for block storage cubes in hybrid mode.

Description

If the amount of cache Essbase sets aside for calculating outline members is insufficient, Essbase switches its formula cache mechanism to allocate only existing and temporary cache values. If this occurs and query tracing is enabled, the following message is logged in query_trace.txt: 

Max formula cache size overflow. Calculation cache will use map structure for values. If query performance is insufficient try to extend MAXFORMULACACHESIZE over this limit: n

The following guidelines can help you determine what value to use for n:

  1. Identify which queried dimensions are represented by dynamic members.

  2. Multiply the sizes of those dimensions to get a number of members.

  3. Multiply the number of members by 8 to get the recommended n value (not more than 4G).

For example, the default formula cache size of 1024 allows 1024/8=128 members to be in the cache.

Notes

  • This setting is only relevant if your query references at least one dynamic member with a formula, or if your MDX query has a calculated member in the WITH section.

  • This cache is allocated per calculation thread. Concurrent MDX requests can be allocated multiple cache objects, each with a maximum size specified in MAXFORMULACACHESIZE.

  • The entire specified amount is not used unless needed.

  • The memory is released after the query completes.

Example

MAXFORMULACACHESIZE 2048

Sets the formula cache size maximum to 2048 KB for every application and database.

See Also

QUERYTRACE

TRACE_REPORT