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. 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

Caveats

• • Default value (1 MB) is fairly small. Many queries will hit this limit.