Strategies for Using the Cache

One of the main advantages of query caching is to improve apparent query performance.

Query caching might be valuable to seed the cache during off hours by running queries and caching their results. A good seeding strategy requires that you know when cache hits occur.

To seed the cache for all users, then you might seed the cache with the following query:

SELECT User, SRs

After seeding the cache using SELECT User, SRs, the following queries are cache hits:

SELECT User, SRs WHERE user = valueof(nq_SESSION.USER) (and the user was USER1)
SELECT User, SRs WHERE user = valueof(nq_SESSION.USER) (and the user was USER2)
SELECT User, SRs WHERE user = valueof(nq_SESSION.USER) (and the user was USER3)

This section contains the following topics:

Run a Suite of Queries to Populate the Cache

To maximize potential cache hits, one strategy is to run a suite of queries to populate the cache.

The following are some recommendations for the types of queries to use when creating a suite of queries with which to seed the cache.

Common prebuilt queries. Queries that are commonly run, particularly ones that are expensive to process, are excellent cache seeding queries. Queries whose results are embedded in dashboards are good examples of common queries.
SELECT lists with no expressions. Eliminating expressions on SELECT list columns expands the possibility for cache hits. A cached column with an expression can only answer a new query with the same expression; a cached column with no expressions can answer a request for that column with any expression. For example, a cached request such as:
```
SELECT QUANTITY, REVENUE...
```
can answer a new query such as:
```
SELECT QUANTITY/REVENUE... 
```
but not the reverse.
No WHERE clause. If there is no WHERE clause in a cached result, then it can be used to answer queries that satisfy the cache hit rules for the select list with any WHERE clause that includes columns in the projection list.

In general, the best queries to seed cache with are queries that heavily consume database processing resources and that are likely to be reissued. Be careful not to seed the cache with simple queries that return many rows. These queries (for example, SELECT * FROM PRODUCTS, where PRODUCTS maps directly to a single database table) require very little database processing. Their expense is network and disk overhead, which are factors that caching doesn't alleviate.

When Oracle BI Server refreshes semantic model variables, it examines business models to determine if they reference those semantic model variables. If they do, Oracle BI Server purges all cache for those business models. See How Repository Changes Affect the Query Cache.

Use Agents to Seed the Oracle BI Server Query Cache

You can configure agents to seed the Oracle BI Server query cache.

Seeding the cache can improve response times for users when they run analyses or view analyses that are embedded on their dashboards. You can accomplish this by scheduling agents to run requests that refresh this data.

Log in to Oracle Analytics Server and select New, then select Agent.
On the General tab, select Recipient for the Run As option. Personalized cache seeding uses the data visibility of each recipient to customize agent delivery content for each recipient.
On the Schedule tab, specify when you want the cache to be seeded.
Optional: Select Condition and create or select a conditional request. For example, you might have a business model that determines when the ETL process is complete. You could use a report based on this business model to be the conditional trigger for the cache seed to begin.
On the Delivery Content tab, select an individual request or an entire dashboard page for which you want to seed the cache. Selecting a dashboard page can save time.
On the Recipients tab, select individual users or groups to be the recipients.
On the Destinations tab, clear all user destinations and select Oracle Analytics Server Cache.
Save the agent by selecting the Save button in the upper-right corner.

The only difference between cache seeding agents and other agents is that they clear the previous cache automatically and don't appear on the dashboard as alerts.

Note:

Cache seeding agents only purge exact match queries, so stale data might still exist. Ensure that the caching strategy always include cache purging, because agent queries don't address ad-hoc queries or drills.