Optimizing PL/SQL Code - Oracle Database 11gR2 Performance Tuning - page 277

How it works...
In the next screenshot, we can see the output obtained after the execution of step 5
and step 6:
In the previous example, we are using a function many times. Every time we call SIMPLE_
FUNCTION , there is an overhead due to subprogram calling, which can be significant when
the called procedure is small and is called many times, so the procedure execution time is
negligible compared to the subprogram calling overhead.
The function inlining technique is very common in compilers—together with loop unrolling and
other tricks—and its implementation is very simple. The compiler inserts the body (the entire
source code) of the called subprogram into the caller body instead of the calling code (the
function call) avoiding the subprogram calls and their overhead, at the expense of longer code.
Starting from Oracle Database 11 g , there is a new feature that allows us to make our
procedures and functions inline— PRAGMA INLINE . With this simple statement, we
have instructed the database to inline (the parameter YES ) the SIMPLE_FUNCTION
implementation inside our STRESS_INLINING procedure. The statement is as follows:
PRAGMA INLINE (SIMPLE_FUNCTION, 'YES');
Using this feature, we can see a performance improvement in terms of our code execution
time, without losing the modularity and isolation of the code.
There's more...
In this recipe, we have used manual inlining, because we have written the PRAGMA INLINE
directive by ourselves to inform the database about the function call that has to be inlined.