Oracle預存程序本地編譯方式

     通常將Oracle預存程序編譯為本地編譯方式的測試記錄.

  測試用表:   

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

測試用的預存程序:

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;

測試: 

SQL> set serveroutput onSQL> 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 secondsSQL> exec pro_xcl('11g INTERPRETED'); 11g INTERPRETED PL/SQL procedure successfully completed Executed in 4.68 seconds

  更改下，pro_xcl，將t1換成t_n表。

  測試本地編譯方式出來的預存程序運行速度.

-- 用本地編譯方式編譯預存程序pro_xclSQL> 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 secondsSQL> 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編譯相關一些參數：　　

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