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 Oracle BI Server was able to use cache to answer the query and didn't go to the database at all. 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 |
All of the columns in the This rule describes the minimum requirement to hit the cache, but meeting this rule doesn't guarantee a cache hit. The other rules listed in this table also apply. |
Columns in 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:
hits cache on the query:
because |
|
For the query to qualify as a cache hit, the A
In addition columns that are used on the
Doesn't result in a cache hit for the seeding query in the previous list because REGION isn't 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 ( |
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, |
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 isn't hit. In addition, the value of session variables that are security sensitive must match the security session variable values that are defined in the semantic model, even though the logical SQL statement itself doesn't reference session variables. See Ensure 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. |
|
If a cached query eliminates duplicate records with |
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:
The following query requests the quantity sold at the city level:
The second query results in a cache hit on the first query. |
Limited additional aggregation |
For example, if a query with the column |
|
Queries that order by columns that aren't 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
|
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:
|
Ensure 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, 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 Model 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're 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 don't match.
-
Session Variable object. For security-related variables, 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 Managing Metadata Repositories for Oracle Analytics Server
-
Managing Session Variables in Managing Metadata Repositories for Oracle Analytics Server
-
Managing Metadata Repositories for Oracle Analytics Server for general information about database and session variable objects