Local compilation of Oracle stored procedures

The Oracle stored procedure is usually compiled as a test record for local compilation.

Test Table:

SQL> create table t1(rid number); Table created SQL> create table t_n(rid number); Table created

Stored Procedure for testing:

create or replace procedure pro_xcl(p1 varchar2)isbegin    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:

SQL> set serveroutput onSQL> set timing on -- view the current compilation method of the Stored Procedure SQL> select plsql_code_type from all_plsql_object_settings where name = 'Pro _ XCL '; PLSQL_CODE_TYPE--------------------------------------------------------------------------------INTERPRETED Executed in 0.14 secondsSQL> exec pro_xcl ('11g interpreted'); 11g interpreted pl/SQL procedure successfully completed Executed in 4.68 seconds

Under Change, replace t1 with t_n table with pro_xcl.

Test the running speed of stored procedures compiled locally.

-- Use local compilation to compile the Stored procedure pro_xclSQL> alter Procedure pro_xcl compile plsql_code_type = native; procedure altered Executed in 0.062 seconds -- view the current compilation method of the stored Procedure, the local compilation method has been changed to SQL> select plsql_code_type from all_plsql_object_settings where name = 'Pro _ XCL '; PLSQL_CODE_TYPE--------------------------------------------------------------------------------NATIVE Executed in 0.063 secondsSQL> exec pro_xcl ('11g NATIVE '); 11g native pl/SQL procedure successfully completed Executed in 4.087 seconds

Local compilation is faster than 0.6 seconds.

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

Alter session set plsql_code_type = native;

Set the default database program to the local compilation method:

Alter system set plsql_code_type = native;

Parameters related to PLSQL Compilation:

SQL> show parameter plsql NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------plsql_ccflags                        string      plsql_code_type                      string      INTERPRETEDplsql_debug                          boolean     FALSEplsql_optimize_level                 integer     2plsql_v2_compatibility               boolean     FALSEplsql_warnings                       string      DISABLE:ALL

MAIL: xcl_168@aliyun.com

BLOG: http://blog.csdn.net/xcl168