OBIEE - Why a Query is Not Added to the Query Cache ?

1 - About

You may found that one SQL query is not added to the query cache. This article try to give you some reasons and tools to diagnose this behaviour.

Customers who rely on query result caching in the Oracle BI Server to meet their performance KPIs can use caching parameters.

3 - Reasons

The following describes some of the situations when the cache is not seeded.

  • Non-cacheable SQL element. If a SQL request contains Current_Timestamp, Current_Time, Rand, Populate, or a parameter marker then it is not added to the cache.

  • Non Cacheable Table. Physical tables in the Oracle BI Server repository can be marked 'non-cacheable'. If a query references any non-cacheable table then the query results will not be added to the cache.
  • Cache hit. In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache.
The exception is query hits that are aggregate roll-up hits. These are added to the cache if the POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.
  • Result set is too big. See Max parameters
  • Query is cancelled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
  • Oracle BI Server is clustered. Queries that fall into the 'cache seeding' family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query may be put into the cache on Oracle BI Server node 1, it may not be on Oracle BI Server node 2.
  • If the system session variable disable_cache_seed is set to 1. No cache will be seeded.

4 - Support

4.1 - NQQuery.log

The nqquery.log (Logging facilities) with a Level of 4 is the best tool to diagnose whether the Oracle BI Server compiler intended to add the entry into the query result cache.

In 11g, you can see this kind of message:

[2013-09-04T13:21:03.000+02:00] [OracleBIServerComponent] [TRACE:4] [USER-48] [] 
[ecid: 43ab55e9078af33b:-11844ded:14014c0282e:-8000-0000000000252fb8]
[tid: 46d8a940]
[requestid: 88280076]
[sessionid: 88280000] 
[username: gerardnico] 
The logical query block fail to hits or seed the cache in subrequest level due to   
session variable or config file disable subrequest cache 

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap