Improving PL/SQL performance in APEX

One of the simplest tuning techniques to encapsulate PL/SQL used in APEX within packages, minimising the size of anonymous blocks. This applies to any PL/SQL within the page, including computations, processes, plugins, dynamic actions, validations, shortcuts and dynamic PL/SQL regions.

This change can make a big impact in the execution time of PL/SQL as it's processed at compile time instead of being parsed and compiled at runtime as dynamic PL/SQL.

Plug-ins can be wonderful black boxes and consumers may not care how it works or looks under the hood. I have a few other views on the use plug-ins that I'll share on day, but this post looks at a way you can always improve it's performance by shifting the supplied code into a PL/SQL package.

Example of freshly imported plug-in in APEX 5.0

You (re)move the code that's supplied and defined as a procedure in your own package, perhaps apx_plugins.

Then modify the "Render function name" to prefix the call, ie: apx_plugins.render_save_before_exit

I gathered performance data on two plugins using compiled vs dynamic code. The following graph shows the relative difference in rendering time between the two using 11g, where the red dots display how many data points I used.

APEX Plug-in Performance - Dynamic vs Compiled PL/SQL

This particular information was garnered from debug log execution time.

select message, avg(execution_time)
from apex_debug_messages
where message like '%sparkline%'
group by message;

Update: I have used Oracle's instrumentation to get better figures
http://www.grassroots-oracle.com/2019/11/on-interpreted-code-in-oracle-apex.html

The recommendation to move this code can be found in (At least) the help text for plugin PL/SQL and documentation.

Moving code to packages will also overcome other limitations such as how much code fits in the attribute, though I think this boundary should only be reached in extreme circumstances. The Excel2Collection plug-in is an example. The author had to compress the PL/SQL code to make it and be packaged as a plug-in.

Encapsulating PL/SQL also provides more opportunity for re-use, reducing chance of defects and
allowing more granular version control.

I would like to think Oracle Forms developers have been doing this for years, in part to help minimise network traffic.

If I'm not mistaken, the same concepts can be applied to complex SQL, moving them into database views.

Daniel Hochleitner has also blogged on this topic.