I had an email exchange with a contact at this same customer this week. He did mention the lack of "APEX skills" in the organization, but as I candidly told him, the vast majority of their issues were a lack of Oracle skills, not APEX skills. They had many people, not necessarily proficient in data modeling or SQL performance tuning or Oracle, building applications intended to service thousands of concurrent end users. As I told him:
I wouldn't focus too much on "APEX skills". The problems you've had at your company are "Oracle skills". How to design a data model. How to write and tune efficient SQL access. How to be aware of data security aspects. How to author and maintain secure, efficient PL/SQL. APEX is simply the veneer over a database application.
Anytime I start a new APEX application, I actually don't touch Oracle Application Express. I usually start in SQL Developer Data Modeler and start designing the foundation of my application in a logical and efficient data model. Once I have that perfected, it's usually quite straightforward to build the Web interface in APEX on top of this.
Just today, I received another shining example of this philosophy of "Oracle skills". One of my favorite customers at a large enterprise in Europe, where they have hundreds of APEX applications deployed across the enterprise, sent me the following:
....we talked a couple of months ago about the weird visibility of APEX performance in companies. Just to give you another example, we also fight against the perception that APEX is slow, because people know that applications were built with APEX, and if the response time is bad, then APEX is slow. It's that simple. We had a complaint about a couple of APEX pages. One page took around 15 seconds to render with a complicated chart, the other page took around 3 minutes to show up.
The result of the analysis was - guess - bad DB design paired with bad SQL. They had a history table with 1.5 billion records, bad partitioning and they populated all LOV filters from this table, distinct, order by. At the end, the fact table was just 7 million records to filter from the LOV filter elements. We just built up a new table with all distinct values of the dimensions (the data is loaded only once every night) as base table to serve all filter elements. We dropped the unnecessary indexes and built a reasonable one and.....voila we were down to 2 seconds for the chart and 3 seconds for the page that took 180 seconds before.
It's very hard to explain that APEX is neither fast nor slow, because it is not doing anything relevant for itself but just query the database with the good or bad SQL that the developers provide against a good or poorly designed Oracle database, which is more or less busy with 50 other projects and data loads.
If you look at the remedy to improve the performance of their application, it was little to zero APEX and almost all Oracle knowledge and skill. By the way, creating a select list against 1.5 billion rows is going to take an equally long amount of time using .NET, PHP, Java, Jython, Perl, or whatever.
What's the intended takeaway from this blog post? Understand the architecture of APEX and how it interacts with the database, but also invest in understanding the Oracle database, SQL and database design.
