Sometimes even though we enabled cache in all areas, still few reports doesn’t hit Cache.I have noticed some valuable information related to Cache management in Oracle documentation.
Using logging facilities we can diagnose common reasons for getting a cache miss, where the logical SQL query that was supposed to seed the cache did not get inserted into the cache.
The following describes some situations when this might occur.
- 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 are not 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 NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to YES.
- Result set is too big. This situation occurs when you exceed the size set in DATA_STORAGE_PATHS, or if you have rows in excess of the number set in MAX_ROWS_PER_CACHE_ENTRY.
- Query is canceled. 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 might be put into the cache on Oracle BI Server node 1, it might not be on Oracle BI Server node 2.
Level 4 of query logging is the best tool to diagnose whether the Oracle BI Server compiler intended to add the entry into the query result cache.
Posts
