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) 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;测试:
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表。
测试本地编译方式出来的存储过程运行速度.
-- 用本地编译方式编译存储过程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编译相关一些参数:
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
MAIL: [email protected]
BLOG: http://blog.csdn.net/xcl168
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。