Native PL/SQL code compilation in Oracle 11g

In the Oracle environment, PL/SQL is the best way for us to implement business logic, and it is also the best language to combine with the Oracle database itself. Using PL/SQL itself can greatly improve our work efficiency.
 
From the very beginning we learned about computers and programming, we came into contact with a concept called "binary code ". A computer can directly execute binary code. Currently, binary is the fastest way for a computer to execute binary code. Other advanced languages, such as C and C ++, are converted to binary programs by compiling Compile and connecting the Link process.
 
A binary program features high execution speed. However, the disadvantage is obvious, that is, for a physical machine type (such as CPU architecture) and an operating system, the format definition of binary execution programs is different. Therefore, for each operating system and physical platform, theoretically we need to perform Compile and Link processes to form a unique executable program.
 
The emergence of neutral language, or intermediate language, has ended this situation. This representativeness is Java and many scripting languages. With a "platform-related" Virtual Machine software, we can run the same code on different platforms. This is the so-called "one-time compilation, multiple executions ".
 
By default, PL/SQL code is such an intermediate language, also known as an interpreted language. The same Code is running on different platforms. Compared with Native code, the performance of parsing code has always been an important concern. Localization of code Native is a method of program optimization that people often mention.
 

-------------------------------------- Split line --------------------------------------

 

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

-------------------------------------- Split line --------------------------------------

1. Native PL/SQL statements

 

Native PL/SQL statements are introduced from Oracle9i. The initial intention of native is performance. Generally, native PL/SQL code is faster than interpreted form. Early native Development was effort-consuming and we needed to provide local compiler addresses.
 
In the native process of PL/SQL statements, we need to install an additional C compiler. In some production environments, we still have security concerns.

In the 9i and 10g era, the database includes a parameter named plsql_native_library_dir, which is used to specify the local compiler directory location. In 11g, this parameter is canceled, and the process of native code is also relatively simple.
 
In 11g, Oracle does not need to install the C compiler on the server. Oracle will directly convert native PL/SQL code to the shared library (DDL) on the server ). Therefore, local compilation of PL/SQL becomes very simple, and only one switch device is required. This is the Oracle parameter plsql_code_type.
 
When using native PL/SQL, we must pay attention to the different memory objects. The machine code corresponding to Native PL/SQL code maps to the PGA memory before being transferred to the database catalog. The interpreted code (interpreted form code) corresponds to SGA. Therefore, when using native code, the consumption of SGA is reduced.
 
 

2. Switch Parameter plsql_code_type

 

From Oracle 11g, we can use plsql_code_type to control the compiler selection switch. We chose Oracle11g for the experiment.

 

 

SQL> select * from v $ version;

 

BANNER

--------------------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

 

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

 

 

Related Parameters

 

 

SQL> show parameter plsql_code

NAME TYPE VALUE

-----------------------------------------------------------------------------

Plsql_code_type string INTERPRETED

 

 

By default, Oracle is compiled in the form of interpreted code. In the user/all/dba_plsql_object_settings view, we can see the compilation form used for storing the code object.
 
First, we use the default method to compile the stored procedure.

 

 

SQL> create or replace procedure P_RECE_CALL_TEST is

2 I number;

3 c number;

4 begin

5 for I in 1 .. 100 loop

6 select count (*) into c from emp;

7 dbms_output.put_line (to_char (c ));

8 end loop;

9 end P_RECE_CALL_TEST;

10/

 

Procedure created

 

SQL> select name, plsql_code_type from user_plsql_object_settings;

 

NAME PLSQL_CODE_TYPE

--------------------------------------------------

P_RECE_CALL_TEST INTERPRETED

 

 

The plsql_code_type column of the Code object shows the object compilation form. For a separate stored procedure, we can directly specify the compilation method in the compile process without modifying the parameters. In this way, the code can be compiled into native.
 
 

 

SQL> alter procedure p_rece_call_test compile plsql_code_type = native;

Procedure altered

 

SQL> select name, plsql_code_type from user_plsql_object_settings;

NAME PLSQL_CODE_TYPE

--------------------------------------------------

P_RECE_CALL_TEST NATIVE

 

 

The plsql_code_type configuration parameter is relatively simple. Currently, Oracle supports Interpreted and Native options. The default value is Interpreted, indicating that the program code is compiled into a parsing format. The other is Native, which indicates compiling as local code. We can perform flexible configuration at the session level.
 
 

 

SQL> alter session set plsql_code_type = 'native ';

Session altered

 

SQL> alter procedure p_rece_call_test compile;

Procedure altered

 

SQL> select name, plsql_code_type from user_plsql_object_settings;

 

NAME PLSQL_CODE_TYPE

--------------------------------------------------

P_RECE_CALL_TEST NATIVE

 

 

After logging on again, you can compile it back to the explanatory state.

 

 

SQL> conn scott/tiger @ ora11g;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as scott

 

SQL> alter procedure p_rece_call_test compile;

Procedure altered

 

SQL> select name, plsql_code_type from user_plsql_object_settings;

NAME PLSQL_CODE_TYPE

--------------------------------------------------

P_RECE_CALL_TEST INTERPRETED

For more details, please continue to read the highlights on the next page:

1 2 Next Page