Friday, February 27, 2009

Oracle Application Express 3.2 released

Today, Oracle Application Express 3.2 was made available for download off of the Oracle Technology Network.

If you go to http://apex.oracle.com, you'll find links to:

  1. The link to download Oracle Application Express 3.2
  2. The list of new features in Oracle Application Express 3.2
  3. New Oracle By Examples, including a an Oracle Forms to Oracle APEX OBE and a soon-to-be-released security OBE.

Thanks to the Oracle Application Express community for your continued support, ideas and enthusiasm.

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.

Tuesday, February 17, 2009

Carl Backstrom - Oracle ACE

Thanks to the work of Sharon on our team, she was able fulfill one of the desires Carl Backstrom had expressed a couple times.

The Oracle ACE program was "designed to recognize and reward members of the Oracle Technology and Applications communities for their contributions to those communities. These individuals are technically proficient (when applicable) and willingly share their knowledge and experiences." This really epitomized Carl - he was always ready and willing to share his vast knowledge with any one.

Well, as luck would have it, the folks at the Oracle Technology Network decided that Oracle employees could no longer be awarded the ACE designation. And that bothered Carl a little bit, as he was a very active contributor to the community every day. It just would have been nice to receive that designation.

Thanks to Sharon's suggestion, this has now been achieved: http://forums.oracle.com/forums/profile.jspa?userID=354238

Wednesday, February 11, 2009

apex.oracle.com upgraded to Application Express 3.2

Today (Wednesday, 11-FEB-2009) I upgraded apex.oracle.com to Application Express 3.2.0.00.21. As our long time customers know, this is one of the last milestones in our cycle prior to production release.

You can go here for a brief introduction to the new features in Application Express 3.2. As you'll see, there are two primary themes - Forms Conversion and security. The Application Express 3.2 documentation is not staged yet, but the online help/documentation is current for APEX 3.2.

If you encounter odd behavior or you feel that something was not properly upgraded in your application, please feel free to report it on the APEX OTN discussion forum. We will watch this closely.

Thank you for all of your support.