Oracle預存程序本地編譯方式

 　　通常將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