The UDF Pragma

The PRAGMA UDF compiler directive is not a caching mechanism. ‘So, what’s it doing in the chapter on caching?’ we hear you ask.

It’s an optimization technique for subprograms, so it fits in nicely into our current discussion. It tells the compiler that the function ought to be prepared for execution from SQL statements. Because of that information, Oracle can sometimes reduce the cost of context switches.

As of Oracle Database 12c, there is also the possibility of adding a PL/SQL function to your SQL statement with the WITH clause. A non-trivial example is described on ianhellstrom.org, from which it follows that the WITH clause is marginally faster than the UDF pragma, but that the latter has the advantage that it is modular, whereas the former is the equivalent of hard coding your functions.

We can therefore recommend that you first try to add PRAGMA UDF to your PL/SQL functions if and only if they are called from SQL statements but not PL/SQL code. If that does not provide a significant benefit, then try the WITH function block.