通常将Oracle存储过程编译为本地编译方式的测试记录.

　　测试用表:

?

1 2 3 4 5 6 7 SQL> create table t1(rid number);    Table created    SQL> create table t_n(rid number);    Table created

　　测试用的存储过程:

?

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;

　　测试:

?

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 --查看存储过程当前编译方式 SQL>  select plsql_code_type from all_plsql_object_settings 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

　　更改下，pro_xcl，将t1换成t_n表。

　　测试本地编译方式出来的存储过程运行速度.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 -- 用本地编译方式编译存储过程pro_xcl SQL> alter procedure pro_xcl compile plsql_code_type=native;    Procedure altered    Executed in 0.062 seconds  --查看存储过程当前编译方式,可看到，已变成本地编译方式了 SQL>  select plsql_code_type from all_plsql_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

　　本地编译方式要快0.6秒，没有快到想象的夸张的地步。

　　设置当前session程序编译方式默认为本地编译:

　　alter session set plsql_code_type=native;

　　设置数据库默认程序为本地编译方式:

　　alter system set plsql_code_type=native;

　　与PLSQL编译相关一些参数：

?

1 2 3 4 5 6 7 8 9 10 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