# Essbase Index Cache, Data File Cache and Data Cache Sizing Calculator

> Source: https://netray.co/tools/essbase-data-cache-index-cache-sizing-calculator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter the total size of your .ind and .pag files and available memory to get recommended index cache, data file cache and data cache settings for a BSO database.

## Category

oracle

## Erp

Oracle Hyperion / Essbase

## Audience

- Essbase administrator
- EPM infrastructure engineer
- Hyperion consultant

## Tool

**Kind:** calculator

**Inputs:**

#### Total size of all .ind files

**Key:** ind_mb

**Type:** number

**Default:** 400

**Min:** 0

**Step:** 10

**Unit:** MB

#### Total size of all .pag files

**Key:** pag_mb

**Type:** number

**Default:** 24000

**Min:** 0

**Step:** 100

**Unit:** MB

#### Expanded block size

**Key:** block_kb

**Type:** number

**Default:** 53

**Min:** 1

**Step:** 1

**Unit:** KB

#### I/O access mode

**Key:** io

**Type:** select

**Default:** 1

**Options:**

##### Buffered I/O (default)

**Value:** 1

##### Direct I/O

**Value:** 2

#### Memory available for this database

**Key:** ram_gb

**Type:** number

**Default:** 16

**Min:** 1

**Step:** 1

**Unit:** GB

**Outputs:**

#### Available memory (MB)

**Key:** ram_mb

**Expr:** ram_gb * 1024

**Format:** integer

#### Index cache (MB)

**Key:** index_cache

**Expr:** min(ind_mb, ram_mb)

**Format:** integer

**Primary:** true

**Description:** Guideline: combined size of all .ind files, if memory allows.

#### Data file cache (MB, direct I/O only)

**Key:** data_file_cache

**Expr:** if(eq(io, 2), pag_mb, 0)

**Format:** integer

**Description:** Guideline: combined size of all .pag files, if memory allows. Not used with buffered I/O.

#### Data cache (MB)

**Key:** data_cache

**Expr:** 0.125 * pag_mb

**Format:** integer

**Primary:** true

**Description:** Guideline: 0.125 x the data file cache size, i.e. 0.125 x combined .pag size.

#### Expanded blocks that fit in data cache

**Key:** blocks_in_cache

**Expr:** floor(data_cache * 1024 / block_kb)

**Format:** integer

#### Total cache memory (MB)

**Key:** total_mb

**Expr:** index_cache + data_file_cache + data_cache

**Format:** integer

#### Memory headroom (MB, negative = over budget)

**Key:** headroom_mb

**Expr:** ram_mb - total_mb

**Format:** integer

**Chart:**

#### Data cache and total cache vs .pag size

**Type:** line

**X:** pag_mb

**From:** 1000

**To:** 100000

**Steps:** 30

**Series:**

- data_cache
- total_mb

**Disclaimer:** Guidelines from the Essbase Database Administrator's Guide, Optimizing Essbase Caches (index cache = .ind size, data file cache = .pag size for direct I/O, data cache = 0.125 x data file cache). They are starting points; tune with cache hit ratios on your workload.

## Content

**Intro:** This calculator applies Oracle's documented starting points for Essbase BSO cache sizes: index cache, data file cache and data cache. It uses the combined size of your .ind and .pag files and checks the total against the memory you can give the database.

**Sections:**

#### How to use it

**Body:** Add up the sizes of all essn.ind files and all essn.pag files for the database (they live in the database directory under the application, and are also shown in database statistics). Enter the expanded block size, the I/O access mode and the memory you can dedicate to this database, taking other databases and the OS into account. The results are starting values; after a typical calc and retrieval workload, check the hit ratios in database statistics and adjust.

**Bullets:**

- Index cache hit ratio near 1.0 means the whole index fits.
- Data cache hit ratio is usually lower; raise it if calcs thrash.
- Negative headroom means scale the guideline values down.

#### The guidelines behind the formulas

**Body:** The DBAG recommends an index cache equal to the combined size of all index files when memory allows, so the whole index stays in memory. For direct I/O, the data file cache holds compressed blocks and is recommended at the combined size of the .pag files, again memory permitting. The data cache holds expanded blocks being calculated or retrieved, and the guideline is 0.125 x the data file cache size. With buffered I/O, the default, the data file cache is not used and the OS file system cache does that job, which is why this calculator sets it to zero for buffered I/O.

#### Setting the caches with MaxL

**Body:** Set the values with MaxL and restart the database so they take effect, for example alter database Sample.Basic set index_cache_size 400mb; and alter database Sample.Basic set data_cache_size 3000mb; and, for direct I/O only, alter database Sample.Basic set data_file_cache_size 24000mb;. The same settings are available in database properties in Administration Services or the Essbase web interface. Calculator cache, used by calcs on sparse dimensions, is set separately through CALCCACHEHIGH, CALCCACHEDEFAULT and CALCCACHELOW in essbase.cfg and is not sized here.

**Bullets:**

- Cache settings apply when the database next starts.
- Parallel calculation increases memory use per thread; leave headroom.

**Cta:** If you want admins and planners to ask questions of Essbase statistics and data in plain English on your own servers, we can set up a private AI layer for that.

**Faq:**

#### What should the Essbase index cache size be?

**Answer:** The guideline is the combined size of all .ind files for the database, so the entire index stays in memory. If memory is short, give it as much as possible; index cache misses are expensive because every block lookup goes through the index. Check the index cache hit ratio after a normal workload.

#### How big should the Essbase data cache be?

**Answer:** The DBAG starting point is 0.125 x the data file cache size, which for most sizing work means 0.125 x the combined .pag file size. Increase it when calcs touch many blocks at once or use parallel calculation, and watch the data cache hit ratio in database statistics.

#### Is the data file cache used with buffered I/O?

**Answer:** No. The data file cache is used only with direct I/O. With buffered I/O, which is the default, Essbase relies on the operating system file system cache for compressed blocks, so leave memory for the OS cache instead of setting a large data file cache.

#### How do I change Essbase cache sizes with MaxL?

**Answer:** Use alter database App.Db set index_cache_size n; alter database App.Db set data_cache_size n; and for direct I/O alter database App.Db set data_file_cache_size n; with sizes such as 400mb. Restart the database for the new values to take effect, then verify them in database properties.

## Search terms

essbase admin guide, essbase index cache size, essbase data cache size, essbase technical reference, essbase block analysis report, essbase cache settings maxl, essbase data file cache

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json