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:

About Cache Hits

When caching is enabled, each query is evaluated to determine whether it qualifies for a cache hit.

A cache hit means that the server was able to use cache to answer the query and did not go to the database at all. The Oracle BI Server can use the query cache to answer queries at the same or higher level of aggregation.

Many factors determine whether cache is hit. The table below describes these factors.

Factor or Rule	Description
A subset of columns in the `SELECT` list must match	All of the columns in the `SELECT` list of a new query have to exist in the cached query to qualify for a cache hit, or they must be able to be calculated from the columns in the query. This rule describes the minimum requirement to hit the cache, but meeting this rule does not guarantee a cache hit. The other rules listed in this table also apply.
Columns in the `SELECT` list can be composed of expressions on the columns of the cached queries	The Oracle BI Server can calculate expressions on cached results to answer the new query, but all the columns must be in the cached result. For example, the query: SELECT product, month, averageprice FROM sales WHERE year = 2000 hits cache on the query: SELECT product, month, dollars, unitsales FROM sales WHERE year = 2000 because `averageprice` can be computed from `dollars` and `unitsales` (`averageprice = dollars/unitsales`).
`WHERE` clause must be semantically the same or a logical subset	For the query to qualify as a cache hit, the `WHERE` clause constraints must be either equivalent to the cached results, or a subset of the cached results. A `WHERE` clause that is a logical subset of a cached query qualifies for a cache hit if the subset meets one of the following criterion: A subset of `IN` list values. Queries requesting fewer elements of an `IN` list cached query qualify for a cache hit. For example, the following query: SELECT employeename, region FROM employee, geography WHERE region in ('EAST', 'WEST') qualifies as a hit on the following cached query: SELECT employeename, region FROM employee, geography WHERE region in ('NORTH', 'SOUTH', 'EAST', 'WEST') It contains fewer (but identical) `OR` constraints than the cached result. It contains a logical subset of a literal comparison. For example, the following predicate: WHERE revenue < 1000 qualifies as a cache hit on a comparable query with the predicate: WHERE revenue < 5000 There is no `WHERE` clause. If a query with no `WHERE` clause is cached, then queries that satisfy all other cache hit rules qualify as cache hits regardless of their `WHERE` clause. In addition columns that are used on the `WHERE` clause must be on the projection list. For example, the following query: SELECT employeename FROM employee, geography WHERE region in ('EAST', 'WEST') Does not result in a cache hit for the seeding query in the previous list because REGION is not on the projection list.
Dimension-only queries must be an exact match	If a query is dimension only, meaning that no fact or measure is included in the query, then only an exact match of the projection columns of the cached query hits the cache. This behavior prevents false positives when there are multiple logical sources for a dimension table.
Queries with special functions must be an exact match	Other queries that contain special functions such as time series functions (`AGO`, `TODATE`, and `PERIODROLLING`), limit and offset functions (`OFFSET` and `FETCH`), relationship functions (`ISANCESTOR`, `ISLEAF`, `ISROOT`, and `ISSIBLING`), external aggregation functions, and generally filter metrics must also be an exact match with the projection columns in the cached query. In these cases, the filter must also be an exact match. For filter metrics, if the filter metric can be rewritten as a `WHERE` clause, then the subset cache might be leveraged.
Set of logical tables must match	To qualify as a cache hit, all incoming queries must have the same set of logical tables as the cache entry. This rule avoids false cache hits. For example, `SELECT * FROM product` does not match `SELECT * FROM product, sales`.
Session variable values must match, including security session variables	If the logical SQL or physical SQL statement refers to any session variable, then the session variable values must match. Otherwise, the cache is not hit. In addition, the value of session variables that are security sensitive must match the security session variable values that are defined in the repository, even though the logical SQL statement itself does not reference session variables. See Ensuring Correct Cache Results When Using Row-Level Database Security.
Equivalent join conditions	The resultant joined logical table of a new query request has to be the same as (or a subset of) the cached results to qualify for a cache hit.
`DISTINCT` attribute must be the same	If a cached query eliminates duplicate records with `DISTINCT` processing (for example, `SELECT DISTINCT`...), then requests for the cached columns must also include the `DISTINCT` processing; a request for the same column without the `DISTINCT` processing is a cache miss.
Queries must contain compatible aggregation levels	Queries that request an aggregated level of information can use cached results at a lower level of aggregation. For example, the following query requests the quantity sold at the supplier and region and city level: SELECT supplier, region, city, qtysold FROM suppliercity The following query requests the quantity sold at the city level: SELECT city, qtysold FROM suppliercity The second query results in a cache hit on the first query.
Limited additional aggregation	For example, if a query with the column `qtysold` is cached, then a request for `RANK(qtysold)` results in a cache miss. Additionally, a query that requests `qtysold` at the country level can get a cache hit from a query that requests `qtysold` at the country, region level.
`ORDER BY` clause must be comprised of columns in the select list	Queries that order by columns that are not contained in the select list result in cache misses.
Avoiding cache misses using advanced hit detection	You can avoid some cache misses by setting the parameter `USE_ADVANCED_HIT_DETECTION` to `YES` in the NQSConfig.INI file. Advanced hit detection enables an expanded search of the cache for hits. See USE_ADVANCED_HIT_DETECTION.
Diagnosing cache hit behavior	To better assess cache hit behavior, set the ENABLE_CACHE_DIAGNOSTICS session variable to 4, as shown in the following example: ENABLE_CACHE_DIAGNOSTICS=4

Ensuring Correct Cache Results When Using Row-Level Database Security

When using a row-level database security strategy, such as a Virtual Private Database (VPD), the returned data results are contingent on the authorization credentials of the user.

Because of this, the Oracle BI Server must know whether a data source is using row-level database security and which variables are relevant to security.

To ensure that cache hits only occur on cache entries that include and match all security-sensitive variables, you must correctly configure the database object and session variable objects in the Administration Tool, as follows:

Database object. In the Physical layer, in the General tab of the Database dialog, select Virtual Private Database to specify that the data source is using row-level database security.

If you are using row-level database security with shared caching, then you must select this option to prevent the sharing of cache entries whose security-sensitive variables do not match.
Session Variable object. For variables that you are using for authentication, in the Session Variable dialog, select Security Sensitive to identify them as sensitive to security when using a row-level database security strategy. This option ensures that cache entries are marked with the security-sensitive variables, enabling security-sensitive variable matching on all incoming queries.

Refer to the following resources:

Setting Up Row-Level Security in the Database in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition
Managing Session Variables in Security Guide for Oracle Business Intelligence Enterprise Edition
Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for general information about database and session variable objects

Running 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 does not alleviate.

When the Oracle BI Server refreshes repository variables, it examines business models to determine if they reference those repository variables. If they do, the Oracle BI Server then purges all cache for those business models. See Changes to Dynamic Repository Variables.

Using Agents to Seed the Oracle BI Server Cache

You can configure agents to seed the Oracle BI Server 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 execute requests that refresh this data.

Log in to Oracle Business Intelligence 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.
Optionally, 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 BI 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 do not 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 do not address ad-hoc queries or drills.

Using the Cache Manager

The Cache Manager lets you view information about the entire query cache and information about individual entries in the query cache that are associated with the open repository.

You can also use the Cache Manager to select specific cache entries and perform various operations on those entries, such as viewing and saving the cached SQL statement, or purging them.

In the Administration Tool toolbar, select Manage, then Cache.
Select the Cache tab on the left explorer pane to view the cache entries for the current repository, business models, and users. The associated cache entries are reflected in the right pane, with the total number of entries shown in the view-only field at the top.

You can control the cache entry information and its display sequence using the Options settings (select Edit, then select Options from the Cache Manager, or select Tools, then Options, then Cache Manager from the Administration Tool menu). Information can include the options that are described in the next table.

Option	Description
User	The ID of the user who submitted the query that resulted in the cache entry.
Created	The time the cache entry's result set was created.
Last used	The last time the cache entry's result set satisfied a query. (After an unexpected shutdown of the Oracle BI Server, the last used time might temporarily have a stale value—a value that is older than the true value.)
Creation elapsed time	The time, in seconds, that is needed to create the result set for this cache entry. Note: The value that is stored in the cache object descriptor on disk is in units of milliseconds. The value is converted to seconds for display purposes.
Row count	The number of rows generated by the query.
Row size	The size of each row (in bytes) in this cache entry's result set.
Full size	Full size is the maximum size used, considering variable length columns, compression algorithm, and other factors. The actual size of the result set is smaller than Full size.
Column count	The number of columns in each row of this cache entry's result set.
Logical Request	The logical request that is associated with this cache entry. If subrequests are being cached, then this column shows the text of the subrequest.
Use count	The number of times that this cache entry's result set has satisfied a query (since Oracle BI Server startup).
Business model	The name of the business model that is associated with the cache entry.
Repository	The name of the Oracle Business Intelligence repository that is associated with this cache entry.
SQL	The SQL statement that is associated with this cache entry. If subrequests are being cached, then there might be multiple cache entries that are associated with a single SQL statement.
Query Server	The Oracle BI Server that serviced the query.
Fact Table Source	The fact table that is associated with the logical request for this cache entry.

Expand the repository tree to display all the business models with cache entries, and expand the business models to display all users with cache entries. The right pane displays only the cache entries associated with the selected item in the hierarchical tree.

Displaying Global Cache Information in the Cache Manager

Select Action, then select Show Info to display global cache information.

The table below describes the information that appears in the Global Cache Information window.

Column	Description
Amount of space still available for cache storage use	The amount of space, in megabytes, still available for cache storage.
Amount of space used on disks containing cache related files	The total amount of space, in megabytes, used on the disk that contains cache-related files (not just space used for the cache-related files).
Maximum allowable number of entries in cache	The maximum number of entries that can be in the cache, from the `MAX_CACHE_ENTRIES` parameter in the `NQSConfig.INI` file.
Maximum allowable number of rows per cache entry result set	The maximum number of rows that are allowed for each cache entry's result set, from the `MAX_ROWS_PER_CACHE_ENTRY` parameter in the `NQSConfig.INI` file.
Number of entries currently in cache	The current number of entries in the global cache. These entries might relate to multiple repositories.
Number of queries not satisfied from cache since startup of Oracle BI Server	Cache misses, since the last time the Oracle BI Server was started.
Number of queries satisfied from cache since startup of Oracle BI Server	Cache hits, since the last time the Oracle BI Server was started.

With the Cache Manager as the active window, press F5, or select Action, then Refresh to refresh the display. This retrieves the current cache entries for the repository that you have open and the current global cache information. If the DSN is clustered, then information about all repositories in the cluster is displayed.

Purging Cache in the Administration Tool

Purging cache is the process of deleting entries from the query cache.

You can purge cache entries in the following ways:

Manually, using the Administration Tool Cache Manager facility (in online mode).
Automatically, by setting the Cache Persistence Time field in the Physical Table dialog for a particular table.
Automatically, by setting up an Oracle BI Server event polling table.
Automatically, as the cache storage space fills up.

Note:

You can also purge the cache programmatically using ODBC-extension functions. See Purging and Maintaining Cache Using ODBC Procedures.

In addition, cache can be purged when the value of dynamic repository variables changes. See Changes to Dynamic Repository Variables.

You can manually purge cache entries in the Cache Manager as follows:

Use the Administration Tool to open a repository in online mode.
Select Manage, then Cache to open the Cache Manager dialog.
Select Cache or Physical mode by selecting the appropriate tab in the left pane.
Browse the explorer tree to display the associated cache entries in the right pane.
Select the cache entries to purge, and then select Edit, then Purge to remove them. Or, right-click the selected entries and then select Purge.
- In Cache mode, select the entries to purge from those displayed in the right pane.
- In Physical mode, select the database, catalog, schema or tables to purge from the explorer tree in the left pane.
In Cache mode, you can purge:
- One or more selected cache entries that are associated with the open repository.
- One or more selected cache entries that are associated with a specified business model.
- One or more selected cache entries that are associated with a specified user within a business model.
In Physical mode, you can purge:
- All cache entries for all tables that are associated with one or more selected databases.
- All cache entries for all tables that are associated with one or more selected catalogs.
- All cache entries for all tables that are associated with one or more selected schemas.
- All cache entries that are associated with one or more selected tables.

Purging deletes the selected cache entries and associated metadata. Select Action, then Refresh or press F5 to refresh the cache display.