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 supports native compilation since version 9 and thanks to some changes in recent versions, it's super easy to use.

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!