Oracle 12c: PRAGMA UDF – the truth

Let’s check, why PRAGMA UDF makes execution faster, then regular function.
C function responsible for calling a PL/SQL code from SQL is called "plsql_run":

Now let’s try to create a new HR session and create a simple function which will multiple values by 2:

From other terminal I will connect to the HR session with GDB:

and I will set the breakpoint for plsql_run function

In the HR session, I will use my function on the table "EMPLOYEES".

In the gdb we can see, that the breakpoint was hit 107 times – this is related to the number of rows in the table.

And what will happen if we add "PRAGMA UDF" syntax to our little function?

The gdb shows a funny thing:

So why, the function was called only 9 times?
The tkprof shows something that can be interesting:

There was 9 fetches! Is it possible that the function was called once for each fetch? We can make sure by changing the arraysize in SQL*Plus

The gdb shows three plsql_run executions:

And the tkprof also:

So the fetch size can have actual impact on PRAGMA UDF functionality.
Let’s see what will happen from inside of the PL/SQL – without throwing the results to the client:

gdb shows two plsql_run hits – one for anonymous block and one for the "BY2″ function.