Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how to compile the Native PL/SQL code in Oracle 11g. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

In the Oracle environment, PL/SQL is not only the best means for us to implement business logic, but also the best language to combine with Oracle database itself. Making good use of the function of PL/SQL itself can greatly improve our work efficiency.

From the very beginning of learning about computers and programming, we have been exposed to a concept called "binary programming code". The computer can execute the binary code directly, and at present, binary is one of the fastest forms of computer execution. Other high-level languages, such as C # and Cellular languages, are converted into binary programs by compiling Compile and connecting Link procedures.

Binary programs are characterized by fast execution. But the disadvantage is also obvious, that is, for a physical machine type (such as CPU architecture), an operating system, the format definition of the binary executor is different. Therefore, for each operating system and physical platform, we theoretically need to go through the Compile and Link process to form a unique executable program.

The emergence of neutral languages, or intermediate languages, put an end to this situation. This is typical of Java and many scripting languages, and with a "platform-dependent" virtual machine software, we can make the same code run on different platforms. This is the so-called "compile at once, execute at multiple places".

By default, PL/SQL code is such an intermediate language, which can also be called 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 issue of concern. Localizing code Native is a method of program optimization that is often mentioned.

1. Nativezation of PL/SQL statement

The Nativization of the PL/SQL statement is introduced from Oracle9i. The original purpose of native is performance, and PL/SQL code in native is usually faster than interpreted form (interpreted form). The early nativezation was laborious and required us to provide the local compiler address.

The native process of the PL/SQL statement requires us to install an additional C compiler, which has security concerns in some production environments.

In the 9i and 10g times, the database included a parameter named plsql_native_library_dir that specified the location of the local compiler directory. In 11g, this parameter is canceled, and the process of code native becomes easier.

In 11g, Oracle does not need to install an additional C compiler on the server. Oracle directly converts the PL/SQL code that needs to be natived to shared library (DDL) on the server. As a result, native compilation of PL/SQL becomes simple, requiring only one switchgear. This is the Oracle parameter plsql_code_type.

When using native PL/SQL, we must pay attention to the differences in using memory objects. The machine code (machine code) corresponding to the Native PL/SQL code is mapped to PGA memory before it is called into the database catalog. The interpretation code (interpreted form code) corresponds to SGA. Therefore, when using native code, the consumption of SGA is a decreasing trend.

2. Switch parameter plsql_code_type

Starting with Oracle 11g, we can use plsql_code_type to control the compiler selection switch. We chose Oracle11g to carry out 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. Through the view user/all/dba_plsql_object_settings, we can see the compiled form used by the corresponding stored code object.

First, we compile the stored procedure using the default method.

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 compiled form of the object. For individual stored procedures, we can specify the compilation method directly in the compile process without parameter modification. It is also possible to compile the code in native mode.

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 configuration parameter plsql_code_type is relatively simple. The current version of Oracle supports two option values: Interpreted and Native. The default value is Interpreted, which means that the program code is compiled into parsed form. The other is Native, which means to compile to native code. We can configure it flexibly in 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 back in, you can compile it back to the interpreted 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

3. Performance comparison

The biggest benefit and advantage of PL/SQL localization code is performance. Especially in 11g version, the advantage of local Native is more obvious.

We choose a more resource-consuming function-Fibonne concubine series to calculate the nth item, using a recursive structure to calculate.

SQL > create or replace function fib (n number)

2 return number

3 is

4 begin

5 if (n select name, plsql_code_type from user_plsql_object_settings

NAME PLSQL_CODE_TYPE

FIB INTERPRETED

Clean up shared_pool and buffer_cache before performing the lab.

SQL > alter system flush shared_pool

System altered.

SQL > alter system flush buffer_cache

System altered.

SQL > set timing on

SQL > set serveroutput on

SQL > declare

2 n number

3 begin

4 n: = fib (40)

5 dbms_output.put_line ('Result is:' | | n)

6 end

7 /

Result is: 165580141

PL/SQL procedure successfully completed

Executed in 43.547 seconds

The execution time of PL/SQL interpretive form code is 43.55s and the result is calculated at nasty 40. Let's take a look at the situation after Nativezation.

SQL > alter function fib compile plsql_code_type=native

Function altered

Executed in 0.219 seconds

SQL > select name, plsql_code_type from user_plsql_object_settings

NAME PLSQL_CODE_TYPE

FIB NATIVE

Executed in 0.078 seconds

Perform the same calculation task for the second time.

SQL > alter system flush shared_pool

System altered.

SQL > alter system flush buffer_cache

System altered.

SQL > set timing on

SQL > set serveroutput on

SQL > declare

2 n number

3 begin

4 n: = fib (40)

5 dbms_output.put_line ('Result is:' | | n)

6 end

7 /

Result is: 165580141

PL/SQL procedure successfully completed

Executed in 25.734 seconds

The second native execution shows that the calculation is completed in 25.73s. The performance is improved by nearly half!

4. Conclusion

When we write pl/sql code, performance is a very important consideration. Native programming can improve efficiency to some extent. However, it should be noted that the Native program is conditional. The time cost saved by Native PL/SQL is at the level of the PL/SQL engine, while the SQL statement engine will not be greatly improved.

So, if our code is dominated by process, calculation, and loop judgment, and there are relatively few SQL statements, then Nativezation is more cost-effective. On the other hand, if we are mainly doing the calculation of SQL statements, even if we Native the code, we will gain less advantage.

On the other hand, Native programs may have a lot of additional problems and concerns when migrating and upgrading. It is also an aspect that we need to pay attention to.

The above is the editor for you to share how to compile the Native PL/SQL code in Oracle 11g, if you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.