Alternatives to Performance Weary PL/SQL Function- (Result_cache, Deterministic Function)

There are several real time problems where we come across Scanning of data records with PLSQL Functions, We prefer using functions for bushiness validations. But did any of us encountered a slowness while using it on table that scans through a very large data set. You might have come across such issues. Oracle has come up with various options over the years to ascend the Performance of functions.

In this post i will try to manifest some use cases that could rachet up your performance.

Result_cache:

Result cache represents very nice feature which is used to store results of query execution in the result cache area (part of the SGA). Since 11g you can cache pl/sql function results in the SGA and its available to all sessions which runs the application.

You enable result caching for a function by adding the RESULT_CACHE clause in the function definition.

Let's find is caching the result of function of any help to boost the weary functions.

Lets create some sample data prior to creating a weary function.

create table TEST
(
 seq NUMBER,
 descp VARCHAR2(100)
)
 
INSERT/*+Append*/ INTO TEST SELECT ROWNUM,DBMS_RANDOM.string('A',16) from dual connect by level <= 1000000

Creating a Result_cache Function: Now that we have created a TEST table, we have to create a function to run our test, In this example we are creating a function that would return the average letter of a string, i.e. if we pass AC it would return B. Probably not very useful in real world but simple enough to exhibit our code.

create or replace function Get_average_Char(Input Varchar2) 
RETURN Varchar2
 RESULT_CACHE RELIES_ON(TEST) IS
 l_sum number := 0;
BEGIN
 FOR i in 1 .. length(input) LOOP
 l_sum := l_sum + ASCII(SUBSTR(input, i, 1));
 END LOOP;
 RETURN CHR(l_sum / length(input));
END;

declare
l_value NUMBER;
l_start number;
Cursor C1 is 
 Select DESCP from TEST;
 BEGIN
 l_start := dbms_utility.get_time;
FOR Z in C1 loop
 IF Get_average_Char(z.descp) = 'd' THEN
 l_value := l_value + 1;
 END IF;
 END LOOP;
DBMS_OUTPUT.PUT_LINE('FIRST LOOP: '||(dbms_utility.get_time - l_start));
 l_start := dbms_utility.get_time;
 FOR Z in C1 loop
 IF Get_average_Char(z.descp) = 'd' THEN
 l_value := l_value + 1;
 END IF;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('SECOND LOOP: '||(dbms_utility.get_time - l_start));
 END;

You check out for yourself does it have significant impact on performance or not.

DETERMINISTIC Functions: This is my favorite and would be fairly impactful in our example as it gives us the liberty to create a index on the result of a function. A quick word about deterministic function is these functions returns same value for the same input parameters.

create or replace function Get_average_Char(Input Varchar2) 
RETURN Varchar2
 DETERMINISTIC IS
 l_sum number := 0;
BEGIN
 FOR i in 1 .. length(input) LOOP
 l_sum := l_sum + ASCII(SUBSTR(input, i, 1));
 END LOOP;
 RETURN CHR(l_sum / length(input));
END;

Create index TEST_IX1 ON TEST(get_average_char(DESCP));

declare
l_value varchar2(1000);
l_start number;
Cursor C1 is 
 Select DESCP from TEST;
 BEGIN
  l_start := dbms_utility.get_time;
FOR Z in C1 loop
l_value := Get_average_Char(z.descp);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TIME in hsec: '||(dbms_utility.get_time - l_start));
l_start := dbms_utility.get_time;
 END;

Refer TABLE function for some more.