Turning customers into rockstars, with the Oracle Database, PL/SQL and Oracle Application Express.

Friday, February 20, 2009

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):

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:

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.

24 comments:

I don't. I know that seems like an obvious question, but I was a little under the gun today. Once APEX 3.2 goes out the door, I'll modify this post with actual numbers (on 11gR1 on OEL on VMWare on Vista x-64).

You had me worried that maybe I did "fake" my query results, but I checked it again - I assure you, nothing faked here. I wonder why DBMS_UTILITY.COMPIILE_SCHEMA isn't touching those TYPEs. I didn't have to do anything separately before. Either way, try this:

Excellent post, did for myself a complete change of the DB from interpreted to native, but from the Oracle documentation at: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#insertedID10 I found the following:

"During the conversion to native compilation, TYPE specifications are not recompiled by dbmsupgnv.sql to NATIVE because these specifications do not contain executable code.Package specifications seldom contain executable code so the run-time benefits of compiling to NATIVE are not measurable. You can use the TRUE command-line parameter with the dbmsupgnv.sql script to exclude package specs from recompilation to NATIVE, saving time in the conversion process."

So these objects in the db that do not contain executable code don't profit from conversion from interpreted to native.

Good question. To be honest, we have not enabled it on apex.oracle.com because I've gun been shy ever since the issues we had in 2009, when we had to disable it.

In talks with the PL/SQL group, they indicated that of all of the permutations (native code calling interpreted, interpreted code calling native, etc.), the most expensive is for native code to call interpreted. And thus...they recommended that all PL/SQL in the database be natively compiled. There is a (probably unsupported) script in $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql that can be used to facilitate this.

So....do we recommend this? I personally don't have enough evidence to recommend one way or the other. But I'd consider trying it again.

About Me

My name is Joel Kallman. I am the Director of Software Development at Oracle, and I manage the development and product management of Application Express (APEX) at Oracle. I've been at Oracle since 1996, and my VP Mike Hichwa and I created Application Express in 1999. My passion is to make customers extraordinarily successful with the Oracle Database, PL/SQL and Oracle Application Express.