Oracle APEX Plugin Performance

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: [code language="text"] Load JavaScript file=demo/r/140/files/plugin/53116667758949585/v1/select2-apex.js [/code] 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...