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