<让oracle跑得更快-4> 优化器(optimizer)

标签: oracle 优化 optimizer | 发表时间:2015-02-27 21:27 | 作者:
出处:http://www.iteye.com
Oracle数据库中优化器(optimizer)是sql分析和执行的优化工具,它负责制定sql的执行计划,也就是它负责保证sql执行的效率最高,比如优化器决定oracle以什么样的方式访问数据,是全表扫描(Full Table Scan, FTS),索引范围扫描(Index Range Scan)还是全索引快速扫描(Index Fast Full Scan, INDEX_FFS);对于表关联查询,它负责确定表之间以一种什么方式来关联,比如hash_join还是nested loops或是merge join。这些因素直接决定着sql的执行效率,所以优化器是sql执行的核心,它做出的执行计划好坏,直接决定着sql的执行效率。
Oracle的优化器有两种,基于规则的优化器(RBO)和基于代价的优化器(CBO),从oracle10g开始,RBO已经被弃用(但是我们依然可以通过HINT方式来使用它)。

4.1 RBO基于规则的优化器
在8i之前,oracle使用的是一种叫做RBO(Rule Based Optimizer,基于规则的优化器)的优化器,它的执行机制非常简单,就是在优化器里嵌入若干种规则,执行的sql语句符合哪种规则,就按照规则制定出相应的执行计划,比如说表上有个索引,如果谓词上有索引的列存在,则oracle会选择索引,否则选择全表扫描;又比如,两个表关联的时候,按照表在sql中的位置来决定哪个是驱动表,哪个是被驱动表。
下图是RBO在选择执行计划的一个优先级列表。


但事实是,走索引未必比走全表扫描快,还要看数据分布等。比如,首先创建一个分布极不均匀的表:
Create table t as select 1 id, object_name from dba_objects;
Update t set id=99 where rownum = 1; ---这样整个表就只有第一行的id=99;其余都是1.
Create index ind_t on t(id);
Select id, count(*) from t group by id;
结果:
ID   COUNT(*)
----------------------------
1	51080
99    1


在RBO的年代里,这两条的执行计划时这样的:
Select /*+ rule */ * from t where id=99;

执行计划


最后一句提示当前使用的优化器是RBO,建议使用CBO。

再次查询
Select /*+ rule*/ * from t where id=1;

执行计划:


使用hint /*+ rule*/的方式强制让oracle使用RBO优化器来产生执行计划,结果非常让人失望,oracle在id字段有索引的情况下,毫无悬念地全部选择了索引。
实际上我们知道,对于id=1,几乎所有的数据全都符合谓词条件,选择索引只能增加额外的开销(因为oracle要首先访问索引数据块,在索引上找到了相应的键值,然后按照键值上的rowid再去访问表中的相应数据),既然我们几乎要访问表中所有的数据,那么全表扫描自然是最优的选择,很遗憾的是,RBO让我们失望地做出了错误的选择。
RBO的情况就是这样,它是一种呆板、过时的优化器,已经在oracle10g以后的版本上被彻底剔除掉了(但仍然能通过使用hint来强制使用)。

4.2 CBO基于成本的优化器
从8i开始,oracle引入了CBO(Cost Based Optimizer, 基于成本的优化器),它的思路是让oracle获取所有执行计划相关的信息,通过对这些信息做计算分析,最后得出一个代价最小的执行计划作为最终执行计划。CBO是一种比RBO更理性的优化器。

还是拿上面的例子,看下CBO的表现:
Exec dbms_stats.gather_table_stats(user, ‘t’, cascade=>true);
Select /*+ all_rows */ * from t where id=1;

执行计划:


Select /*+ all_rows */ * from t where id=99;

执行计划:


CBO表现的非常完美,CBO会根据表的统计信息做出正确的执行计划:
Id=1, 全表扫描
Id=99, 索引扫描
因为这样的选择代价是最小的。从10g开始, oracle已经彻底丢弃了RBO。即使在表、索引没有被分析的时候,oracle依然会使用CBO。此时,oracle会使用一种叫做动态采样的技术,在分析sql的时候,动态的收集表、索引上的一些数据块,使用这些数据块的信息及字典表中关于这些对象的信息来计算出执行计划的代价,进而挑选出最优的执行计划。
比如:
Create table t (x int, y varchar2(2000));
Insert into t select object_id, object_name from dba_objects;
Commit;
Create index t_ind on t(x);
Alter session set sql_trace=true;
Select count(x) from t where x <100;
Count(x)
------
98

Alter system flush shared_pool;
Set autotrace trace exp;
Set linesize 120;
Select * from t where x=100;



可以看到,当表没有做分析的时候,oracle会使用动态采样来收集统计信息,这个动作只有在sql执行的第一次,即硬分析阶段使用,后续的软分析将不再使用动态采样,直接使用第一次sql硬分析时生成的执行计划。

CBO优化器有两种可选的运行模式:
(1) FIRST_ROWS(n)
(2) ALL_ROWS

当设置优化器模式为FIRST_ROWS(n)时,意味着oracle在执行sql时,优先考虑将结果集中的前n条记录以最快的速度反馈回来,而其他的结果并不需要同时返回。这种需求在一些网站搜索或者BBS的分页上经常看到,比如每次只查询信息的前20条或BBS的前20个帖子,这时设置FIRST_ROW(n)就非常合适,优化器并不需要同时将所有符合条件的结果返回。比如:
Select /*+ first_rows(10) */ b.x, b.y
from (select /*+ first_rows(10) */ a.*, rownum rnum
from (select /*+ first_rows(10)*/ * from t order by x) a where rownum <= 20) b
where rnum >= 10;
这是一个典型的分页的例子,每次从结果取出10条记录。需要注意的是, 排序使用的字段x必须创建有索引,否则CBO会忽略FIRST_ROWS(N)而使用ALL_ROWS.

ALL_ROWS在OLAP系统中使用得比较多,它的目的在于用最快的速度获得sql执行的最后一条记录,而不是前n条数据。和FIRST_ROWS(n)正好相反,ALL_ROWS强调sql整体的执行效率,而FIRST_ROWS(n)强调以最快的速度返回前n行,而不管所有的结果返回时长,可能最后一条要很长时间之后才能获得。

已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [oracle 优化 optimizer] 推荐:

<让oracle跑得更快-4> 优化器(optimizer)

- - ITeye博客
这些因素直接决定着sql的执行效率,所以优化器是sql执行的核心,它做出的执行计划好坏,直接决定着sql的执行效率. Oracle的优化器有两种,基于规则的优化器(RBO)和基于代价的优化器(CBO),从oracle10g开始,RBO已经被弃用(但是我们依然可以通过HINT方式来使用它). 4.1 RBO基于规则的优化器.

oracle 索引优化

- - Oracle - 数据库 - ITeye博客
表:gzl_action_define. 字段:id:主键,有索引. name:一般字段,无索引. 1.使用索引(UNIQUE SCAN). 2.使用索引(RANGE SCAN). 3.不使用索引(TABLE ACCESS (FULL)). 4.使用索引(FAST FULL SCAN). 5.不使用索引(TABLE ACCESS (FULL)).

oracle性能优化

- - 行业应用 - ITeye博客
                              oracle数据库的性能优化. 主要从内部存储结构,逻辑分区,表结构范式,sql语句优化. hibernate处的层次更高,主要从缓存,和sql语句的使用.     对于ORACLE数据库的数据存取,主要有四个不同的调整级别,第一级调整是操作系统级包括硬件平台,第二级调整是ORACLE RDBMS级的调整,第三级是数据库设计级的调整,最后一个调整级是SQL级.

oracle sql 优化大全

- - Oracle - 数据库 - ITeye博客
最近遇到了oracle sql优化的问题,找了一下,发现这文章实在不错,跟大家分享一下,如果以后有什么新的改进也会继续补充的. 1     前言… 2 . 2     总纲… 2 . 3     降龙十八掌… 3 . 第一掌 避免对列的操作… 3 . 第二掌 避免不必要的类型转换… 4 . 第三掌 增加查询的范围限制… 4 .

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

oracle性能优化总结

- - Oracle - 数据库 - ITeye博客
oracle是一个很成熟的数据库产品,当然性能方面也有不俗的表现. 尤其是9i之后又做了很多好的改进. 现在已经到12c了,不过本人只用过11g,最近有时间了,我把自己对性能的一些拙见总结一下. (有一些是来自网上,自己又给整理了一下). ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面.

Linux 内核参数优化(for oracle)

- - CSDN博客数据库推荐文章
    Oracle 不同平台的数据库安装指导为我们部署Oracle提供了一些系统参数设置的建议值,然而建议值是在通用的情况下得出的结论,并非能完全满足不同的需求. 使用不同的操作系统内核参数将使得数据库性能相差甚远. 本文描述了linux下几个主要内核参数的设置,供参考.   共享内存是在系统内核分配的一块缓冲区,多个进程都可以访问该缓冲区.

ORACLE数据库优化设计方案

- - CSDN博客推荐文章
本文主要从大型数据库ORACLE环境四个不同级别的调整分析入手,分析ORACLE的系统结构和工作机理,从九个不同方面较全面地总结了ORACLE数据库的优化调整方案. 关键词 ORACLE数据库 环境调整 优化设计 方案. 对于ORACLE数据库的数据存取,主要有四个不同的调整级别,第一级调整是操作系统级包括硬件平台, 第二级调整是ORACLE RDBMS级的调整,.

Oracle-SQL优化-union和union all

- - 数据库 - ITeye博客
用union all替代union. Union因为要进行去除重复值的处理,所以效率要低. 适用场合:1-如果合并两个select结果集,没有刻意要去除重复行.           2-如果union的各个select结果集,不存在交集. Oracle的内部处理过程:. union操作:先执行union all操作获取所有数据合集,再执行去除重复行操作.