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.
1.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.
2.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).
3.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.
4.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.
5.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), external aggregation functions, and 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.
6.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.
7.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.
8.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.
9.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.
10.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.
11.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.
12.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.
13.Avoiding cache misses using advanced hit detection:
You can avoid some cache misses by setting the parameter
1.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.
2.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).
3.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.
4.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.
5.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), external aggregation functions, and 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.
6.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.
7.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.
8.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.
9.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.
10.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.
11.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.
12.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.
13.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
Thank you for this Post. It is helpful. I have a question regarding the where clause. Say, I have cached a query with where clause on 1 column and then I will add one more column to the where clause which will be subset of the data in cached query. Will OBIEE hit this cached query when second query is run
ReplyDeleteex:
First query:
Select ename, dno , sal from employee where dno =10 --- cached this query
Second query is this :
Select ename, dno , sal from employee where dno =10 and sal>9000 -- Now will this query hit cache (as this is subset of the above query.
Thank you for your help.