with “(select f(owner) from dual)”—you will
notice a massive reduction in calls to the
function, as shown in Listing 3.
As you can see in Listing 3, the function went from 72,841 calls down to 66!
And the CPU time dropped dramatically as
well. That reduction in CPU time resulted
from not calling the function (and therefore not making the context switch from
SQL to PL/SQL and not invoking DBMS_
APPLICATION_INFO and LENGTH so often).
Now, some of you might be thinking
about the possibility of marking the func-
tion deterministic, because it is, in fact,
deterministic. Wouldn’t that reduce the
number of function calls as well? The short
answer is “yes,” but the longer answer is
“yes, but not as well as scalar subquery
caching can.”
As you can see in the deterministic
function in Listing 4, the number of calls is
reduced, but only down to 8,316. The scalar
subquery cache in this case was better than
just marking the function as deterministic.
(Note: Marking a function deterministic
affects caching only in Oracle Database 10g
and above; you will see no change in the
number of calls in Oracle9i Database with
deterministic functions).
Going one step further, you might ask,
“But what if you used the function result
cache in Oracle Database 11 g Could you get
the function calls down to zero?” The answer
is, “Yes, sort of.” The function calls would
go down to zero, but the context switching
from SQL to PL/SQL would remain at a very
high value: 72,841 times in this case.
Listing 5 shows a function result cache
solution. The number of function calls is
32 (because I happen to have 32 schemas
in my database), and the CPU time is 0.73
seconds—about the same as that of the
DETERMINISTIC function and far above the
CPU time of 0.29 seconds used by the 66
calls made by the scalar subquery.
Furthermore, if you were to run this query
again—as shown in Listing 6—you’d discover that it calls the function zero times, but
the CPU time (0.63 seconds) is still longer
than the CPU time for the scalar subquery
cache example in Listing 3.
This just shows that even if your function is deterministic, even if it is “result-
Code Listing 3: Reducing function calls with scalar subquery
SQL> begin
2 :cpu := dbms_utility.get_cpu_time;
3 dbms_application_info.set_client_info(0);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select owner, (select f(owner) from dual) f from stage;
…
72841 rows selected.
SQL> select
2 dbms_utility.get_cpu_time-:cpu cpu_hsecs,
3 userenv('client_info')
4 from dual;
CPU_HSECS USERENV('CLIENT_INFO')
—————————————— ————————————————————————————————
29 66
Code Listing 4: Deterministic function variation
SQL> create or replace function f( x in varchar2 )
return number
2 DETERMINISTIC
3 as
4 begin
5 dbms_application_info.set_client_info
(userenv('client_info')+ 1 );
6 return length(x);
7 end;
8 /
Function created.
SQL> begin
2 :cpu := dbms_utility.get_cpu_time;
3 dbms_application_info.set_client_info(0);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select owner, f(owner) from stage;
…
72841 rows selected.
SQL> select
2 dbms_utility.get_cpu_time-:cpu cpu_hsecs,
3 userenv('client_info')
4 from dual;
CPU_HSECS USERENV('CLIENT_INFO')
—————————————— ————————————————————————————————
69 8316
cached,” there is a good reason to always
wrap your function call in a SELEC T FROM
DUAL. I’ve gotten into the habit over the
years of never coding
select *
from t
where column = plsql_function(..);
but rather including a simple scalar
subquery
select *
from t
where column =
(select plsql_function(…)
from dual);
