Make all of your APEX applications run a bit faster

See the important update below

Interested in making your APEX applications run faster? I know this seems like an impossible and astonishing feat, and you'll soon be approaching page view execution times of zero, but you can squeeze even a little more throughput and scalability with this one small exercise. And this shouldn't cost you an extra cent.

As a lot of people know already, Oracle Application Express is essentially one big SQL and PL/SQL program. "Porting" of Oracle Application Express to other platforms is not necessary. It installs via SQL*Plus. It runs where PL/SQL does. And PL/SQL, truly, is a write-once-run-everywhere platform.

So how do you make a PL/SQL program run faster? Through native compilation of PL/SQL, of course. When you compile a module in PL/SQL, you are converting it to an intermediate form named system code (or bytecode). At runtime, this system code is interpreted. Execution of this program would be much faster if it were compiled natively and the interpretation step was bypassed altogether. This is analogous to the old days of taking an interpreted BASIC program and compiling it to a native program.

An excellent description of PL/SQL native compilation can be found in Oracle Database PL/SQL Language Reference. When PL/SQL native compilation was introduced in Oracle Database 9iR1 and 9iR2, I found it to be complicated and involved, and I think I was successful getting a small program to ncomp once (and only once). Here is the explanation from some poor guy who figured out all the steps to do this in 9iR2 on Windows. But in Oracle Database 11gR1, this is downright trivial.

My test below was done in an Oracle Database 11gR1 11.1.0.6 on Oracle Enterprise Linux on VMWare Server on a Windows Vista x-64 host. With all those layers of software, the performance difference at runtime could still be easily observed. Also, I did this with the soon-to-be-released Application Express 3.2. Wherever you see APEX_030200, replace it with the database user of your specific APEX release (e.g., APEX 3.1 = FLOWS_030100).

The database view DBA_PLSQL_OBJECT_SETTINGS provides information about the compiler settings for all stored objects in the database. Connect as SYS via SQL*Plus or SQL Developer and run the following query (remembering again to replace 'APEX_030200' if you're not running Application Express 3.2):

column plsql_optimize_level format 999
column plsql_code_type format a20
select count(*), o.object_type, s.plsql_optimize_level, s.plsql_code_type
  from dba_objects o, dba_plsql_object_settings s
 where o.object_name = s.name
   and o.owner = 'APEX_030200'
   and s.owner = o.owner
 group by o.object_type, s.plsql_optimize_level, s.plsql_code_type
 order by 2 asc

On my instance this returned:

COUNT(*) OBJECT_TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
---------- ------------------- -------------------- --------------------
      12 FUNCTION                               2 INTERPRETED
     370 PACKAGE                                2 INTERPRETED
     362 PACKAGE BODY                           2 INTERPRETED
      19 PROCEDURE                              2 INTERPRETED
       1 TABLE                                  2 INTERPRETED
     366 TRIGGER                                2 INTERPRETED
       4 TYPE                                   2 INTERPRETED

7 rows selected.

All PL/SQL objects are interpreted and have a PL/SQL optimization level of 2. You can alter the PL/SQL compiler optimization level via PLSQL_OPTIMIZER_LEVEL, but I encountered runtime errors in Application Express when I natively compiled with an optimizer level of 3. I don't know why, but I'm saving that for another day.

Recompiling all of these objects via native compilation can be done with three easy statements. Note: You should not do this while the APEX applications are actively being used, as these steps will recompile all of the objects in the schema and you could encounter object contention issues. Connect as SYS in SQL*Plus and run:

alter session set plsql_optimize_level = 2;
alter session set plsql_code_type = native;
exec dbms_utility.compile_schema('APEX_030200');

That's it! If you execute the query above, you should now see something like:

COUNT(*) OBJECT_TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
---------- ------------------- -------------------- --------------------
      12 FUNCTION                               2 NATIVE
     370 PACKAGE                                2 NATIVE
     362 PACKAGE BODY                           2 NATIVE
      19 PROCEDURE                              2 NATIVE
       1 TABLE                                  2 NATIVE
     366 TRIGGER                                2 NATIVE
       4 TYPE                                   2 NATIVE

7 rows selected.

If you encounter errors and you want to revert back to what you had, run:

alter session set plsql_optimize_level = 2;
alter session set plsql_code_type = interpreted;
exec dbms_utility.compile_schema('APEX_030200');

But I think you'll be pleasantly surprised with the results and have no desire to revert back. In Database 11gR1, this has become a downright trivial exercise. And faster page views means greater throughput which means greater scalability on equivalent hardware. That's both green and economical.

Lastly, you might wonder if the hosted instance of Application Express at http://apex.oracle.com is running with natively compiled PL/SQL. It's not, but after we formally release Application Express 3.2, it will be. There's no reason not to.



Important Update

08-APR-2009: There's nothing like actually using and testing these features on a large-scale system. A few weeks ago, I had natively compiled the APEX engine on apex.oracle.com. But just this past week, we had to switch this back to interpreted. Some unexplained ORA-600 errors were being encountered which is being actively researched by the database development team.