目录
一, Oracle 的优化器
1.1 优化器简介
1.2 SQL 执行过程
二, 优化器优化方式
2.1 优化器的优化方式
2.2 基于规则的优化器
2.3 基于成本的优化器
三, 优化器优化模式
3.1 优化器优化模式分类
3.2 优化模式使用方法
在看《基于 Oracle 的 SQL 优化一书》知道了很多专业名称, 做了记录, CBO, 优化器, 查询转换, 执行计划, Hint, 并行, 游标, 绑定变量, 统计信息, 直方图, 索引等等. 这篇博客可以说是读书笔记
一, Oracle 的优化器
1.1 优化器简介
优化器(Optimizer): 优化器是 Oracle 数据库内置的一个核心子系统, 负责解析 SQL,Oracle 优化器是 Oracle 系统的一个核心组件, 其目的是按照一定的原则来获取目标 SQL 在当前情形下执行的最高效执行路径, 也可以说是执行计划.
1.2 SQL 执行过程
Oracle SQL 的执行过程:
二, 优化器优化方式
2.1 优化器的优化方式
Oracle 优化器按照优化方式分为两种
基于规则的优化器(Rule-Based Optimizer), 简称 RBO
基于成本的优化器(Cost-Based Optimizer), 简称 CBO
2.2 基于规则的优化器
2.2.1 RBO 简介
基于规则的优化器(Rule-Based Optimizer): 所谓基于规则的优化器是指 Oracle 按照硬编码在数据库的一系列规则来决定 SQL 的执行计划, 简称是 RBO
2.2.2 RBO 缺陷
RBO 在 oracle10 后官方就不建议用, 因为 RBO 并不支持 oracle 一些性能比较好的功能特性, 也不会根据表的数据量等等获取执行计划, 而且 RBO 定的那些规则也不是很容易修改. 不过代码在 oracle10 后版本还是保存的, 所以要学习测试也可以用 SQL 开启 RBO 模式
alter session set optimizer_mode='RULE';
2.2.3 RBO 执行过程
RBO 的执行过程: 对于一条 sql,oracle 会事先给 sql 各种情况的执行计划定一个等级, 一共有 15 个等级, 从等级 1 到等级 15, 规则是等级越低执行效率越高, 也就是等级 1 的执行计划执行效率是最高的. 然后 oracle 自然就选出等级 1 的执行路径作为执行计划.
2.2.4 RBO 特殊情况
对于执行路径一样的情况: 假如出现执行路径一样的情况, 这时候就要根据数据字典缓存来确定最低的等级了, 意思就是获取缓存中的先后顺序确定哪条作为执行计划
2.2.5 强制 CBO 的情况
上面说了可以通过 SQL 开启 CBO 模式, 这是针对普通情况的, 假如出现下面情况, 那就是强制使用 CBO
SQL 涉及对象有 IOT(Index Organized Table)
SQL 涉及的对象分区表
使用了并行查询或者并行 DML
使用了星型连接
使用了哈希连接
使用了索引快速全扫描
使用了函数索引
....
这些情况总结来自《基于 Oracle 的 SQL 优化一书》
虽然 Oracle 针对上述情况都开启了强制 CBO, 但是我们还是可以手动解决的, 方法也是来自《基于 Oracle 的 SQL 优化一书》, 作者提供了改写等价 sql 的方法, 比如在 sql 的 where 条件中对 number 或者 date 类型的列加 0,
select * from 表格 where a+0 > 参数
如果是 varchar2 类型的, 加可以加个空字符串
select * from 表格 where a || '' = 参数
2.3 基于成本的优化器
2.3.1 CBO 简介
介绍一下基于成本的优化器(Cost-Based Optimization): 基于成本的优化器简称是 CBO, 在 SQL 执行过程, 会缓存执行的一些信息到 Oracle 的数据字典里, 这里的信息就有 sql 执行路径的 I/O, 网络资源, CPU 的使用情况, 其实这个就是 SQL 的执行成本, 也是按照这个成本来确定执行计划. 所以 CBO 概念就是根据 I/O, 网络资源, CPU 的使用情况来确定 SQL 执行路径也可以说是执行计划的优化器.
2.3.2 集的势
集的势 (Cardinality) 是 CBO 特有的概念, 集的势指结果集的行数. 引入这个概念是为了表示 SQL 执行成本值, Cardinality 越大, 也就是说 sql 执行返回的结果集所包含的行数就越多, 也说明成本越大.
2.3.3 可选择率
可选择率(Selectivity): 指施加指定谓语条件后返回结果集的记录数占未施加任何谓语条件的原始结果集的记录数的比率. 可选择率的范围是 0~1, 它的值越小, 说明可选择性越好, 值越大说明可选择性越差, 也就是成本值越大. 可选择率为 1 时性能是最差的.
可选择率 = 施加指定谓语条件后返回结果集的记录数 / 未施加任何谓语条件的原始结果集的记录数.
三, 优化器优化模式
3.1 优化器优化模式分类
优化器优化模式分为 Rule,Choose,First rows,All rows
Rule: 就是基于规则 Rule 的方式
Choose: 当一个表或索引有统计信息, 则走 CBO 的方式, 如果表或索引没统计信息, 表又不是特别的小, 而且相应的列有索引时, 那么就走索引, 走 RBO 的方式. 这是 Oracle 的默认方式
First rows: 与 Choose 方式是类似的, 所不同的是当一个表有统计信息时, 它将是以最快的方式返回查询的最先的几行, 从总体上减少了响应时间.
All rows: 其实就是基于 Cost 方式
3.2 优化模式使用方法
要修改优化模式可以使用类似 SQL
alter session set optimizer_mode='RULE';