Oracle优化之执行计划解析
用友软件股份有限公司 林世福
2013年 9 月 25日
应用优化的重要性认识
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
SQL语句的解析过程
1 语法分析 分析语句的语法是否符合规范，衡量语句中各 表达式的意义。 2 语义分析 检查语句中涉及的所有数据库对象是否存在,及 用户权限。 3 视图转换 将涉及视图的查询语句转换为相应的对基表查 询语句。 4 表达式转换 将复杂的SQL表达式转换为较简单的等效连 接表达式。 5 选择优化器 不同的优化器一般产生不同的“执行计划”
数据量大的不同的结果集进行连接，较小的结果集作为驱动表，创建 基于内存的Hash table，大的结果集计算hash value，然后在内存中进 行匹配。
较小的结果集的大小接近hash_area_size，即较小的结果集一次性加载 到内存中
表连接算法
外表(驱动表)
内表
Nested Loop (嵌套循环)
成本优化策略演化
• 传统的：仅仅计算读取请求的数目 • 系统统计(1)：说明读取的大小和时间 • 系统统计(2)：说明CPU成本以及读取请求的大小和时间 • 系统统计(3)： 说明缓存、CPU成本以及读取请求的大小
和时间
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
如何解读执行计划
执行计划阅读方法 执行方法描述 术语解释
执行计划阅读方法
以树状格式进行读取，通过递归进入最底层 ，然后再返回该树的父（第一）。
实际演示
执行方法描述
数据扫描方式 表连接算法
数据扫描方式
表扫描
1).全表扫描(Full Table Scans, FTS) 2).通过ROWID的表存取(Table Access by ROWID )
执行方法描述
数据扫描方式 表连接算法

较小的建立Hash表 较大的计算Hash值 探测Hash表 优点：ora10G后默 认的连接方式，相 当于对两张表分别 扫描一次。
Hash Table
row1 row2
… … rowm
ram
key1 … … keyn
表连接
索引扫描
1).索引唯一扫描(index unique scan) 2).索引范围扫描(index range scan) 3).索引快速扫描(index fast full scan)
全表扫描
对于全表扫描,Oracle将顺序读取请求段中的所有数据块 对于返回大量数据查询尤其有效 可以避免由于索引访问带来的额外I/O 可以通过多块访问(db_file_multiblock_read_count) 全表扫描访问HWM下的所有数据块
Index访问
通过索引访问获得rowid 进而通过rowid访问获取数据所在位置 通过单块I/O读取 Rowid是访问单行的最快的方法
ROWID的格式如下(6位对象号，3位文件号，6位块号，3 位行号)
例子:AAABqHAADAAAC7EAAA 数据对象编号 文件编号 块编号 行编号 OOOOOO FFF BBBBBB RRR
率
6. Hash cluster key 7. Indexed cluster key
由
8. Composite key
高
9. Single-column non-unique index
到
10. Bounded range search on indexed columns 11. Unbounded range search on indexed columns
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
了解RBO和CBO
什么是RBO 和CBO
Oracle的优化规则，主要有 (RBO, RULE-Based Optimizer基于内在规则) (CBO, Cost-Based Optimizer 基于成本)
当两个table都有index时，选择结果集较小的table作为 驱动表 (CBO/RBO的区别)；
1 5
1 2
4
3
3
4
双重for逐行循 环
7
5
…
外层数据小，内层被连接的字段已建 索引，且内层数据量大； 比如select * from bd_corp c inner join ic_general_h h on
c.pk_corp=h.pk_corp
表连接
必须有一个table拥有index；
只用一个table有index时，选择没有index的table作为驱 动表；
RBO的规则
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key with unique key
4. Single row by unique index
效
5. Cluster join
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
什么是SQL执行计划
所谓执行计划，就是对一个DML SQL做出一份怎样去 完成任务的执行路径。基于不同的优化方式，执行计划可能 有很大的差异。
什么是SQL执行计划
Oracle应用优化
SQL语句的解析过程
6 选择连接方式 对多表连接ORACLE可选择适当的连接方 式。 7 选择连接顺序 对多表连接ORACLE选择哪一对表先连接 ，选择这两表中哪个表做为驱动表。 8 选择数据的搜索路径 根据以上条件选择合适的数据搜索 路径，如是选用全表搜索还是利用索引或是其他的方式。 9 产生“执行计划” 并执行。
低
12. Sort-merge join
13. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table-scan
CBO的规则
成本
指执行SQL语句导致的实际资源消耗；或者表示优化器对 执行语句所用时间的最优估计。