比较plsql native编译的性能

显示全部楼层 · 2012-5-21 10:19:41

SQL> create or replace procedure na
2as
3x number;
4begin
5for i in 1..1e8 loop
6x:=x+i;
7end loop;
8dbms_output.put_line(x);
9end;
10/
过程已创建。
已用时间:00: 00: 00.17
SQL> set serverout on
SQL> exec na;
PL/SQL 过程已成功完成。
已用时间:00: 00: 11.60
SQL> alter procedure na compile plsql_code_type=native;
过程已更改。
已用时间:00: 00: 00.10
SQL> exec na;
PL/SQL 过程已成功完成。
已用时间:00: 00: 09.68
SQL>

千问 · 2012-5-21 10:19:41

刚才忘记赋初值
SQL> create or replace procedure na
2as
3x number:=0;
4begin
5for i in 1..1e8 loop
6x:=x+i;
7end loop;
8dbms_output.put_line(x);
9end;
10/
过程已创建。
已用时间:00: 00: 00.03
SQL> exec na;
5000000050000000
PL/SQL 过程已成功完成。
已用时间:00: 00: 16.31
SQL> alter procedure na compile plsql_code_type=native;
过程已更改。
已用时间:00: 00: 00.01
SQL> exec na;
5000000050000000
PL/SQL 过程已成功完成。
已用时间:00: 00: 14.61

千问 · 2012-5-21 10:19:41

8位整数
D:\app>add
sum=4999999950000000,time=249
D:\app>type add.cpp
#include
#include
int main()
{
__int64 i,x=0;
long t=clock();
for (i=0;iadd1
sum=4999999950000000.000000,time=748
D:\app>type add1.cpp
#include
#include
int main()
{
__int64 i;
double x=0;
long t=clock();
for (i=0;i create or replace procedure Euler_Pi_Number is
2subtype My_Number is number;
3zero constant My_Number := 0.0;
4one constant My_Number := 1.0;
5two constant My_Number := 2.0;
6six constant My_Number := 6.0;
7toler constant My_Number := 0.00000000001;
8root_toler constant My_Number := toler/1000.0;
9root My_Number;
10prev_root My_Number;
11prod_over_six My_Number;
12prod My_Number;
13pi My_Number;
14prev_pi My_Number;
15step My_Number;
16
17begin
18pi := one;
19prev_pi := zero;
20prod_over_six := zero;
21step := zero;
22while pi - prev_pi > toler
23loop
24prev_pi := pi;
25step := step + one;
26prod_over_six := prod_over_six + one/(step*step);
27prod := six*prod_over_six;
28prev_root := prod;
29root := prod/two;
30while Abs(root - prev_root) > root_toler
31loop
32prev_root := root;
33root := (root + prod/root)/two;
34end loop;
35pi := root;
36end loop;
37end Euler_Pi_Number;
38/
过程已创建。
SQL> set timi on
SQL> exec Euler_Pi_Number ;
PL/SQL 过程已成功完成。
已用时间:00: 00: 02.49
SQL> create or replace procedure Euler_Pi_Binary is
2subtype My_Number is binary_double;
3zero constant My_Number := 0.0d;
4one constant My_Number := 1.0d;
5two constant My_Number := 2.0d;
6six constant My_Number := 6.0d;
7toler constant My_Number := 0.00000000001d;
8root_toler constant My_Number := toler/1000.0d;
9root My_Number;
10prev_root My_Number;
11prod_over_six My_Number;
12prod My_Number;
13pi My_Number;
14prev_pi My_Number;
15step My_Number;
16
17begin
18pi := one;
19prev_pi := zero;
20prod_over_six := zero;
21step := zero;
22while pi - prev_pi > toler
23loop
24prev_pi := pi;
25step := step + one;
26prod_over_six := prod_over_six + one/(step*step);
27prod := six*prod_over_six;
28prev_root := prod;
29root := prod/two;
30while Abs(root - prev_root) > root_toler
31loop
32prev_root := root;
33root := (root + prod/root)/two;
34end loop;
35pi := root;
36end loop;
37end Euler_Pi_Binary;
38/
过程已创建。
已用时间:00: 00: 00.03
SQL> exec Euler_Pi_Binary;
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.43
SQL> alter procedure Euler_Pi_Binary compile plsql_code_type=native;
过程已更改。
已用时间:00: 00: 00.18
SQL> exec Euler_Pi_Binary;
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.17
SQL> alter procedure Euler_Pi_Number compile plsql_code_type=native;
过程已更改。
已用时间:00: 00: 00.03
SQL> exec Euler_Pi_Number;
PL/SQL 过程已成功完成。
已用时间:00: 00: 02.32
SQL>

千问 · 2012-5-21 10:19:41

SQL> create or replace procedure nab
2 as
3 x binary_double:=0d;
4 begin
5 for i in 1..1e8 loop
6 x:=x+i;
7 end loop;
8 dbms_output.put_line(x);
9 end;
10/
过程已创建。
已用时间:00: 00: 00.06
SQL> set serverout on
SQL> exec nab;
5.00000005E+015
PL/SQL 过程已成功完成。
已用时间:00: 00: 04.33
SQL> alter procedure nab compile plsql_code_type=native;
过程已更改。
已用时间:00: 00: 00.03
SQL> exec nab;
5.00000005E+015
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.60

千问 · 2012-5-21 10:19:41

貌似选对数据类型比本地编译更重要