8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

PL/SQL Native Compilation

When PL/SQL is loaded into the server it is compiled to byte code before execution. The process of native compilation converts PL/SQL stored procedures to native code shared libraries which are linked into the kernel resulting in performance increases for the procedural code. The extent of the performance increase depends on the content of the PL/SQL. The compilation process does not affect the speed of database calls, only the procedural logic around them such as loops and calculations.

Before PL/SQL can be natively compiled following parameter file parameters must be set.

ALTER SYSTEM SET plsql_native_make_utility = 'make';
ALTER SYSTEM SET plsql_native_make_file_name = 
  '/u01/app/oracle/product/9.2.0.1.0/plsql/spnc_makefile.mk';
ALTER SYSTEM SET plsql_native_library_dir='/u01/oradata/DEV';

Once these parameters are set the compilation style can be switched using the following.

ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';
ALTER SESSION SET plsql_compiler_flags = 'NATIVE';

The following test is an example of the performance gains available for pure procedural code.

ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';

CREATE OR REPLACE PROCEDURE test_speed AS
  v_number  NUMBER;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    v_number := i / 1000;
  END LOOP;
END;
/

SET TIMING ON
EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.00

ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
ALTER PROCEDURE test_speed COMPILE;

EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

From this you can see that a basic procedural operation can be speeded up significantly. In this example the natively compiled code takes approximately 1/20 of the time to run.

The speed of database calls is unaffected by native compilation so the performance increases of most stored procedures will not be so dramatic. This is illustrated by the following example.

ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';

CREATE OR REPLACE PROCEDURE test_speed AS
  v_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    SELECT SYSDATE
    INTO   v_date
    FROM   dual;
  END LOOP;
END;
/

EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:02:21.04

ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
ALTER PROCEDURE test_speed COMPILE;

EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:02:19.04

Once again, the procedural elements of the stored procedure show a performance increase but the overall result is not so impressive.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.