Oracle APEX Plugin Performance

Last week a customer of mine hired me to look into performance issues they had at larger forms within their APEX inhouse app.
After digging into it, I recognized that they used a lot of plugins in this particular app (~ 90), and of course a lot of known ones from the community like Select2 or Dropzone.

The problematic pages were mostly larger forms with a lot of items, in this case also a lot of plugin items, like mentioned above Select2. This item type plugin was used around 30 times on this page…

So first I had a look into the debug output of the page and I saw that most of the output was the PL/SQL source code of this plugin. So 30 times the APEX engine had to parse and execute the complete PL/SQL source of Select2, and the newest version of it has 773 lines of PL/SQL code…

The second problem I found was that all static files of the plugin (e.g JavaScript and CSS files) came from the database, you will see that in the debug output in APEX > 5.0 environments on the file path of the files that are loaded, e.g:

Load JavaScript file=demo/r/140/files/plugin/53116667758949585/v1/select2-apex.js

File paths which contain the virtual directory “/r/” shows us that the file being loaded comes from the DB, so each time a static file was loaded it hits the DB which returns a BLOB. Of course browsers will cache such a file after getting it once, but in my opinion the best place for static files is the web server itself not the DB…

So I found 2 problems in this case:
1) Large PL/SQL code of a plugin
2) Plugin static files are loaded from DB

A possible solution for both:
1) Transfer the PL/SQL code from the plugin to a real DB object, e.g a PL/SQL package
2) Transfer all plugin static files to the web server

After creating a PL/SQL package (e.g. select2_plg_pkg) with the copied source code from the plugin and moving all static files to the web server, the plugin looked like this:

So lets compare the page performance before and after the changes (30 Select2 items / 2 Dropzone Regions).
With the orginal plugins the page rendering took: ~ 4.3 seconds
With the modified plugins the page rendering took: ~ 1.3 seconds

This is an performance improvement of ~330% !! So the page is 3x faster in rendering than before. To be honest I didn´t expect such an performance increase by changing the plugin code…

If you want to see it in action, here´s a little demo app:
https://orclapex.io/ords/f?p=PLG_PERFORMANCE
Use demo / demo for login!

Conclusion:
As best practise learns us to have as less code in APEX as possible (e.g only function calls), this is also valid for plugins. So be careful in using plugins and always try to review the code. In this case the plugins are not bad coded or something like that, instead it´s the most easy way to share plugins with others, having files and PL/SQL source code inside the plugin. This means that you only need the plugin export SQL file to import and use it.

But some plugins have a lot of source code (here over 700 lines) which makes it problemtatic using such a plugin many times on a page, because this source code have to be parsed again and again.

If you decide to also improve the performance of your plugins like I told you here, be careful, thus the plugin now consists of several parts:
– the plugin export SQL file
– the static files / folder
– the pl/sql package
And if you want to share your plugin or use it in another application you have to import all 3 parts of it!

Another disadvantage would be, if an plugin developer updates the open source plugin you have to catch up the changes by yourself, because you have changed some logic inside of the plugin before…

6 thoughts on “Oracle APEX Plugin Performance

  1. Nice. I wonder if aggregating the JavaScript files would help even more. Something I have wanted to try but don’t have a case anywhere near as good as yours – 32 plugins.
    This hints at APEX not having the right level of abstraction to do the optimization automatically, and possibly plugin packaging.

    Like

    1. Thanks John! In this example packaging the PL/SQL logic to an DB object was most effective, around 90% of the improvement comes from this change. The rest was transfering the files to web server…But I´m with you, having 1 larger file instead of multiple smaller ones would improve the network transfer time. But having the client side logic of multiple plugins inside 1 file could impact performance, thus unnecessary code is loaded and being parsed by the browser…

      Like

  2. Hi Daniel, sweet!
    I ll give it a shot, as i have a page that take 5-10sec and I m trying to figure out what makes it slow..
    My question is how can I set that timer you have on the top corner so I can do some proper testing ?

    Like

    1. Hi,

      you can either see it in the debug output of the page (server side time to render page):
      Debug Output

      Or you can use a JavaScript snippet to get the client side time:
      function loadTime() {
      var now = new Date().getTime();
      var pageLoadTime = (now - performance.timing.navigationStart) / 1000;
      console.log('Page Loading Time: ' + pageLoadTime);
      }

      Define this function in the “Function and Global Variable Declaration” part of the page, and put the function call into “Execute when Page Loads”: loadTime()
      After that you will see the time in your browser console…

      Like

  3. nice blog! I really wonder what Oracle-Apex team thinks about this. Should be nice to have a solution out-of-the-box for this simple and nice performance improvement trick.

    Like

    1. As I heard they included some caching mechanism for source code entered in APEX in 18.1, so the soft-parsing is only done once and not multiple times if you used a plugin multiple times on a page…But I have to test this new behaviour, maybe it´s not already there and only planned for a future release 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s