We learned the effect of the optimization level and subprogram inlining on the program performance. Once PLSQL_OPTIMIZER_LEVEL is set by the DBA, the optimization strategy is transparent to the end user. For this reason, Oracle introduced a pragma (PRAGMA INLINE) to authorize the user to explicitly inline a subprogram of its own choice. In the last section, we saw that PLSQL_OPTIMIZE_LEVEL 2 will prioritize the subprograms for inlining as per its own intelligence, while PLSQL_OPTIMIZE_LEVEL 3 will forcibly inline all the subprogram calls. While the latter setting might skip our expected subprogram, the latter setting might inline the expected subprogram along with the unnecessary inlining(inlinings).
Note
PRAGMA INLINE is the fifth pragma in Oracle after AUTONOMOUS_TRANSACTION, EXCEPTION_INIT, RESTRICT_REFERENCES, and SERIALLY_REUSABLE.
PRAGMA INLINE can be used with PLSQL_OPTIMIZER_LEVEL 2 and PLSQL_OPTIMIZER_LEVEL 3. When PLSQL_OPTIMIZE_LEVEL is set to 2, the pragma can be...