Cache Management

Introduction

Cache is an essential feature for obtaining an optimized report and faster response time. However, when managed in an improper way, it will lead to stale data appearing in reports.  So this chapter explains the ways to monitor and manage cache for eliminating stale data and for improving performance.

1. Business Challenge

• Decision support systems running the report require a larger amount of database processing.

• Frequent trips to back-end databases to satisfy query results can result in increased query response time and poor performance.

2. Business Solution

The solution to the drawback is to employ OBI Server Query Cache

Oracle BI Server can be configured to maintain a disk-based cache of query result sets (query cache):

• The server can save the results of a query in cache files and then reuse them later when a similar query is requested.

• For subsequent times that a similar query is executed, the results are satisfied by the cache and not the database since it is processed only once for the initial time a query is executed.

• Oracle BI Server can configure Oracle BI Server to maintain a local, disk-based cache of query sets (the query cache).

• The query cache enables Oracle BI Server to satisfy many subsequent query requests without having to access back-end databases.

• This reduction in communication costs can dramatically decrease query response time.

3. Advantages of Caching

• The unnecessary database processing can be eliminated since the pre-computed results are stored in a local cache.

• The query performance is improved by fulfilling a query from the cache rather than searching through the database.

• The network resources are conserved because the intermediate results do not have to come over the network to Oracle BI Server.

4. Costs of Caching

1. Disk Space

The query cache requires dedicated disk space. The amount of space depends on the query volume, the size of the query results sets, and the amount of disk space to be allocated to the cache. A high-reliability disk system should be exclusively used for caching in order to increase the performance.

2. Administrative Tasks

There are a few administrative tasks associated with caching that result in caching costs. You need to set the cache persistence time for each physical table appropriately, knowing how often data in that table is updated. When the update frequency changes, you need to keep track of when changes occur and purge the cache manually when necessary.

3. Keeping the Cache Up to Date

If the cache entries are not purged when the data in the underlying database changes, queries can potentially return results that are out of date.

For applications in which data is updated yearly or quarterly, it may be acceptable to keep stale data in the cache.

For applications in which data is updated frequently, it may be necessary to purge the cache more often. It is also possible to purge the entire cache as part of the extraction, transformation, and loading (ETL) process for rebuilding the data mart, making sure that there is no stale data on the cache.

5. Architecture of Query Cache

The query cache consists of:

1. Cache storage space

2. Cache metadata

3. Cache detection

A query cache is a facility that stores the results from queries. If a query is fulfilled by the results stored in the cache, it is called a cache hit.

In the architecture depicted above, the process of accessing the cache metadata occurs very quickly. If the metadata shows a cache hit, the bulk of the query processing is eliminated, and the results are immediately returned to the user.

6. Monitor and Manage Cache

Cache files always produce the same results, even after a database has been updated.

Issues with retaining cache files like not purging outdated caches can potentially return inaccurate results over time and consume disk space. Therefore, you need a cache management strategy to manage changes to the underlying databases and to monitor cache entries.

The choice of a cache management strategy depends on the volatility of the data in the underlying databases and the predictability of the changes that cause this volatility.

It also depends on the number and types of queries that comprise your cache, as well as the usage those queries receive.

Note: Cache won’t refresh if we refresh the database. It depends on the user queries. If the user make a query, it will hit the database first and not the cache.

Selvi

Add comment