Reasons Why a Query is Not Added to the Cache:
If you rely on query result caching in the Oracle BI Server to meet your performance KPIs, you can use caching parameters to help determine why a cache hit did not occur. Logging facilities can help to 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 of the situations when this might occur.
1. Caching is not configured. Caching is not enabled in NQSConfig.ini file.
2. 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.
3. Non-cacheable table. Physical tables in the Oracle BI Server repository can be marked 'noncacheable'. If a query references any non-cacheable table then the query results will not be added to the cache.
4. 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.
5. Result set is too big. MAX_ROWS_PER_CACHE_ENTRY and MAX_CACHE_ENTRY_SIZE parameters in nqsconfig.ini limit the max rows and max size for cache.
6. Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through a time out.
7. 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 you rely on query result caching in the Oracle BI Server to meet your performance KPIs, you can use caching parameters to help determine why a cache hit did not occur. Logging facilities can help to 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 of the situations when this might occur.
1. Caching is not configured. Caching is not enabled in NQSConfig.ini file.
2. 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.
3. Non-cacheable table. Physical tables in the Oracle BI Server repository can be marked 'noncacheable'. If a query references any non-cacheable table then the query results will not be added to the cache.
4. 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.
5. Result set is too big. MAX_ROWS_PER_CACHE_ENTRY and MAX_CACHE_ENTRY_SIZE parameters in nqsconfig.ini limit the max rows and max size for cache.
6. Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through a time out.
7. 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.
No comments:
Post a Comment