Using DTrace to understand why PL/SQL packages can be faster than stored procedures (kgiPinObject)

I know – everyone knows, that PL/SQL packages are faster than stored procedures. If you’ll ask anyone at the training or in your dev team "what is better" – you’ll (almost) always hear: PL/SQL packages. But why exactly?

The documentation says:

The first time you invoke a package subprogram, Oracle Database loads the whole package into memory. Subsequent invocations of other subprograms in same the package require no disk I/O.

Source: http://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS00904

OK, it’s understandable and feels natural. But let’s try to think about it for a while.
I’ve created one procedure, one function and a package, containing those two. As simple as it can be:

I think that for this simple example, using "set timing on" will be sufficient enough.

So why there is a difference here? I don’t see any place here for disk I/O. Both procedures were compiled and loaded into memory, so we can’t speak about subsequent loads.

To understand what has happened I’ve created a very simple DTrace script, which will count the number of function calls, made by server process, associated with my session. I’ve used DTrace ability to trace functions, based on PID provider.

This technique is called "User-space Probing" and can be done also on Linux, using dynamic tracing tools like Systemtap https://sourceware.org/systemtap/SystemTap_Beginners_Guide/userspace-probing.html

Let’s check the tail output for test with package and separate PL/SQL stored function/procedure.

We don’t need a very detail investigation to notice the difference between those two executions.
I decided to check the execution times of 4 particular functions:

• plsql_run
• kglpnp
• kglPinSetContext
• kgiPinObject

The first one is responsible for the whole PL/SQL initialization and execution, so its time should be similar to what we can see in SQL*Plus timing. The next two should have something to do with maintaining the PL/SQL objects in library cache – "oradebug doc component" shows, that "kgl" components are responsible for LIBRARY CACHE:

I’m guessing by the name, that the last one should be responsible for pining the PL/SQL object in library cache for the execution time.

Based on this I used a very simple DTrace script to check the execution time and number of executions of each function:

Please notice, that this script will seriously affect the execution times, because monitoring can change the monitored object 😉

So right now the execution time for package looks like this:

And this is the output of my Dtrace script:

And this is the execution time of procedure call:

And the result of my DTrace script for this execution:

As you can see, the numbers are accurate enough to make some conclusions: execution time of function kgiPinObject is almost 80% of the whole execution time!

So the reason why PL/SQL packages are faster in subsequent invocations of other subprograms is not the "disk I/O", but penalty due to the need to pin/lock the foreign object in Library Cache each time, when I want to execute it.

So the practical knowledge "the lightning kills" did not change, but now at least I know, that it’s not because Zeus is mad at me 😉

P.S.
If you want to meet true experts in PL/SQL, you have to visit Poland, 7/8 OCTOBER 2016 at POUG (Pint with Oracle Users Group). Details can be found in here: http://poug.org/en/
Name of the function, which executes kgiPinObject will give -10% for the ticket 😉