Problem

Faster PL/SQL code by using native compiler

When working with Oracle, you'll probably care about performance, it makes users happy and keeps the cost low.

Oracle support native compilation version 9 and thanks to some changes in recent versions, it's super easy to use.

Solution

Recipe #1 - Native PL/SQL compilation

Let's start by creating a dummy method:

CREATE OR REPLACE PROCEDURE perf_test
AS
   n_ NUMBER;
BEGIN
   FOR i_ IN 1..500000000 LOOP
      n_ := n_ + 1;
   END LOOP;
END;

After you've created this method, it will be executed as interpreted code by default.

However, changing it to native code is easy, just execute the following command:

ALTER PROCEDURE perf_test compile plsql_code_type=native;

If you want to switch back, specify "interpreted" instead of "native" and things will probably be slower.

You can specify PLSQL_CODE_TYPE=NATIVE in your spfile to use native mode by default.

Have fun with your faster database!