Search

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



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 accend the Performance of functions.


In this post i will try to manifest some recipes 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.




6 views
  • Facebook
  • Twitter
  • LinkedIn