Disk
Space
The query cache
requires dedicated disk space. How much space depends on the query volume, the
size of the query result sets, and how much disk space you choose to allocate
to the cache. For performance purposes, a disk should be used exclusively for
caching, and it should be a high performance, high reliability type of disk
system.
Administrative Tasks
There are a few
administrative tasks associated with caching. You need to set the cache
persistence time for each physical table appropriately, knowing how often data
in that table is updated. When the frequency of the update varies, you need to
keep track of when changes occur and purge the cache manually when necessary.
You can also create a cache event polling table and modify applications to
update the polling table when changes to the databases occur, making the system
event-driven.
The Oracle BI
Server also provides ODBC-extension functions for purging cache entries
programmatically.
You can write your own scripts to call these functions at the appropriate
times.
The ODBC
functions are :
SAPurgeCacheByQuery()
SAPurgeCacheByTable()
SAPurgeCacheByDatabase()
SAPurgeAllCache()
Keeping the
Cache Up To Date
If the cache
entries are not purged when the data in the underlying databases changes,
queries
can potentially
return results that are out of date. You need to evaluate whether this is
acceptable. It might be acceptable to allow the cache to contain some stale
data. You need to decide what level of stale data is acceptable and then set up
(and follow) a set of rules to reflect those levels.
For example,
suppose your application analyzes corporate data from a large conglomerate, and
you are performing yearly summaries of the different divisions in the company.
New data is not going to materially affect your queries because the new data
will only affect next year’s summaries. In this case, the tradeoffs for
deciding whether to purge the cache might favor leaving the entries in the cache.
Suppose,
however, that your databases are updated three times a day and you are
performing
queries on the
current day’s activities. In this case, you will need to purge the cache much
more
often, or
perhaps consider not using it at all.
Whatever your
situation, you need to evaluate what is acceptable as far as having noncurrent
information
returned to the users.
