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

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.

24 comments:

  1. Joel,

    Excellent post.

    Do you have any figures to hand of the sort of difference in timings this made?

    ReplyDelete
  2. John,

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

    Joel

    ReplyDelete
  3. Joel,

    I followed all steps and here is my result :

    COUNT(*)OBJECT_TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
    --------------------------------------------------------
    12 FUNCTION 2 NATIVE
    358 PACKAGE 2 NATIVE
    350 PACKAGE BODY 2 NATIVE
    19 PROCEDURE 2 NATIVE
    1 TABLE 2 NATIVE
    243 TRIGGER 2 NATIVE
    4 TYPE 2 INTERPRETED

    I wonder why Types are still Interpreted.
    I use Oracle 11G.

    Thanks.
    Georges

    ReplyDelete
  4. Georges,

    I am getting the same exactly.

    COUNT(*)OBJECT_TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
    --------------------------------------------------------
    12 FUNCTION 2 NATIVE
    358 PACKAGE 2 NATIVE
    350 PACKAGE BODY 2 NATIVE
    19 PROCEDURE 2 NATIVE
    1 TABLE 2 NATIVE
    243 TRIGGER 2 NATIVE
    4 TYPE 2 INTERPRETED

    ReplyDelete
  5. Same with me.
    4 types are still interpreted.

    Maybe the difference between apex 3.1 and 3.2, since Joel did get them natively compiled? Unless off course Joel faked his query results ;-)

    Anyway, on my "try what you like, I don't care databse" I notice a very pleasing increase in performance.
    Both developing and using the application

    I'm definately going to sugest to do the same for the 'real' databases.

    Erik

    ReplyDelete
  6. 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:

    alter session set plsql_optimize_level = 2;
    alter session set plsql_code_type = native;
    begin
    for c1 in (select type_name, owner from dba_types where owner = 'APEX_030200') loop
    execute immediate 'alter type ' || c1.owner || '.' || c1.type_name || ' compile';
    end loop;
    end;
    /

    ReplyDelete
  7. It works now Joel
    all of them compiled.
    Thank you

    ReplyDelete
  8. I had the same issue with the types being left as interpreted. My machine was 11.1.0.6 on Enterprise Linux in the cloud. Your workaround worked perfectly for me.

    ReplyDelete
  9. Excellent post Joel. I would be very interested in any metrics you have on the performance increase. Thanks for posting this though.

    Duncs

    ReplyDelete
  10. Hello Joel,

    i've try this. The interpreted files are saved in the correct dir.

    But if i access my apex-application an error 404 result. The apache-error-log says:

    ....mod_plsql: /pls/htmldb/f HTTP-404 \nf: PROCEDURE DOESN'T EXIST

    Oracle 10.2.0.4, Apex 3.1.0.00.32

    Whats wrong?

    ReplyDelete
  11. @Carsten,

    I don't really know. What was your optimization level? What do you mean by "The interpreted files are saved in the correct dir."?

    Joel

    ReplyDelete
  12. Hi Joel,

    if i run: "exec dbms_utility.compile_...." the files *.c are saved to the "plsql_native_library_dir" correctly.

    The optimization level is 2, the same as yours example.

    Carsten

    ReplyDelete
  13. Hi Carsten,

    Short of doing it myself with your specific APEX Version and database version, I'm not sure at this time.

    Joel

    ReplyDelete
  14. Joel,

    after i run "exec dmbs_utility.compile_schema..." more than 700 objects (views, triggers) are invalid.

    ReplyDelete
  15. 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.

    ReplyDelete
  16. Joel,
    Any news on what caused the ora-600 errors? I'm interested in seeing how much native compiling would benefit APEX applications.
    Todd

    ReplyDelete
  17. i think it does not work on Oracle XE.
    I got a lot of invalid APEX views and APEX is not running any more

    ReplyDelete
  18. Joel,

    I followed your steps, and I too was unable to get the TYPES to compile. When I run the additional script as shown below, I get the error message also below.

    This is running on an Oracle 10g Enterprise 10.1.0.5.

    begin
    for c1 in (select type_name, owner from dba_types where owner = 'FLOWS_030100') loop
    execute immediate 'alter type ' || c1.owner || '.' || c1.type_name || ' compile';
    end loop;
    end;


    --select type_name, owner from dba_types where owner = 'FLOWS_030100';
    Error at line 22
    ORA-02311: cannot alter with COMPILE option a valid type with type or table dependents
    ORA-06512: at line 3

    Also, when just running the compile_schema code sample, my apex applicaitons wouldn't run at all.

    Any ideas?

    ReplyDelete
  19. @Kris - sorry, I really don't have any guidance there. This is why I tried this in 11g, as it used to not be so simply prior to 11g.

    ReplyDelete
  20. Is this possible with 10gr2?

    ReplyDelete
  21. @Drew - it's possible, but it may be slightly more involved than the instructions I've given here. I personally haven't tried it on 10gR2.

    Joel

    ReplyDelete
  22. Joel

    Is it now ok or recommened to use Native compile on Apex 11.2g databases?

    Thanks
    Dean

    ReplyDelete
  23. Hi Dean,

    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.

    Joel

    ReplyDelete
  24. Nice, I tried it on APEX 5. DB 11.2.3 where APEX has become remarkably faster..

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

    ReplyDelete