Caching data for Discoverer

I thought it was about time for another post and this one is about how you can cache data for use in Discoverer.

Caching is just one strategy that you can use to make your Discoverer reports run faster. It is a bit like bribing a wayward child. You give it some cash and it will get on with the job a bit quicker. But the performance improvement doesn't come for free. You have to load your cache first and keep it ready for the Discoverer report to use which will use additional resource.

Caching is big a topic; too big to cover in a single post. There are different levels where you can cache data for Discoverer reports. You can use OracleAS Web cache to hold HTML web pages generated by Discoverer Viewer. You can cache data in the database ready for use by Discoverer. You can schedule Discoverer workbooks to run so that the results are cached ready for the user to access.

This time I will look at how you can cache data in the database where you hold records in a cache ready for Discoverer to use. Before I go into the details I think it is important to say that caching is not a technique you should use to make a badly written report run faster. If it badly written and inefficient the solution is to fix it, not add more complexity by caching data to try and make it run faster. Nor should you use caching to try and make one tricky report run a bit faster. It is something you need to design into your Discoverer environment at the beginning to give you the maximum benefit.

Caching data in the database is where records are cached in the database ready for Discoverer to use. Again there are many techniques you can use; some of these techniques depend on new features introduced in Oracle 11g. A database cache can make a big difference to the performance of your reports as it will enable Discoverer to run simpler SQL for the reports which will it turn give better performance. But, like with all performance work, it all depends on many factors; a database cache could make no difference or make your report run slower. It is all about using the right technique for the right situation.

Basically there are four points where data can be cached for a Discoverer report:

1. Before the user logs on
2. When the user logs on
3. On-the-fly when a report runs
4. In a SQL result cache

Unfortunately there is no eul_trigger$post_open_document trigger available in Discoverer or any mechanism that would allow you cache data when a workbook was first opened or run.

Caching data before the user logs on

Here the data is cached overnight or at regular intervals so that it is available when a user logs on.

The traditional technique is used is a materialized view. Materialized views (MVs) have two really cool features. Firstly, they can be automatically refreshed either on commit, or periodically or on demand. Secondly, they can use query redirection where an SQL statement that references the underlying tables but which only requires summary information is re-directed to the MV.

Oracle 11g introduces some new features for materialized views which could be very useful for Discoverer developers. One of the most interesting is that you can set up an OLAP cube in the database that summaries your data and then use a MV to redirect queries to the OLAP cube. Hence you could use the Discoverer relational tools to gain the benefits of running against an OLAP cube without having to switch to the Discoverer OLAP product.

Having said that, I would avoid using MVs and would only use them as a last resort. Here are some of the reasons why:

1. Normally, you cannot use an ‘on commit’ refresh on the MVs and therefore the information in the MVs is always older than the underlying tables. This means the results obtained from MVs are always slightly out of date.
2. Also, often you cannot use an incremental refresh on the MVs because this requires a primary key defined on the underlying tables. So the MVs are often completely refreshed periodically and this needs a lot of database resource.
3. When the MV is refreshed you don’t know which users will be accessing the information and therefore it is difficult to ensure the data security available in the underlying tables is replicated in the MVs.
4. Frequently the query used to define the MV is complex and therefore the database is not able to use query redirection because the database must be able to match query being redirected to the query defining the MV.

I have seen a number of projects come unstuck because they have tried to improve performance by making some of their reports into MVs. The result is many complex MVs which are constantly out of date and need huge database resources to refresh.

I have found the best approach is to design a small number of simple MVs that summarise some core tables that can be used by many reports.

Caching data when the user logs on

Here the data is cached when the user logs onto Discoverer. The mechanism that you use to run a PL/SQL procedure to cache the data will depend on your environment but could be either:

1. a database trigger that checks whether the current session is a Discoverer session
2. the Oracle Applications 11i ‘Initialization SQL Statement – Custom’ system profile can be used to run an initialization PL/SQL procedure for Discoverer sessions
3. an eul_trigger$post_login trigger is used to run a PL/SQL procedure.

When the session initialization process runs it can run SQL to cache data from the session which then can be used in all subsequent Discoverer reports. There are different places that you can cache data in the database.

Database Contexts – This is useful for storing session parameters that you can use in subsequent database queries.

Temporary Tables – You use one or more temporary tables as a cache for the Discoverer. The table then can be used within the Discoverer reports or as a base table for a list of values. The data in the temporary tables will only be visible to the current Discoverer session so is useful for storing information specific to the user that can be used to improve performance in reports.

PL/SQL arrays – This is useful for caching data that is required by the PL/SQL functions run from Discoverer. See the next section for more details of this technique.

For example, if many of your queries need to use an ORG_ID associated with a user then create a context namespace using:

CREATE OR REPLACE CONTEXT DISCO USING DISCO_UTILITIES_PKG;

Then in the process initialisation obtain the ORG_ID for the user and set the context using:

dbms_session.set_context('DISCO', 'ORG_ID', v_org_id);

You can then check the context in a condition in your view or Discoverer folder using SYS_CONTEXT('DISCO', 'ORG_ID') .

If you have lots of contexts that you need to set at initialisation then it is useful to put all the queries to obtain the contexts values in a view then you can set all the contexts using:

FOR xcon IN (SELECT context_name, context_value
FROM xxmod_dis_context_value_v)
LOOP
dbms_session.set_context('DISCO', xcon.context_name, xcon.context_value);
END LOOP;

Then any changes to the context set up can be accommodated by simply changing the view.

However, where you need to save many ORG_IDs or, for example, a list of PRODUCT_IDs that a user can see then the technique to a temporary table. For example, you can create the temporary table using:

CREATE GLOBAL TEMPORARY TABLE PRODUCT_ID_TMP
( PRODUCT_ID NUMBER(10),
CONSTRAINT PRODUCT_ID_TMP PRIMARY KEY (PRODUCT_ID) ENABLE
) ON COMMIT DELETE ROWS;

Then just insert the product ids into the table in the initialisation PL/SQL procedure and use the table in a Discoverer folder or view just like a normal table. When the session finishes the data is deleted.

Caching Data on-the-fly

Here data is loaded by the first call to a PL/SQL function into a PL/SQL array. Subsequent calls to the function use data from the internal array. This technique is very useful for converting internal codes into text descriptions that are required in a report. For example, in an Oracle Applications 11i environment where you need to make repeated lookups into tables such as FND_FLEX_VALUES_VL and FND_LOOKUP_VALUES to obtain segment and attribute descriptions. It can also be used to store values that are hard to obtain but need to be used many times in a report.

Taking a generic approach using a single cache_lookup function gives the best results. The cache_lookup function can be defined like this:

FUNCTION cache_lookup(lookup_type IN VARCHAR2,
lookup_code1 IN VARCHAR2) RETURN VARCHAR2

The lookup_type parameter determines which SQL statement is used to obtain the data for the cache. The lookup_code1 is a bind variable for the SQL statement. The cache_lookup function is overlaid with multiple versions with different numbers of bind variables, because some lookups require multiple bind variables. For example, the call to

cache_lookup('LOOKUP_MEANING', 'MAR_STATUS', papf.marital_status)

obtains the marital status text from the FND_LOOKUP_VALUES table.

The function actually works by using DBMS_UTILITY.get_hash_value to hash all the input parameters together and check in a BINARY_INTEGER indexed PL/SQL array to see whether the value is in the cache. If it is not then the SQL statement is run and the value is added to the cache. Subsequent calls to the function with the same parameters retrieve the value directly from the cache.

Oracle 11g introduces the concept of a function result cache which provides similar functionality so this could be the way to go if you are using 11g.

There is always a trade-off between the overheads of making a call to a PL/SQL function against the benefits of caching the data also bearing in mind that the Oracle database is often very efficient at caching data internally when processing SQL statements.

However here are four reasons why I often use PL/SQL caching:

1. It’s quicker. Joining lookup tables in to query often makes finding an efficient execution plan difficult for the optimiser. The most efficient SQL is often to use a scalar query within the SELECT statement but this is not supported by Discoverer or in a materialized view. An alternative is to outer join the lookup tables but the outer join always generates less efficient SQL and cannot be used with an OR condition. Using a PL/SQL cache can be quicker because it removes all the lookup tables from the main SQL giving the optimiser the best chance of finding an efficient execution plan. But really it all depends on the complexity of the lookups as to whether you will see a performance improvement using a PL/SQL cache.
2. It’s quicker again. A Pl/SQL caches lasts for the duration of a session. Often running ad-hoc Discoverer involves run using similar queries many times. Your caches is loaded on the first query so subsequent queries are quicker.
3. Better instrumentation. This is the big bonus of using a PL/SQL cache. Especially, with Oracle Applications there are often errors encountered when processing the lookups. Sometimes, there is more than one description for the lookup code, or no description is found when one is needed. Finding the row that has caused the problem can be very difficult. With a PL/SQL cache you can build in instrumentation code that raises a meaningful exception when an error is encountered. Even better you can make this behaviour configurable so that for normal users lookup errors are ignored.
4. Less code. Less code is always good. By using a generic lookup function you can put all your lookups in one place. This function can then be called in views, Discoverer calculations and anywhere else where you need to convert a code to text.
   

Caching data in SQL result cache

This is a Oracle 11g technique that allows you to save the results of an SQL query (or subquery within a query) in an SQL cache located in the SGA. This is probably most useful to cache the results from a complex query fragment within your Discoverer views. For example, if the results from dept are complex and difficult to obtain you might define a view based on a query like this:

SELECT employee_name, dept_id …
FROM emp_table
, (SELECT /*+ result_cache */ dept_id, dept_name, …
FROM dept) dept
WHERE emp.dept_id = dept.dept_id

I haven’t had a chance to benchmark this feature so cannot make any claims for performance improvement but I believe it would help in some situations.