在Itpub上有朋友问到这样一个问题:
拿expert 的例子在10g上测验绑定变量的问题.
发现如果不使用绑定慢许多(这个没问题),我们的机器肯定要好于当时作者的机器,(但是)结果(却比作者慢很多)如下:
不使用绑定变量要36.33秒..
使用绑定变量0.50秒..
而作者的测试分别为:
不使用绑定变量14.86 seconds...
使用绑定变量1.27 seconds...
到底是什么原因差距这么大?
首先我们需要知道，使用绑定变量，降低硬解析，通常可以提高系统的性能。
对于这个问题，我们需要知道，从Oracle9i到Oracle10g，Oracle有一个非常显著的变化，那就是放弃RBO，全面引入了CBO。
正是这一变化导致了以上差异的存在。
在Oracle9i中，通常我们不会收集系统的统计信息，这使得对于all_objects的查询会使用RBO，而在Oracle10g中，缺省的会使用CBO优化器。
对于反复的硬解析，统计信息、柱状图信息的判断以及执行计划的选择使得性能急剧下降。
下面我的测试结果中，你可以清楚的看到这些差别，在增加了rule提示以后，速度明显增加：
1.不使用绑定变量
大约用了27秒
[oracle@danaly udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 31 10:31:47 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> alter system flush shared_pool; System altered. SQL> set serveroutput on
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select object_name
11 from all_objects
12 where object_id = ' || i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
18 ' seconds...' );
19 end;
20 /
27.01 seconds... PL/SQL procedure successfully completed. |
2.增加Rule提示
只需16秒多一点.
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select /*+ rule */ object_name
11 from all_objects
12 where object_id = ' || i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
18 ' seconds...' );
19 end;
20 /
16.56 seconds... PL/SQL procedure successfully completed. |
3.使用绑定变量
使用绑定变量方式速度非常的快，只需要.19秒。
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select object_name
11 from all_objects
12 where object_id = :x'
13 using i;
14 fetch l_rc into l_dummy;
15 close l_rc;
16 end loop;
17 dbms_output.put_line
18 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
19 ' seconds...' );
20 end;
21 /
.19 seconds... PL/SQL procedure successfully completed. SQL> |
-----
