Geek Answers Handbook

Oracle PL / SQL: Are there any advantages when changing PLSQL_CODE_TYPE from interpreted to native?

Are there any tangible advantages when changing PLSQL_CODE_TYPE from interpreted to native? I wonder if there are any case studies on the same.

If an advantage justifies change, how can I implement it?

My platform is a Windows 2003 server running with Oracle 10gR2 (10.2.0.1.0) if that helps.

+6
performance oracle plsql
source share
3 answers

The advantage should be quick. Regardless of whether it is “tangible,” it depends on whether PL / SQL performance is a problem for you. This will not bring any benefit to the SQL side (e.g. SELECT), or if you have problems with latency elsewhere (e.g. calling web services).

If you are not performing any complex computational tasks in PL / SQL, I suspect that you will not notice the difference. I would be much more worried about working with the database without the appropriate patches, so we recommend that you take a look at applying the patches so that you can get 10.2.0.4

+10
source share

Just to complement Gary's great answer (which I voted) here is some more info from Oracle documentation

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#LNPLS01209

+4
source share

In addition to the excellent answers already received, if you really don't need to switch to Native now, I would recommend waiting until you upgrade to Oracle 11g. Here is the relevant paragraph from the documentation:

Starting with Oracle Database 11g, the PL / SQL Native Collection does not need the C compiler. Therefore, if you currently use the 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 the Oracle RAC configuration).

We switched to Native compilation in our 11-gram database, but we do not do much computationally intensive PL / SQL, so our performance gains were almost negligible. We hope that in the future we will be able to use the code in the future. On the positive side, this did not cause us any problems and was easy to do.

+2
source share

More articles:


All Articles