Oracle stored procedures Native compilation method

Oracle stored procedures are typically compiled into test records that are compiled locally.

Test table:

?

1 2 3 4 5 6 7

sql> CREATE TABLE T1 (RID number);    Table created sql> CREATE TABLE t_n (RID number); Table created

Stored Procedures for testing:

?

1 2 3 4 5 6 7 8 9 10 11 12-13

Create or Replace procedure Pro_xcl (P1 varchar2) is begin Dbms_output.put_line (p1);     INSERT INTO T1 select RowNum as RR from dual connect by rownum < 1000000;      Commit Exception when others then Dbms_output.put_line (sqlcode| | ': ' | |  SUBSTR (sqlerrm,200)); End

Test:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17-18

Sql> set serveroutput on sql> set timing on--View the current compilation mode of the stored procedure sql> select Plsql_code_type from All_plsql_object_set    Tings where name= ' pro_xcl ';    Plsql_code_type--------------------------------------------------------------------------------interpreted    Executed in 0.14 seconds sql> exec pro_xcl (' 11g interpreted '); 11g interpreted Pl/sql procedure successfully completed executed in 4.68 seconds

Change, PRO_XCL, replace T1 with T_n table.

Test the speed at which stored procedures come out of the local compilation method.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22-23

-Compile stored procedures with local compilation PRO_XCL sql> alter procedure PRO_XCL compile plsql_code_type=native; Procedure altered executed in 0.062 seconds--View the current compilation of the stored procedure, as you can see, has become local compilation sql> Select Plsql_code_type from All_pls    ql_object_settings where name= ' pro_xcl '; Plsql_code_type--------------------------------------------------------------------------------NATIVE executed    In 0.063 seconds sql> exec pro_xcl (' 11g NATIVE '); 11g NATIVE pl/sql procedure successfully completed executed in 4.087 seconds

The local compilation method must be fast 0.6 seconds, not fast to the imagination exaggeration.

Sets the current session program compilation method by default to local compilation:

Alter session set Plsql_code_type=native;

To set the database default program to compile locally:

alter system set plsql_code_type=native;

Some parameters related to the Plsql compilation:

?

1 2 3 4 5 6 7 8 9 ten

sql> show parameter Plsql    name                                   type        VALUE----------------------------------------- ------------------------------------plsql_ccflags                         string       plsql_code_type                       string      interpreted plsql_debug                           boolean     FALSE plsql_optimize_level                  integer      2 plsql_v2_compatibility                boolean     FALSE plsql_warnings                        string       disable:all