Move your APEX Plugin PL/SQL code to the Database

This blog post is about performance; Not about writing plugins.
APEX 20.1 is out now, but this information applies to all version of APEX, but it will be especially of interest if you’re on a version earlier to 19.2. Let me explain.

Before the Oracle database can execute your code (either SQL or PL/SQL), it needs to understand it. This is called parsing. For practical purposes, the first time the code is read and understood is called Hard Parsing, after that, it will be Soft Parsing.
Now, Hard Parsing, in particular, is a very expensive operation. This is relevant because many of the plugins you may use in your applications ship with “inline” code. This means that every time a plugin with inline code is used the code needs to be parsed (hard parsed, that is) and compiled. This is expensive and it’s slowing down your applications.
How expensive is hard parsing you ask? Well, I’m glad you asked.

The Test

I set up a page with 30 items using the, extremely popular, Select2 plugin.
1. APEX 5.1 — 30 Select2 Items — In-line Code
2. APEX 5.1 — 30 Select2 Items — Compiled Code

And the results were surprising. The page took ~2.7 sec to render with inline code, but only 0.27 sec with compiled code!

Debug Output - APEX 5.1.4 — Debug Output – APEX 5.1.4

Thirty Select2 page items on a page may sound like a lot, but it’s not unreasonable in a complex page. Imagine if you could shave 2.5s of your page rendering by doing nothing but compiling the code of your plugins.

The Silver Lining

APEX 19.2 brought a fantastic optimization. Plugin code is parsed only once per page request.
twitter.com/chrisneumueller/status/1200081803928834048

Correct. Starting with 19.2, we are parsing plugin code only once per request, to reduce parses when a plugin is used several times on a page.

— Chris Neumueller (@chrisneumueller) November 28, 2019

So, even though we still need to hard parse, even if you have 50 instances of the plugin on the page, it only gets parsed once. How fantastic is that?
That said, I still strongly recommend you compile your plugin code. The more concurrent users or page views you have the healthier your DB will be. And by healthy, of course, I mean being able to focus on what’s important instead of parsing and compiling code over and over.

Debug Output - 20.1 — Debug Output – 20.1

Important: Please do not go comparing my numbers between APEX 5.1.4 & APEX 20.1. For one thing, it’s 30 items vs 50 and it was on completely different hardware.

I should mention that several plugin developers are now adopting this approach and providing the plugin code as a package. I say bravo! Hopefully, more will do this by default.

The Solution

The solution is straightforward, compile the code of your plugin by moving it into a package. Yes, do it for plugins someone else coded, all plugins you download.

Here’s how you can go about it. There are two main routes:
1. One package per plugin
2. One package with multiple plugins
I’m partial to option 2, but I don’t think there’s a right or wrong. For example, I use a {prefix}_plugins package. For simplicity, let’s call it app_plugins
Edit your plugin definition and find the PL/SQL Code block with procedures and functions.
You’ll have to place their declaration in the package spec and the core PL/SQL plugin code in the package body. Since I like to combine plugins, I will change the render function to something like select2_render and the ajax procedure to select2_ajax. There’s zero harm in renaming

Then in the “Callbacks” section of the Plugin, change the previous calls to now reference your package.

Finally, do not forget to empty the original PL/SQL Code block, or your efforts will be in vain. This means APEX will continue to parse that inline PL/SQL code block because it may or may not be required to execute the plugin.

Plugin Upgrades

What’s the worse that will happen when you upgrade the plugin? Simple, if the new plugin still has inline code, the performance will degrade. But hopefully, you’ll remember to repeat the process of moving the PL/SQL code from inline to your plugins package.