Menu
Are there are tangible benefits in changing the PLSQL_CODE_TYPE from interpreted to native? Was wondering if there are any case studies on the same.
If benefit justifies the change, how can I go about implementing the same ?
My platform is Windows 2003 server, running Oracle 10gR2 (10.2.0.1.0) if that helps.
982
PL/SQL executes in a virtual machine, and it first translates (compiles) your code into virtual machine code, sometimes called bytecode or mcode. This is basically the same model that Java uses. When it is time to actually run your code, however, that bytecode is translated (interpreted) into system calls.
Native compilation is the process by which a stored PL/SQL program is compiled into native code that does not need to be interpreted at runtime. In comparison, code that is not natively compiled is stored in an intermediate form that must be interpreted at runtime.
Using an Oracle tool, such as SQL*Plus. Calling it explicitly in the code of a database application, such as an Oracle Forms or precompiler application. Calling it explicitly in the code of another procedure or trigger.
The benefit should be speed. Whether it is 'tangible' depends on whether PL/SQL performance is an issue for you. It won't give any benefit on the SQL side (eg SELECTing) or if you have latency issues elsewhere (eg calling web services).
Unless you are doing some computationally heavy tasks in PL/SQL, I suspect you won't notice a difference. I'd be much more concerned about running the database without the appropriate patches, so would recommend you look to applying the patchsets to get you to 10.2.0.4
109
Just to supplement Gary's excellent answer (which I voted up) here is some further information from the Oracle documentation
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#LNPLS01209
5
In addition to the excellent answers already given, unless you really need to switch to Native now, I would recommend waiting until you migrate to Oracle 11g. Here is a relevant paragraph from the documentation:
Starting with Oracle Database 11g, PL/SQL Native Compilation does not need a C compiler. Therefore, if you presently use a C compiler only to support PL/SQL Native Compilation, you can remove it from the machine where your database is installed (and from each node in an Oracle RAC configuration).
We have switched to Native compilation in our 11g database, but we don't do a lot of computationally intensive PL/SQL, so our performance gains have been almost insignificant. We hope to have code in the future that will take greater advantage of it. On the plus side it hasn't caused us any problems and was easy to do.
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
