Oracle优化之执行计划解析

数据扫描方式
表扫描
1).全表扫描(Full Table Scans, FTS) 2).通过ROWID的表存取(Table Access by ROWID )
索引扫描
1).索引唯一扫描(index unique scan)
2).索引范围扫描(index range scan) 3).索引快速扫描(index fast full scan)
执行计划中该行的累计成本。
基数（Card）
根据统计信息，执行计划中该行将产生的数据量。
字节（Bytes）
执行计划中该行将产生的数据总量。
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
NC SQL规范
1、不使用不必要的外连接
外连接(left/right join)与内连接(inner join)作用不同。 例： select pk_bdinfo, bdcode, ……… from bd_bdinfo a where exists (select 1 from bd_subjass b left outer join bd_accsubj acc on b.pk_accsubj = acc.pk_accsubj where a.pk_bdinfo = b.pk_bdinfo and acc.pk_corp = '1057')
NC SQL规范
4、维表的查询
尽量减少不必要的复杂表达式。 Oracle解析SQL时需要将复杂的SQL表达式转换为较 简单的等效连接表达式。 SELECT a.pk, ( SELECT b.name FROM b WHERE b.code = 1 and a.id=b.id) name, ( SELECT b.type FROM b WHERE b.code = 1 and a.id=b.id) type FROM a WHERE a.code = 2 改写： SELECT a.pk, b1.name name, b2.type type FROM a , b b1 ,b b2 WHERE a.code = 2 And b1.code = 1 and a.id=b1.id And b2.code = 1 and a.id=b2.id
select * from staff_member where staff_id=’123’；
列上有数学运算的，如：
select * from staff_member where salary*2<10000;
使用不等于(<>)运算的，如：
select * from staff_member where dept_no<>2001;
效 率 由 高 到 低
CBO的规则
成本
指执行SQL语句导致的实际资源消耗；或者表示优化器对 执行语句所用时间的最优估计。
成本优化策略演化
• • • • 传统的：仅仅计算读取请求的数目 系统统计(1)：说明读取的大小和时间 系统统计(2)：说明CPU成本以及读取请求的大小和时间 系统统计(3)： 说明缓存、CPU成本以及读取请求的大小 和时间
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
什么是SQL执行计划
所谓执行计划，就是对一个DML SQL做出一份怎样去 完成任务的执行路径。基于不同的优化方式，执行计划可能 有很大的差异。
什么是SQL执行计划
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
了解RBO和CBO
什么是RBO 和CBO
Oracle的优化规则，主要有 (RBO, RULE-Based Optimizer基于内在规则) (CBO, Cost-Based Optimizer 基于成本)
NC SQL规范
8、避免使用IS NULL
设计中尽量避免字段为NULL，不能用NULL代表业 务意义。 例：总帐 凭证的记帐标示等 NC系统里常见（col1=’’ or colx is null）造成诸多效 率问题
NC SQL规范
9、将产生排他锁的操作放到事务的最后
锁等待的避免 死锁产生及其避免
当两个table都有index时，选择结果集较小的table作为 驱动表 (CBO/RBO的区别)；
适用于有index的连接，两个有大小差异的结果集，数据 量较小。
表连接
Sort Merge Join
(合并连接)
对合并列分别都 要求排序，效率 最低
1 3 4 6 7 10
1 2 3 4 5 10
表连接
QA
在不使用index的情况下使用MR join：
在连接table的字段上不存在可用的index； 查询将返回两个table中大部分的数据块； CBO认为table scan比index range scan更少的cost； 适用于没有index的连接，或两个大小接近的超大 结果集。
术语解释
耗费（Cost）
双重for逐行循 环
1 5 4 3 7
1 2 3 4 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作为驱 动表；
NC SQL规范
5、 EXISTS和IN以及表连接
通常优化器会将in或exists转换为连接来进行优化， 但这个转换并不是100%会进行的，建议尽量使用表关 联来替换in 和exists
NC SQL规范
6、避免在索引列上使用计算
如果列上有建索引，存在下面情况的SQL，不会用 到索引： 存在数据类型隐形转换的,如：
NC SQL规范
7、IN、OR、UNION、UNION ALL
建议每条SQL语句中in中的元素个数在500以下，如 果个数超过时，应拆分为多条SQL语句，禁止使用XX in (‘’,’’…) or xx in(‘’,’’,’’)。 禁止使用or超过500，如XX=’123’ or xx=’456’
执行方法描述
数据扫描方式 表连接算法
表连接算法
Hash Table
Hash Join (哈希连接)
较小的建立Hash表 较大的计算Hash值 探测Hash表 优点：ora10G后默 认的连接方式，相 当于对两张表分别 扫描一次。
row1 row2 … … rowm
ram
key1 … … keyn
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
如何解读执行计划
执行计划阅读方法 执行方法描述 术语解释
执行计划阅读方法
以树状格式进行读取，通过递归进入最底层 ，然后再返回该树的父（第一）。
实际演示
执行方法描述
数据扫描方式 表连接算法
表连接
wenku.baidu.com
数据量大的不同的结果集进行连接，较小的结果集作为驱动表，创建 基于内存的Hash table，大的结果集计算hash value，然后在内存中进 行匹配。 较小的结果集的大小接近hash_area_size，即较小的结果集一次性加载 到内存中
表连接算法
外表(驱动表) 内表
Nested Loop (嵌套循环)
NC SQL规范
2、减少访问数据库的次数
批修改、批插入，利用批量方法减少数据库访问次数 。 减少不必要的操作。 不必要的union all
NC SQL规范
3、用Where子句替换HAVING子句
避免使用HAVING子句作为条件，HAVING只会 在检索出所有记录之后对结果集进行过滤，这个处理 需要排序，总计等操作，如果能通过where子句限制记 录的数目，那就能减少这方面开销。
Oracle优化之执行计划解析
用友软件股份有限公司 林世福 2013年 9 月 25日
应用优化的重要性认识
Oracle应用优化
SQL语句的解析过程 什么是SQL执行计划 了解RBO和CBO 如何解读执行计划 NC SQL规范
SQL语句的解析过程
1 语法分析 分析语句的语法是否符合规范，衡量语句中各 表达式的意义。 2 语义分析 检查语句中涉及的所有数据库对象是否存在,及 用户权限。 3 视图转换 将涉及视图的查询语句转换为相应的对基表查 询语句。 4 表达式转换 将复杂的SQL表达式转换为较简单的等效连 接表达式。 5 选择优化器 不同的优化器一般产生不同的“执行计划”
RBO的规则
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. Single row by ROWID Single row by cluster join Single row by hash cluster key with unique key Single row by unique index Cluster join Hash cluster key Indexed cluster key Composite key Single-column non-unique index Bounded range search on indexed columns Unbounded range search on indexed columns Sort-merge join MAX or MIN of indexed column ORDER BY on indexed columns Full table-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
SQL语句的解析过程
6 选择连接方式 对多表连接ORACLE可选择适当的连接方
式。 7 选择连接顺序 对多表连接ORACLE选择哪一对表先连接 ，选择这两表中哪个表做为驱动表。 8 选择数据的搜索路径 根据以上条件选择合适的数据搜索 路径，如是选用全表搜索还是利用索引或是其他的方式。 9 产生“执行计划” 并执行。
NC SQL规范
10、不要随意加HINT
HINT目的是希望SQL按照指定的执行计划去执行， 但一旦环境变化，这个计划就不一定是最有的了。
NC SQL规范
11、避免太多表关联查询
禁止在一条SQL语句中使用3层以上的嵌套查询， 如果有，请考虑使用临时表或中间结果集。 尽量避免在一条SQL语句中从>= 4个表中同时取数 ，对于仅是作为过滤条件关联，但不涉及取数的表， 不参与表个数计算；如果必须关联4个或4个以上表， 尽量采用子查询的方式。
NC SQL规范
6、避免在索引列上使用计算
使用substr字符串函数的，如： select * from staff_member where substr(last_name,1,4)=’FRED’; ‘%’通配符在第一个字符的，如: select * from staff_member where first_name like ‘%DON’; 字符串连接(||)的，如： select * from staff_member where first_name||’’=’DONALD’