Oracle存储过程本地编译方式

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

  测试用表:  

[sql] view plaincopyprint?

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

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

测试用的存储过程:

[sql] view plaincopyprint?

1. create orreplace procedure pro_xcl(p1 varchar2) 
2. is 
3. begin 
4.     dbms_output.put_line(p1); 
5.    
6.     insert into t1select rownum as rr  
7.     from dual connectby rownum < 1000000; 
8.     commit; 
9.     
10. exception 
11.   when others then 
12.      dbms_output.put_line(sqlcode||' : '||substr(sqlerrm,200));   
13. end; 

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] view plaincopyprint?

1. SQL> set serveroutputon 
2. SQL> set timing on 
3. --查看存储过程当前编译方式 
4. SQL>  select plsql_code_type from all_plsql_object_settings wherename='PRO_XCL'; 
5.   
6. PLSQL_CODE_TYPE 
7. -------------------------------------------------------------------------------- 
8. INTERPRETED 
9.   
10. Executed in 0.14 seconds 
11.  
12. SQL> exec pro_xcl('11g INTERPRETED'); 
13.   
14. 11g INTERPRETED 
15.   
16. PL/SQL procedure successfully completed 
17.   
18. Executed in 4.68 seconds 

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表。

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

[sql] view plaincopyprint?

1. -- 用本地编译方式编译存储过程pro_xcl 
2. SQL> alter procedure pro_xcl compile plsql_code_type=native; 
3.   
4. Procedure altered 
5.   
6. Executed in 0.062 seconds 
7. --查看存储过程当前编译方式,可看到，已变成本地编译方式了 
8. SQL>  select plsql_code_type from all_plsql_object_settings wherename='PRO_XCL'; 
9.   
10. PLSQL_CODE_TYPE 
11. -------------------------------------------------------------------------------- 
12. NATIVE 
13.   
14. Executed in 0.063 seconds 
15.  
16.  
17. SQL> exec pro_xcl('11g NATIVE'); 
18.   
19. 11g NATIVE 
20.   
21. PL/SQL procedure successfully completed 
22.   
23. Executed in 4.087 seconds 

-- 用本地编译方式编译存储过程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] view plaincopyprint?

1. SQL> show parameter plsql 
2.   
3. NAME                                 TYPE        VALUE 
4. ------------------------------------ ----------- ------------------------------ 
5. plsql_ccflags                        string       
6. plsql_code_type                      string      INTERPRETED 
7. plsql_debug                          boolean     FALSE 
8. plsql_optimize_level                 integer     2 
9. plsql_v2_compatibility               boolean     FALSE 
10. plsql_warnings                       string      DISABLE:ALL