OBIEE Caching Configuration | The Big Data - Business Intelligence by Sandeep Venu

Enter key word

OBIEE Caching Configuration

OBIEE Caching Best Practices

One of my recent discussions with a colleague on the caching strategy for OBIEE resulted in the following Best practices, Oracle BI Server is an Intelligent Query Engine that stores database hits in a cache file, This cache file is stored on the BI server.
OBIEE Architectural Best Practice feature is to implement the caching mechanism by using the following methodology where in the configuration tags can be set in optimal fashion as follows:
  • Enable: turns caching on/off
  • Data_Storage_Paths: defines location to store result files
  • Metadata_File: defines location for cache metadata file
  • Replace_Algorithm: for discarding entries if cache full
  • Buffer_Pool_Size: buffer for caching metadata file
  • Max_Rows_Per_Cache_Entry: upper limit on rows in result
  • Max_Cache_Entry_Size: upper limit on size (#rows*#bytes/row)
  • Max_Cache_Entries: upper limit on #of cached queries
The Following is an in detail Architectural configuration changes that can be implemented for OBIEE Caching
  1. Parameter: Enable
  • Turns caching on/off
Best Practice
Set to YES if you want caching
  1. Parameter: Data_Storage_Paths
  • Defines the directory or directories to store cached result files
  • Provide location and capacity
    • DATA_STORAGE_PATHS = “d:\OracleBIData\nQSCache” 500 MB
  • Least-recently-used cache is purged if full capacity
Best practice
    • use dedicated drive(s): performance and reliability
    • use local disk (not a file share). (Not enforced)
    • capacity should be significantly larger than value of Max_Cache_Entry_Size
Caveats to be kept in mind
    • Disk space must exist (or bad things will happen)
    • Capacity of each location must not exceed 4 GB (2 GB before 7.7)
  1. Parameter: Replace_Algorithm
Algorithm used to purge cache entries when the cache is full
“Full” is either:
    1. Max_Cache_Entries have been created
    2. Less than Max_Cache_Entry_Size space is available
Removes cache entry that has not been accessed for longest time – not necessarily the oldest “created” cache item
Only choice is LRU (least-recently-used)
  1. Parameter: Buffer_Pool_Size
  • Defines the amount of memory for caching the cache metadata file.
  • Parameter does not affect correctness/behavior of cache – purely a performance setting
Best practice
  • Don’t change the default value. No/limited performance gains possible.
  1. Parameter: Max_Rows_Per_Cache_Entry
  • Defines upper bound on number of rows in a cached result set
  • Prevent large or “runaway” queries from consuming too much cache
  • Query will run to completion, but if limit exceeded result will not be added to cache – event is not logged
  • Set value to 0 if no limit is desired
  • Very large cache files are inefficient
    • stored in single file on disk
    • No indexes – full sequential scan to access
Best practice
    • Define a non-zero value (less than 1,000,000 if possible)
    • Max_Cache_Entry_Size is best place to define space limit
  1. Parameter: Max_Cache_Entry_Size
  • Defines limit on size (#of bytes) of a cache entry
  • Used to prevent large cache entries from being created. Query will not be cached if exceeds this limit. No logging of exceeding limit.
  • Size: #of rows times #of bytes/row
  • #of bytes per row calculation:
    • Unicode expansion (2x or 4x multiplier for char and varchar columns)
    • Column alignment overheads
    • Null value representation overhead
  • Cache is purged until Max_Cache_Entry_Size bytes are available
Best practice
    • Set value to at most 10% of cache capacity (of smallest cache directory)
    • More effective limit than Max_Rows_Per_Cache_Entry or Max_Cache_Entries
    • Default value (1 MB) is fairly small. Many queries will hit this limit.

About Sandeep Venu

    Blogger Comment
    Facebook Comment


Top Links

Upgrading BI Publisher

Add Google Search


Change Admin Password

Best Practices


Host Files



Bug Fixes

Setting Default

OBIEE Installation failed

OBIEE Installation

Oracle Databse

Oracle In Linux

Loopback Adapter

Weblogic Failed

Yum Commands

Weblogic Admin

Linux Static IP


Admin Paswword


Weblogic Starting Error

Localizing BI

Physical Schemas

Multimedia Dashboard

Video in OBIEE

Variables in OBIEE

Ago 30 Days

Email Link

ODI Installation

AGO Function

Sort Pivote table


Reset Sys password

Date Calculation

Add Row in Report

Increase Row limit

OBIEE with Essbase

Reset Weblogic

BIP Login Error

MDS and BI Platform

ORA 28001 Error

ORA 12560 TNS Protocal Error

ORA 12154 TNS Error


Pre Requsite

AIX Commands