PLSQL Native Compilation

By default, PL/SQL code is compiled and stored in the form of byte code ready for execution. During the execution process, this byte code is interpreted, a process which requires time and resources. The process of native compilation converts PL/SQL stored procedures to Pro*C, which is then compiled to native code shared libraries, resulting in performance increases for the procedural code. The extent of the performance increase depends on the content of the PL/SQL, with the best results shown in code containing lots of loops, logic, mathematical operations and comparatively less database work.

In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE, rather than the default value of INTERPRETED, code is compiled directly to machine code and stored in the SYSTEM tablespace. When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.

Remember, native compilation will improve the speed of procedure code, but has no effect on the performance of SQL. When code performs lots of mathematical operations, like the SIMPLE_INTEGER example, native compilation can produce considerable performance improvements. If code is predominantly performing SQL, little improvement will be noticed.

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

SQL> show parameter PLSQL_CODE_TYPE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

SQL> SET TIMING ON
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.74
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.74

SQL> alter system set PLSQL_CODE_TYPE='NATIVE' SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
STARTUP
Database closed.
Database dismounted.

ORACLE instance shut down.
SQL>
ORACLE instance started.

Total System Global Area 2781020160 bytes
Fixed Size                  2251456 bytes
Variable Size            1711277376 bytes
Database Buffers         1061158912 bytes
Redo Buffers                6332416 bytes
Database mounted.

Database opened.
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> SET TIMING ON
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.72
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.73
SQL>
SQL> CREATE OR REPLACE PROCEDURE test_speed AS
  2    v_number  NUMBER;
  3  BEGIN
  FOR i IN 1 .. 10000000 LOOP
    v_number := i / 1000;
  END LOOP;
  4    5    6    7  END;
  8  /

Procedure created.

Elapsed: 00:00:00.15
SQL> SET TIMING ON
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL>

🙂

http://www.dba-oracle.com/t_compiled_pl_sql.htm
http://www.oracle-base.com/articles/10g/plsql-enhancements-10g.php#plsql_native_compilation

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s