Skip to main content

Blogging about 11g : Function Result Cache

Image
By
From the 11g New Features Guide:
"New in 11.1 is the ability to mark a PL/SQL function to indicate that its result should be cached to allow lookup, rather than recalculation, on the next access when the same parameter values are called. This function result cache saves significant space and time. Oracle does this transparently using the input actuals as the lookup key. The cache is system-wide so that all distinct sessions invoking the function benefits. If the result for a given set of actuals changes, you can use constructs to invalidate the cache entry so that it will be properly recalculated on the next access. This feature is especially useful when the function returns a value that is calculated from data selected from schema-level tables. For such uses, the invalidation constructs are simple and declarative.
Concurrent, multi-user applications that use this feature experience better response times. Applications that implement a session-private scheme consume significantly less memory by using this feature and, therefore, experience improved scalability."

Let's see if this statement is true:
First create a (simple) function with the 'result_cache' construct but without the 'relies_on' construct:

SQL> CREATE OR REPLACE FUNCTION CALC_SALARY_WITH_COMM
2 (p_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
3 RETURN NUMBER
4 result_cache
5 --relies_on (employees)
6 is
7   l_salary employees.salary%type;
8   l_comm number;
9   l_result number;
10 BEGIN
11   select salary
12   ,      1 + nvl(commission_pct,0)
13   into   l_salary
14   ,      l_comm
15   from   employees
16   where  employee_id = p_emp_id;
17   l_result := l_salary * l_comm;
18   return l_result;
19 exception
20   when no_data_found
21   then
22     RETURN 0;
23 END;
24 /

Call the function and cache the results of the calculation

SQL> select
2    ,      last_name
3    ,      salary
4    ,      commission_pct
5    ,      calc_salary_with_comm( employee_id ) total
6 from employees
7 where last_name = 'King'
8 /

LAST_NAME SALARY COMMISSION_PCT TOTAL
--------- -----  -------------- ----------
King      10000                 10000
King      24000                 24000

Change the values used in the calculation

SQL> update employees
2 set commission_pct = 0.99
3 where last_name = 'King'
4 /

Issue the select statement again and check that the results of the calculations are not changed! (this is due to the lack of the "RELIES_ON" clause)

LAST_NAME SALARY COMMISSION_PCT TOTAL
--------- -----  -------------- ----------
King      10000             .99 10000
King      24000             .99 24000

When you create another session and issue the select statement in this session you'll see that the results are still from the cache...(because they're still 'wrong').

Reset the values used in the calculation

SQL> update employees
2 set commission_pct = null
3 where last_name = 'King'
4 /

Now recreate the function with the "RELIES_ON" clause

SQL> CREATE OR REPLACE FUNCTION CALC_SALARY_WITH_COMM
2 (p_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
3 RETURN NUMBER
4 result_cache
5 relies_on (employees)
6 is
7   l_salary employees.salary%type;
8   l_comm number;
9   l_result number;
10 BEGIN
11   select salary
12   ,      1 + nvl(commission_pct,0)
13   into   l_salary
14   ,      l_comm
15   from   employees
16   where  employee_id = p_emp_id;
17   l_result := l_salary * l_comm;
18   return l_result;
19 exception
20   when no_data_found
21   then
22     RETURN 0;
23 END;
24 /

Issue the select statement and that will cache the results of the calculation
Change the values used in the calculation

SQL> update employees
2 set commission_pct = 0.99
3 where last_name = 'King'
4 /

Issue the select statement and check that the results of the calculations are changed now

LAST_NAME SALARY COMMISSION_PCT TOTAL
--------- -----  -------------- ----------
King      10000             .99 19900
King      24000             .99 47760

Reset the values used in the calculation

SQL> update employees
2 set commission_pct = null
3 where last_name = 'King'
4 /

Now do a little performance test

Create a similar function that doesn't cache

SQL> CREATE OR REPLACE FUNCTION CALC_SALARY_WITH_COMM_NC
2 (p_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
3 RETURN NUMBER
4 -- result_cache
5 -- relies_on (employees)
6 is
7   l_salary employees.salary%type;
8   l_comm number;
9   l_result number;
10 BEGIN
11   select salary
12   ,      1 + nvl(commission_pct,0)
13   into   l_salary
14   ,      l_comm
15   from   employees
16   where  employee_id = p_emp_id;
17   l_result := l_salary * l_comm;
18   return l_result;
19 exception
20   when no_data_found
21   then
22     RETURN 0;
23 END;
24 /

First call the function 100.000 times without caching

SQL> declare
2 result number;
3 begin
4 for i in 1..100000 loop
5 select calc_salary_with_comm_nc( employee_id ) total
6 into result
7 from employees
8 where employee_id = 100;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.80

Next call the function 100.000 times with caching

SQL> declare
2 result number;
3 begin
4 for i in 1..100000 loop
5 select calc_salary_with_comm( employee_id ) total
6 into result
7 from employees
8 where employee_id = 100;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.78

In this example the result is that the cached version is 40% faster....

The cached results are visible in V_$RESULT_CACHE_OBJECTS. If the RELIES_ON clause is used the cached result becomes "Invalid" when (whatever which) data of the table is changed. Why these "Invalid" cached results are still available is a mystery to me (why aren't they deleted?).

It seems that using this feature you can enhance the performance (when used wisely...).

As specified, this feature is (very) usefull when a function returns a kind of (complex) calculation using non-frequently changed table(s) - or no tables at all.
Untill now a developer usually caches these kinds of values (like parameter values) in a package variable (or array), but that's a per session solution. Using this feature the cached results are available in every session! The risk is ofcourse that the developer "forgets" using the RELIES_ON clause (it is optional!).

Comments

Filipe Silva said…
"The risk is of course that the developer "forgets" using the RELIES_ON clause."
No more risk: in 11gr2 RELIES_ON clause is not necessary!
4:05 am
Post a Comment

Popular posts from this blog

apex_application.g_f0x array processing in Oracle 12

By
If you created your own "updatable reports" or your custom version of tabular forms in Oracle Application Express, you'll end up with a query that looks similar to this one: then you disable the " Escape special characters " property and the result is an updatable multirecord form. That was easy, right? But now we need to process the changes in the Ename column when the form is submitted, but only if the checkbox is checked. All the columns are submitted as separated arrays, named apex_application.g_f0x - where the "x" is the value of the "p_idx" parameter you specified in the apex_item calls. So we have apex_application.g_f01, g_f02 and g_f03. But then you discover APEX has the oddity that the "checkbox" array only contains values for the checked rows. Thus if you just check "Jones", the length of g_f02 is 1 and it contains only the empno of Jones - while the other two arrays will contain all (14) rows. So for ...
Post a Comment
Read more

Filtering in the APEX Interactive Grid

By
Remember Oracle Forms? One of the nice features of Forms was the use of GLOBAL items. More or less comparable to Application Items in APEX. These GLOBALS where often used to pre-query data. For example you queried Employee 200 in Form A, then opened Form B and on opening that Form the Employee field is filled with that (GLOBAL) value of 200 and the query was executed. So without additional keys strokes or entering data, when switching to another Form a user would immediately see the data in the same context. And they loved that. In APEX you can create a similar experience using Application Items (or an Item on the Global Page) for Classic Reports (by setting a Default Value to a Search Item) and Interactive Reports (using the  APEX_IR.ADD_FILTER  procedure). But what about the Interactive Grid? There is no APEX_IG package ... so the first thing we have to figure out is how can we set a filter programmatically? Start with creating an Interactive Grid based upon the good o...
1 comment
Read more

Stop using validations for checking constraints !

By
 If you run your APEX application - like a Form based on the EMP table - and test if you can change the value of Department to something else then the standard values of 10, 20, 30 or 40, you'll get a nice error message like this: But it isn't really nice, is it? So what do a lot of developers do? They create a validation (just) in order to show a nicer, better worded, error message like "This is not a valid department".  And what you then just did is writing code twice : Once in the database as a (foreign key) check constraint and once as a sql statement in your validation. And we all know : writing code twice is usually not a good idea - and executing the same query twice is not enhancing your performance! So how can we transform that ugly error message into something nice? By combining two APEX features: the Error Handling Function and the Text Messages! Start with copying the example of an Error Handling Function from the APEX documentation. Create this function ...
Post a Comment
Read more