Compound Trigger – A Performance Improvement for Trigger Executions

In Oracle database 11g release version 1, among the enhancements in trigger execution an important and efficient feature known as ‘Compound Trigger’ is rolled out. This feature is particularly exciting because of the performance improvements it may bring in along with it.

Auditing Bulk INSERT/UPDATE of a Table Efficiently

Assume you have a transactional table, this table is highly accessed by a front end application which does a lot of inserts and updates every minute. You also have a functional requirement of keeping track of the changes against the values inserted / updated. The traditional setup one would have for such a scenario is to write a separate trigger each for AFTER INSERT and AFTER UPDATE to fetch the values and insert them into an auditing table as well.

Automatic Detection of Data Sources of Result-Cached Function

Oracle 11g release 1 gave the option of caching function results. It is done using the optional reference of mentioning RESULT_CACHE keyword. The values of these function calls are stored in system global area (SGA). It will have one result for every unique combination of parameter of passed. These are available for every session. Thus this helps in greatly reducing the execution time of an often repeated complex process inside a function which gets same repeated input values. This will increase performance in high concurrent applications.

In Oracle 11g Release 1

It is mentioned, the best candidate for result caching is function that is invoked frequently and depend on information that changes infrequently or never. Consider the following function.

CREATE OR
REPLACE FUNCTION get_sal (p_emp_id in hr.employees.employee_id%type)
RETURN NUMBER
RESULT_CACHE
IS
lv_sal_rtn   hr.employees.salary%type;
BEGIN
   select salary
      into lv_sal_rtn
     from hr.employees
   where employee_id = p_emp_id;

 RETURN lv_sal_rtn;

EXCEPTION
  WHEN others
    then 
           RETURN 0;
 END;

Note: the function execution values are stored in SGA by explicitly mentioning RESULT_CACHE. And the downside of this is, at times you can get wrong return values. This is because the values are already cached in memory and not calculated again. Consider the following example.