Essbase uses five memory caches to coordinate memory usage:
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 (
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).
.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.
Essbase provides default size settings for each cache; you can adjust the sizes as needed for each
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.
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,
Essbase and enabling cache memory locking. This script sets the server to run in Superuser mode
so that it can lock memory.
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.
.
For information about changing the I/O access mode for a database, or about changing the
default for all newly created databases, 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.
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 (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.
.pag files) in memory if you are using direct I/O. If you are
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.
In certain cases, you might need to increase the data cache when running concurrent calculations;
Sizing the Calculator Cache
Calculator Cache Options
(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.
Note:
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
“Fine-Tuning Cache Settings” on.When running Essbase on 64-bit platforms, optimal data cache and data file cache settings
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.
Understanding the Calculator Cache Bitmap
You specify the SET CACHE ALL command in a calculation script.
For the calculator cache, Essbase separates sparse dimensions in the database into two groups:
l
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.
Bitmap dimensions: The sparse dimensions from the database outline that Essbase fits into
l
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
Anchoring dimensions: The remaining one or more sparse dimensions in the database
836
Optimizing Essbase Caches
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:
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:
.
l
A single bitmap is used to track child blocks.
l
Multiple bitmaps have these properties:
A single bitmap uses the least memory but is less efficient than multiple bitmaps.
l
Multiple bitmaps are used, one to track child blocks and one to track parent blocks.
l
performance improvement is particularly high when you are calculating the database for
the first time.
Multiple bitmaps use more memory but are faster than using a single bitmap. The
l
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
Calculator Cache Options
1 Single anchoring dimension, multiple bitmaps 1
2 Single anchoring dimension, single bitmap 2
Sizing Caches
Option Method Performance Rating
837
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.
Caution!
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.
“Calculator.If you are calculating the database for the first time, the size of the calculator cache
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
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:
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
The minimum bitmap size is 4 bytes. If (member combinations on the 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
838
Optimizing Essbase Caches
Sparse Dimension Number of Members Dependent Parents
S5 200 3
Use this sample database for the following sample calculations:
l
“Option 1: Single Anchoring Dimension, Multiple Bitmaps” on page 839
l
“Option 2: Single Anchoring Dimension, Single Bitmap” on page 839
l
“Option 3: Multiple Anchoring Dimensions, Single Bitmap” on page 840
Option 1: Single Anchoring Dimension, Multiple Bitmaps
For this sample calculation, assume the following facts about a database (from
837
Table 175 on page):
l
Bitmap dimensions: S1, S2, S3, S4
l
Anchoring dimension: S5
l
Perform this calculation:
Dependent parents in anchoring dimension: 3
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
837
Table 175 on page):
l
Bitmap dimensions: S1, S2, S3, S4
l
Anchoring dimension: S5
l
Perform this calculation:
Dependent parents in anchoring dimension: Not applicable
Bitmap size in bytes = (S1 * S2 * S3 * S4) / 8
= (20 * 20 * 50 * 50) / 8
Sizing Caches
839
=
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
837
Table 175 on page):
l
Bitmap dimensions: S1, S2, S3
l
Anchoring dimensions: S4, S5
l
Perform this calculation:
Perform this calculation:
Dependent parents in anchoring dimensions: Not applicable
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 cacheSelection 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
840
Optimizing Essbase Caches
Minimum Size Specified Option Selected
2,500 bytes
1 Option 3
1
performance may be significantly impaired.
If you specify a calculator cache size of less than 2,500 bytes, Essbase does not use a calculator cache during the calculation. Calculation
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
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.
“SET MSG SUMMARY and SET MSG DETAIL” on page 870.
Note:
on performance if the database calculation is based more on aggregations and less on
formula calculations.
The sizes of the calculator, index, data file, and data caches usually have a greater effect
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” on page 838.
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
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
Calculator Cache Size” on page 842
essbase.cfg file specifies the maximum size of“Changing the Dynamic.
Sizing Caches
841
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.
l
to each dynamic calculator cache on the server.
Recommended setting value = C * S * U.
DYNCALCCACHEMAXSIZE: This setting specifies the maximum size Essbase can allocate
m
(The SET LOCKBLOCK command specifies which CALCLOCKBLOCK setting to use.)
C is the value of the appropriate CALCLOCKBLOCK setting in the essbase.cfg file.
m
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):
S is the size of the largest expanded block across all databases on the machine. To
o
and QTD)
19 (Year, with 12 stored members and 7 Dynamic Calc members, including HTD
o
14 (Measures, with 8 stored members and 6 Dynamic Calc members)
o
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.
4 (Scenario, with 2 stored members and 2 Dynamic Calc members)
m
largest number of concurrent users.
U is the maximum number of expected concurrent users on the database that has the
842
Optimizing Essbase Caches
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).
l
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:
DYNCALCCACHEWAITFORBLK: If Essbase uses all of the area allocated for a dynamic
m
Add physical memory to the server machine
m
you cannot use any more memory for the dynamic calculator cache.
Increase the value of DYNCALCCACHEMAXSIZE, test, and repeat until you verify that
l
calculator cache, this setting defines how long it waits.
Recommended setting value = WT / B.
DYNCALCCACHEBLKTIMEOUT: If Essbase is to wait for available space in the dynamic
m
WT is the maximum tolerable wait time for a query; for example, 5 seconds.
m
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
Dynamic Calculator Cache Usage” on page 437
B is the total number of logical blocks accessed in the largest query.“Reviewing.
l
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.
DYNCALCCACHEBLKRELEASE: If Essbase has waited the specified time and space still is
l
the DYNCALCCACHEBLKRELEASE setting is TRUE, this setting is the size of the dynamic
calculator cache compressed-block buffer.
Recommended setting value = (C * S) / 2.
DYNCALCCACHECOMPRBLKBUFSIZE: If Essbase has waited the specified wait time and
m
SET LOCKBLOCK command specifies which CALCLOCKBLOCK configuration
setting is current.
C is the value of the current CALCLOCKBLOCK setting in the essbase.cfg file. The
m
S as described for the DYNCALCCACHEMAXSIZE setting.
S is the size of the largest expanded block across all databases on the machine. Calculate
Note:
Server to use the new values.
After changing any parameter in the essbase.cfg file, you must stop and restart Essbase
Sizing Caches
843
For information about specific dynamic calculator cache settings, see the
Reference
Oracle Essbase Technical.
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” on page 1072 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
to account for future growth of stored data, you can set the data file cache size larger than the
current size of stored data.
ess*.pag files. Otherwise, the data file cache should be as large as possible. If you want
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
Installation and Configuration Guide
Oracle Hyperion Enterprise Performance Management System.
844
Optimizing Essbase Caches
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.
รค
Services Online Help
To check cache hit ratios, see “Checking Cache Hit Ratios” in Oracle Essbase Administration.
l
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 cache hit ratio indicates the percentage of time that a requested piece of information is
l
index information in the index cache without having to retrieve another index page from
disk.
The Hit Ratio on Index Cache setting indicates the Essbase kernel success rate in locating
l
data file pages in the data file cache without having to retrieve the data file from disk.
The Hit Ratio on Data File Cache setting indicates the Essbase kernel success rate in locating
l
in the data cache without having to retrieve the block from the data file cache.
The Hit Ratio on Data Cache setting indicates the Essbase success rate in locating data blocks
l
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.
Check memory allocation. Add smaller amounts of memory at a time , if needed, because
Checking Performance
You can check cache statistics for a database by using the
the
query database MaxL statement withperformance statistics grammar.
Option Method Performance Rating
The number of bitmaps used is determined by the maximum number of dependent parents
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:
Understanding Essbase Caches
No comments:
Post a Comment