Essbase Index Cache, Data File Cache and Data Cache Sizing Calculator
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.
Your numbers
Your results
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.
Data cache and total cache vs .pag size
Sweeps Total size of all .pag files from 1000 to 100000, holding your other inputs fixed.
Want your cache settings reviewed?
Send us database statistics and hit ratios after a normal calc cycle and we will suggest cache and calc settings.
Optional. The tool and every export work without this. No spam.
How to use it
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.
- 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
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
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.
- Cache settings apply when the database next starts.
- Parallel calculation increases memory use per thread; leave headroom.
Frequently Asked Questions
What should the Essbase index cache size be?
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?
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?
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?
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.
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.
Related Tools
Oracle Essbase BSO Block Size and Database Size Calculator
Enter stored member counts for dense and sparse dimensions and get block size, block counts and estimated .pag and .ind sizes for a block storage cube.
OraclePeopleSoft Application Engine Trace Flag Calculator (-TRACE value)
Turn trace options on and off to get the -TRACE, -TOOLSTRACESQL and -TOOLSTRACEPC values for an Application Engine run, as documented in PeopleBooks.
More Oracle tools
See all- Oracle Essbase Data Load and Dimension Build Error Codes Reference
- Oracle Essbase Calculation Functions Reference (@ Functions)
- Oracle EBS Discrete Job Statuses Reference
- Oracle EBS Inventory Transaction Types Reference
- Oracle EBS Item Attribute Template Worksheet
- Oracle EBS MRP Exception Messages Reference
- Oracle EBS BOM Interface Load Worksheet
- Oracle EBS Inventory Period Close Checklist
- Oracle EBS WIP Accounting Class Reference
Keep exploring
Free tools
- Hyperion and Essbase AI Assistant Sizing and Value CalculatorEstimate peak load, GPUs, index size, hours saved and payback for an on-prem AI assistant on Hyperion and Essbase.
- Oracle Essbase BSO Block Size and Database Size CalculatorEnter stored member counts for dense and sparse dimensions and get block size, block counts and estimated .pag and .ind sizes for a block storage cube.
- Hyperion and Essbase Private AI Layer Blueprint GeneratorFill in your Hyperion and Essbase version, database, hosting and users to get a shareable architecture plan for a private, on-prem AI assistant.