Database Reference
In-Depth Information
compressed blocks in the PAg file. It is feasible to place the entire database into mem-
ory by setting the data file cache to the PAg size. Should you choose this route, oracle
recommends:
1. Direct I/o should be set at the server level.
2. The Essbase server should be on a dedicated box and not be in a shared
resources environment.
3. memory must be carefully monitored.
4. restart the server regularly. Do not exceed server memory.
4.6.4
Calculator Cache
Calculator cache is used to track the children of parent members during calculations.
If the database is small enough and multiple bitmaps are possible, the cache also tracks
the parents. In short, the cache is used most commonly during aggregations. The DBAg
has an excellent write up that covers calculator cache, so I am not going to repeat it here.
What the documentation does not say is that calculation cache operates differently than
index and data caches. While the index and data caches are shared by all of the users of
a given database, calculator cache is not shared. In fact each calculation creates its own
calculator cache for each processor thread. Please go back and read that last sentence
again. It means if CALCCAChE = 199m and a single calculation script logs the message
“Calculating in parallel with [5] threads,” then the Essbase kernel could use as much as
1 gb of memory for the calculator cache alone. Considering that each script has its own
calculator cache, memory can be an issue during periods of high concurrency.
hence, if 199m is not the right answer, what is? That is easy, we test. For starters, when
the database is small, use the guidelines in the documentation and try to achieve mul-
tiple bitmaps. An hourglass organization may be required for multiple bitmaps. When
the database is large, as mine always seem to be, only single bitmap is available. In this
situation, oracle offers one suggestion: Do not set the calculator cache higher than 50
mb. This is because the calculator cache has no index. The search method is sequential.
The thinking is that at some point it takes longer to search than to retrieve. Somewhere
there must be a database that needs 199m calculator cache, but I have never seen one.
optimize calculator cache after the index and data caches have been tuned. Set the
calculator cache in the Essbase.cfg file to 20m, 50m, and 100m for Low, Default, and
high, respectively. recycle the Essbase server to reset the values. Clear the upper blocks
of the database and then run a script that aggregates the sparse dimensions first with
CALCCAChE set to Low. repeat the test for 50m and 100m. If there is not a measure-
able improvement of say 5 to 10%, then stay with the lower value. only set the calcula-
tor cache to 199m if there is a provable performance benefit. In general, scripts that
aggregate large dimensions require larger calculator caches. Scripts that utilize multiple
processors have smaller calculator cache needs because each thread has its own cache.
4.6.5
Dynamic Calc Cache
This cache is used for reporting. The formula is pretty straight forward, so do the math,
but do not exceed your operating system resources. DynCALCCAChEmAxSIzE is
the name of the Essbase.cfg file setting and the default value is 20 mb. The calculation
using the variables in table 4.1 would be:
Dynamic Calc Cache in bytes = C * S * U
