通常将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.68seconds
更改下，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 compileplsql_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.087seconds
本地编译方式要快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
NAMETYPEVALUE
------------------------------------ -----------------------------------------
plsql_ccflagsstring
plsql_code_typestringINTERPRETED
plsql_debugbooleanFALSE
plsql_optimize_levelinteger2
plsql_v2_compatibilitybooleanFALSE
plsql_warningsstringDISABLE:ALL
