Query Cache in OBIEE

The Oracle BI Server can save the results of a query in cache files and then reuse those results later when a similar query is requested. So, for any query request, the database processing is required only once, not every time the query is run.

When a user requests any query, BI Presentation Server generates Logical Query and handover it to the BI server.[Before handover the Logical SQL to BI Server, the Presentation Server checks the Presentation Server cache for similar Logical SQL. If found, the Presentation Server will get the result from that cache and return to the user without contacting the BI Server. Read more...] Then, the BI Server checks the disk-based cache of query result sets (query  cache). If the similar query and its result set are stored in query cache, then BI Server will retrieve the data from the query cache and return it to the Presentation Server. So, the use of cache saves database processing, processing time, and the query response time will be fast.

Advantages of Cache in OBIEE :

Dramatic improvement of query performance : BI Server stores the query results in local cache. The similar subsequent queries can use those results. So, the BI Server does not have to access the backend database. This improves the query performance.

Less network traffic and Reduction in database processing: If the result of a query is stored in a cache, then the result for a similar subsequent queries do not have to come over the network to the BI Server. Not running the query on the database frees the database server to do other work. If the database uses a charge back system, it could save money in the budget as well.

Reduction in Oracle BI Server processing overhead : Another benefit of using the cache to answer a query is savings in processing time on the Oracle BI Server, especially if the query results are retrieved from multiple databases. Depending on the query, there might be considerable join and sort processing in the server. If the query is already calculated, this processing is avoided, freeing server resources for other tasks.

Tip : If there are more than one source tables(those tables may be resides in different sources) for a query, then BI Server will generate separate physical queries and send them to the corresponding databases and get the results. When the BI Server gets the result set, it has to do the appropriate joins and handover it Presentation Server.