Skip to content

Micro Cache & Micro Cache Array

IFS Cloud are designed to use views in the client to retrieve data and as the client often needs more data from other entities, the views uses PL/SQL functions to get that information. This is done either by Get_methods accessed through a primary key (PK) in the select list or in the where clause. The Get_methods are executed at least once for each row  and are fetched from the database, often with calling columns as parameters. If instead the last record fetched is saved, and a check is added to see if it is the same record that is requested again, then the saved record could be passed back to the client. If such functionality existed then a database call (query) isn't necessary and that is exactly what the Micro Cache provides.

The Micro Cache is a runtime cache where the data is saved temporarily. By using the Micro Cache the buffer gets can be reduced significantly, but due to some limitations it should be implemented with care.

The Micro Cache is also an Oracle session cache, so each user logged on to Oracle has it's own cache. This means that if ten users access the same record in the same entity time after time, then by using the same primary key (PK) for a method they will use their own cache. If the first user updates the entity his cache will be flushed, but the other users caches are not flushed. This will lead to that they will retrieve the wrong data until they have read a new record and done a new fetch again. The cache is also flushed if one user logs out and that Oracle session is acquired by another user. In order to minimize this problem a timestamp together with the primary key could be used, then the cache is flushed if the time has past the limit. IFS EE APF pages, the cache is flushed after each client call to the server but not in other clients such as IFS Aurena / IFS EE Feature pages or Mobile clients.

The Micro Cache Array is an extension of Micro cache implementation. The only difference with Micro Cache is, Micro Cache array can store multiple records instead of a single record. When the array is filled with records, one item is randomly removed at the next request.

When to use Micro Cache

Some good examples of when to use Micro Cache are as follows:

  • Values that can be reused even it they have been updated to a new value, e.g.. customer name, part description and so on.
  • Entities with few values, e.g.. Countries, Sites and so on.
  • Entities where most users request the same few records, e.g.. where company or site is the only key.
  • Where the same entity get_methods exists more than once in the select list.
  • Where a get_method exists in the where-clause and its likely that the same request appears frequently.
  • If the get_methods are called in a way where data is ordered.

Example of when to use Micro Cache

When executing the query below the database first fetches all Order_Head rows and then executes Get_Cust_Name method for each row. Since this query is ordered by Customer_No it is most likely that the same Customer_No is used several times, before changing the Customer_no. If the last record fetched is saved, then there is a check to see if it is the same record that is being requested again. If it is, then the saved record is passed back to the client. Then a call (query) to the database isn't necessary.

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

FUNCTION Get_Cust_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_Cust_Name;

When to use Micro Cache Array

Micro Cache Array is preferred for most of the scenarios where Micro Cache is applicable & more than one record of the LU is referred in an application flow. But if this number of records is in hundreds or even higher, then there may be hardly any advantage implementing either Micro Cache or Micro Cache Array for that LU.

When not to use Micro Cache or Micro Cache Array

Examples of when not to use either Micro Cache or Micro Cache Array:

  • Values that are extreme important to get the exact value, e.g.. currency rate, inventory quantity on hand and so on.
  • Stored or computed values which are dependent on child tables, e.g.. order values based on order lines.
  • If the query returns the requested values in an unsorted result set, then the positive effect is gone, e.g.. in the query example above was ordered by Order_No instead of Customer_No then it is unlikely that the same Customer_No  will appear after each other.
  • On derived attributes.

Micro Cache & Micro Cache Array Implementation

To get the Micro Cache / Micro Cache Array implementation to work, you only need to add a codegen property in the entity model.

Micro Cache:

codegenproperties {
   DbImplementation "micro-cache";
}

When the code is generated a few variables will be declared. A public record holding the cached values, a number to be used for the timestamp and an id for the cached Primary Key. If more than one Primary Key exists then more variables needs to be declared.

FUNCTION Private_Declarations (
   customer_no_ IN NUMBER) RETURN VARCHAR2
IS   
   micro_cache_value_ Public_Rec;    
   micro_cache_id_    NUMBER;
   micro_cache_time_  NUMBER := 0;
BEGIN
   RETURN customer_no_;
END Private_Declarations;

The Update_Cache_ method in the Get_method will be generated and used instead of a cursor and then return the value from the Micro Cache record.

FUNCTION Get_Name (
   customer_no_ IN NUMBER ) RETURN VARCHAR2
IS
BEGIN   
   Update_Cache___(customer_no_);
   RETURN micro_cache_value_.name;
END Get_Name;

The Invalidate_Cache___ method will be generated into Update___ and Delete___ to reset the cache in case the record is modified or removed.

PROCEDURE Update___ (
   objid_      IN     VARCHAR2,
   oldrec_     IN     micro_cache_tab%ROWTYPE,
   newrec_     IN OUT micro_cache_tab%ROWTYPE,
   attr_       IN OUT VARCHAR2,
   objversion_ IN OUT VARCHAR2,
   by_keys_    IN     BOOLEAN DEFAULT FALSE )
IS
   value_too_large  EXCEPTION;
   PRAGMA           EXCEPTION_INIT(value_too_large, -12899);
BEGIN
   newrec_.rowversion := sysdate;
   IF by_keys_ THEN
      UPDATE micro_cache_tab
         SET ROW = newrec_
         WHERE id = newrec_.id;
   ELSE
      UPDATE micro_cache_tab
         SET ROW = newrec_
         WHERE rowid = objid_;
   END IF;
   objversion_ := to_char(newrec_.rowversion,'YYYYMMDDHH24MISS');
   Invalidate_Cache___;
EXCEPTION
   WHEN dup_val_on_index THEN
      DECLARE
         constraint_ VARCHAR2(4000) := Utility_SYS.Get_Constraint_From_Error_Msg(sqlerrm);
      BEGIN
         IF (constraint_ = 'MICRO_CACHE_RK') THEN
            Error_SYS.Fnd_Rowkey_Exist(Micro_Cache_API.lu_name_, newrec_.rowkey);
         ELSIF (constraint_ = 'MICRO_CACHE_PK') THEN
            Raise_Record_Exist___(newrec_);
         ELSE
            Raise_Constraint_Violated___(newrec_, constraint_);
         END IF;
      END;
   WHEN value_too_large THEN
      Error_SYS.Fnd_Item_Length(lu_name_, sqlerrm);
END Update___;