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.
The following describes some of the situations when the cache is not seeded.
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.
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