Oracle优化器详解，通宵爆肝整理

作者：IT邦德

中国DBA联盟(ACDU)成员，目前从事DBA及程序编程（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
B站主播Oracle、Mysql、PG实战课程，请搜索：jeames007

微信：jem_db
QQ交流群:168797397
更多内容请关注B站（搜索jeames007）

复制

一、 什么是优化器

   优化器是 SQL 分析和执行的优化工具，他负责制定 SQL 的执行计划,
比如什么时候是全表扫描（FTS full table scan），
什么时候是索引范围搜索（Index Range
Scan），或者是全索引扫描（INDEX fast full scan，INDEX_FFS）；
如果是表于表之间连接的时候，
它会负责去定表之间以一种什么样子的形式来关联，
比如 HASH_JOIN 还是 NESTED LOOPS 或者是 MERGE JOIN。
这些因素直接决定了 SQL 的执行效率,所以优化器是 SQL 执行的核心！！！
注释：SQL 执行一定会有代价。
复制

二、 优化器的种类

Rule Based Optimizer（RBO）基于规则,一套死板的规则来控制数据访问
Cost Based Optimizer（CBO）基于成本，或者讲统计信息，依据一套数据模型，
计算数据访问和处理的成本，选择最优成本作为执行方案

CBO 方式：它是看语句的代价(Cost),
这里的代价主要指 Cpu 和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
统计信息给出表的大小、有少行、每行的长度等信息。
这些统计信息起初在库内是没有的，是做完收集统计信息后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,
因些应及时更新这些信息。

注意：走索引不一定就是优的，
比如一个表只有两行数据，一次 IO 就可以完成全表的检索,
而此时走索引时则需要两次 IO,这时全表扫描(full table scan)是最好。
复制

三、CBO优化器的结构

CBO包含以下组件：查询转换器、评估器、计划生成器

3.1 查询转换器

改变查询语句的形式，以产生较好的执行计划
例如：绑定变量虽然可以有效的减少“硬分析”，Oracle只允许第一次调用时进行“窥视”，
接下来的调用即使绑定变量的值发生了变化，也仍然是使用第一次生成的执行计划

复制

3.2 评估器

通过计算三个值来评估计划的总体成本（累加）：
选择性（Selectivity 0-1之间）
基数（Cardinality）
成本（Cost）
复制

3.3 计划生成器

计划生成器的作用就是生成大量的执行计划，然后选择其中总体成本最低的一个

复制

四、补充

01、CBO工作模式：
FIRST_ROWS（以最快返回n行为目的）/ALL_ROWS（全部处理完毕为目的），默认ALL_ROWS
优化器模式的设置方式
A.参数设置
SYS@ORCL> show parameter optimizer_mode
optimizer_mode                       string      ALL_ROWS
B.会话设置
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS/ALL_ROWS

C.SQL设置
select /*+ all_rows */ count(*)  from t

02、clustering factor（影响成本的一个评估）
    聚簇因子是基于表上索引列上的一个值，每一个索引都有一个聚簇因子,
用于描述索引块上与表块上存储数据在顺序上的相似程度，也就说表上的数据行的存储顺序与索引列上
顺序是否一致。

select a.index_name,a.clustering_factor
  from user_indexes a
where a.table_name = 'EMP'

03.CBO数据来源
初始化参数
– 优化参数
– CPU
– 数据块大小
– 多块读的大小
......
数据字典
– user_tables,user_tab_partitions
– user_indexes,user_ind_partitions
– user_tab_col_statistics

复制

本次分享到此结束啦~

如果觉得文章对你有帮助，点赞、收藏、关注、评论
你的支持就是我创作最大的动力。

❤️ 技术交流可以 关注公众号：IT邦德 ❤️

微信：jem_db
QQ交流群:168797397
更多内容请关注B站看直播（搜索jeames007）