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