CBO RBO Oracle10g and Speed

« 谁偷走了一秒光阴? | Blog首页 | 我的2005年 »

在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>

历史上的今天...
>> 2013-12-31文章:

新年献礼：《深入解析Oracle》一书开源下载

>> 2011-12-31文章:

2011年年终总结以及年终这一天

>> 2010-12-31文章:

天道酬勤 - 从头细数来时路（Eygle的职业生涯）

>> 2009-12-31文章:

Kamus 同学将在2010年正式加盟恩墨科技

>> 2008-12-31文章:

2008 年终总结 - 从头细数来时路

元旦献礼-《深入解析Oracle》第一、四章下载

>> 2007-12-31文章:

2008 新年快乐

>> 2006-12-31文章:

新年快乐我的朋友们

>> 2004-12-31文章:

新年快乐-一个非常漂亮的Flash

天下无贼后传-也是对2004年的总结

By eygle on 2005-12-31 15:27 | Comments (5) | Oracle12c/11g | 609 |

5 Comments

lfree | January 22, 2006 5:01 PM

但是还是比expert作者的慢.
我公司最近买一台新机器,dell 6850,安装rac
做这个试验,需要26秒.
但是不刷新共享池马上在执行就很快.仅仅1秒.

8i下我找一台pc机执行同样的脚本,
仅仅需要8秒.

lfree | January 22, 2006 5:03 PM

但是还是比expert作者的慢.
我公司最近买一台新机器,dell 6850,安装rac
做这个试验,需要26秒.
但是不刷新共享池马上在执行就很快.仅仅1秒.

8i下我找一台pc机执行同样的脚本,
仅仅需要8秒.

kenniu | August 10, 2006 10:24 AM

你在抄袭TOM的>的东东呀

eygle | August 10, 2006 11:14 AM

我倒，kenniu 同学，你有没有看清楚上面在说什么啊？

SargentTi | May 15, 2007 2:02 PM

物品名:Eygle之憤怒
Rare:5
獲得方式:在Eygle.com上使用物品"kenniu的流言"