Using LU MicroCache

In the past years IFS Benchmarking team has performed several benchmarks with different vendors. During these benchmarks it has been discovered that IFS Applications has some areas to improve. Some of these areas to improve is:

The LU MicroCache addresses the first two areas in the above list. Due to new and improved functionality in PL/SQL language it is possible to use the LU MicroCache in IFS Applications. This paper tries to address the possibilities and drawbacks involved with the LU MicroCache .

Preliminary benchmark tests have shown that the LU MicroCache on the most (about 12) frequently used LU's can reduce the buffer gets, fetched by the database, with up 30%. Therefore its of great importance that we implements this feature on particular places within IFS Applications.

Contents

What problem addresses the LU MicroCache and how

IFS Applications LU's is designed to use views for clients to retrieve data. Since the clients often needs more data from other LU's the views uses PL/SQL functions (Get_Methods accessed through LU's primary key) in the select list and the where-clause. The get_ methods is executed at least once for each row fetched from the database and often with calling LU columns as parameters. A simple example:

Customer_API code snippet:

FUNCTION Get_Name (
   customer_no_ IN NUMBER ) RETURN VARCHAR2;
PRAGMA restrict_references(Get_Name, WNDS);

FUNCTION Get_Name (
   customer_no_ IN NUMBER ) RETURN VARCHAR2
IS
   temp_ customer_tab.name%TYPE;
   CURSOR get_attr IS
      SELECT name
      FROM customer_tab
      WHERE customer_no = customer_no_;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN temp_;
END Get_Name;

Client SQL:

SELECT order_no, customer_no, Customer_API.Get_Name(customer_no)
FROM   order_head
ORDER BY customer_no;

When executing this query the database first fetches all order_head rows and then exeutes Get_Name method for each row. Since this query is order by Customer_No it is most likely that the same customer_no is used several times in before changing customer_no. After reading this you should be aware of the problem. Then you should think: What if we save the last record fetched and check if it's the same record wanted again and just passes the saved record back to the client? Then we don't have to make a call (query) to the database. That's just the idea behind the LU MicroCache.

One other problem though is that it is not always this obvious to see where the LU MicroCache makes improvements.
Example: What if the above SQL was ordered by Order_No; then it would most certainly not be that often that the same Customer_No appears after each other, and then is the positive effect of the LU MicroCache gone.

What is LU MicroCache

It is very important to understand that LU MicroCache is an Oracle session cache, so every user logged on to Oracle has it's own cache. What does this mean to you? Lets say that we have ten users accessing the very same record in the same Customer LU time after time (using the same PK in method get_name(customer_no_ => 1)). Then user1 updates the name of that specific customer and flushes his cache. At that time all the user2-10 will retrieve the wrong name until they reads a new record and fetches Customer 1 again. The cache is also flushed if the user logs out in logs on again. To address this kind of problem one can use a timestamp together with the primary key, then the cache is flushed if the "best before" date has past.

So what must be done on an average LU to get the LU MicroCache to work.

How to use LU MicroCache

1. New global PL/SQL variables

Declared in the Package body we must have at least three new variables:

CREATE OR REPLACE PACKAGE BODY Customer_API IS

   -- This is the public record declared in the Package specification holding the cached values
   micro_cache_value_ Public_Rec;    

   -- For holding the cached Primary Key, 
   -- if more than one primary key exists we have to declare more than one variable
   micro_cache_id_    NUMBER;

   -- Used for the "best before" timestamp
   micro_cache_time_  NUMBER := 0;
   ...

Note: These declarations will lead to increased memory consumption in conjunction with one of the improvements points pointed out in the background story above.

2. Two new procedures

We need two new procedures to set and clear the cache.

PROCEDURE Invalidate_Cache___
IS
   null_value_ Public_Rec;
BEGIN
   micro_cache_id_    := NULL;
   micro_cache_value_ := null_value_;
END Invalidate_Cache___;

--
-- The logic for fetching the public record should be moved from the public 
-- Get or Get_Instance methods (if they exists) into Update_Cache___.
--
PROCEDURE Update_Cache___ (
   customer_no_ IN NUMBER )
IS
   null_value_ Public_Rec;
   time_       NUMBER;
   expired_    BOOLEAN;
   CURSOR get_attr IS
      SELECT name, col2, col3 ...
      FROM  customer_tab
      WHERE customer_no = customer_no_;
BEGIN
   -- Get best before offset time
   time_ := Database_SYS.Get_Time_Offset;
   -- Check if the time past more than 10 seconds or past.
   -- 10 second is just a proposal for best before time.
   expired_ := (time_ - micro_cache_time_) > 10);
   -- Check if expired and that Primary Key is equal to the Cached Key
   IF NOT expired_ AND (micro_cache_id_ = customer_no_) THEN
      NULL;
   ELSE
      OPEN get_attr;
      FETCH get_attr INTO micro_cache_value_;
      IF get_attr%NOTFOUND THEN
         micro_cache_value_ := null_value_;
      END IF;
      CLOSE get_attr;
      -- Set fetched cached Primary Key.
      -- Note!!! This can be more than one column, if the Primary Key has more than one column
      micro_cache_id_ := customer_no_;
      -- Set new MicroCache best before offset time
      micro_cache_time_ := time_;
   END IF;
END Update_Cache___;

3. Use the cache procedures

All that is left is to use the newly create cache methods.

PROCEDURE Update___ (
   objid_ IN VARCHAR2,
   oldrec_ IN &TABLE%ROWTYPE,
   newrec_ IN OUT &TABLE%ROWTYPE,
   attr_ IN OUT VARCHAR2,
   objversion_ IN OUT VARCHAR2,
   by_keys_ IN BOOLEAN DEFAULT FALSE )
IS
BEGIN
   newrec_.rowversion := sysdate;
   objversion_ := to_char(newrec_.rowversion,'YYYYMMDDHH24MISS');
   IF by_keys_ THEN
      UPDATE customer_tab
      SET name = newrec_.name
      ...
   ELSE
      ...
   END IF;
   -- Invalidate the cache when updating the record 
   -- (this will at least reset current session's cache)
   Invalidate_Cache___;
EXCEPTION
   WHEN dup_val_on_index THEN
      Error_SYS.Record_Exist(lu_name_);
END Update___;

FUNCTION Get_Name (
   customer_no_ IN NUMBER ) RETURN VARCHAR2
IS
BEGIN
   -- Use Update_Cache___ instead of cursor.
   Update_Cache___(customer_no_);
   RETURN micro_cache_value_.name;
END Get_Name;

FUNCTION Get (
   customer_no_ IN NUMBER ) RETURN Public_Rec
IS
BEGIN
   -- Use Update_Cache___ instead of cursor.
   -- Move Cursor code to Update_Cache___ method.
   Update_Cache___(customer_no_);
   RETURN micro_cache_value_;
END Get;

PROCEDURE Init
IS
BEGIN
   Invalidate_Cache___;
END Init;

When to use LU MicroCache

Good examples of when to use LU MicroCache is:

Note: Remember that LU MicroCache always must be implemented with common sense and that it is not always the solution to performance problems.

When not to use LU MicroCache

Examples of when not to use the MicroCache: