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.
Comments