Databases Reference
In-Depth Information
There's more...
In this recipe, we have seen that the
PLSQL_CODE_TYPE
parameter controls the compilation
of the PL/SQL code. It can assume two values—
INTERPRETED
(the default) and
NATIVE
.
When a native compilation occurs, the source code (PL/SQL in our
examples) is translated to a binary form that can be directly executed
by the CPU. If there is no native compile phase, the source code
is interpreted (translated in a binary form as mentioned earlier) at
runtime when the code needs to be executed.
After changing the parameter to
NATIVE
, we have created a function and a procedure,
obtaining a significant performance gain.
If we use mathematical functions, such as
SQRT
,
SIN
, and so on, in
our compiled procedure,
NATIVE
, then the performance gain is less
evident, because those functions are already native-compiled in the
Oracle libraries. The best performance gain is obtained by compiling
user-defined PL/SQL functions.
Performance of SQL statements is not affected by native compilation.
See also
F
See the
Taking advantage of function result cache
,
Avoiding recursion
, and
In-lining
PL/SQL code
recipes in this chapter
Taking advantage of function result cache
In this recipe, we will see how to use the function result cache feature, available from Oracle
11
g
upwards, to enhance our function's performance.
How to do it...
The following steps will demonstrate the use of the functions result cache:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
