如何针对Oracle数据库的看到的执行计划分析sql并想办法优化
查询Oracle语句的执行计划
- 在工具→首选项→窗口类型→计划窗口选择需要查看的列
- 在窗口列表→右键→新建→解释计划窗口
- 输入需要分析的SQL,执行查询
分析Oracle语句的执行计划
什么样的执行计划是优秀的
- 对于串行执行,关注的是开销
- 对于并行执行,关注的是性能
- 开销指使用的工作单元或资源的数量
- 工作单元:CPU、内存、IO
- 资源的数量:对执行操作需要使用的CPU、内存、IO数量预估
- 性能指对查询的最快响应时间,不关心占用资源
执行计划是否正确
基数
基数用来返回预估的行数,计算方式为行的总数/不同值的总数
基数影响访问方法、联接类型、联接顺序等
- 解决错误
- 统计信息陈旧缺少 DBMS_STATS
- 数据偏差 创建柱状图
- 一个表有多个单列谓词 使用DBMS_STATS.CREATE_EXTENDED_STATS创建一个列组
- 一个联结中使用多个列 使用DBMS_STATS.CREATE_EXTENDED_STATS创建一个列组
- 包含函数的列 使用DBMS_STATS.CREATE_EXTENDED_STATS创建有关包含函数列的统计信息
- 复杂表达式,包含多个表的列 采用4级或更高的动态抽样级别
访问方法
- 完整表扫描,读取全部数据行,过滤不符合where字句谓词的行,用于索引,DOP集等
- 按RowID访问,包含数据行所在的数据文件、数据块、数据块位置的信息,之后where条件里带rowid时使用
- 索引唯一扫描,语句中包含UNIQUE索引或者PRIMARY KEY主键约束条件时使用,确定访问数据只有一行
- 索引范围扫描,访问相邻索引项,可以返回多个rowid值,与等式一起用于非唯一索引,或与范围谓词一起用于唯一索引(<、>、between等)
- 索引跳过扫描,如果前导列中只有很少的不同值,非前导列中有很多不同值,跳过前导列,使用其余有用的部分
- 完整索引扫描,处理索引所有的页块,当所有需要的列都在索引中而且order by字句和索引结构匹配,或者排序合并链接完成可以使用
- 快速完整索引扫描,扫描索引中的所有块,在所有需要的列都在索引中时提到FTS,使用多块IO并行允许
- 索引链接,散列链接多个索引,这些索引一起包含查询中所有表列,不会消除排序操作
- 位图索引,使用键值位图和映射函数,映射函数可以将每个比特转换为一个rowid,可以有效合并where字句中多个条件的索引
解决错误,检查基数,联结顺序
- 使用表扫描
- DOP针对表,而不是索引或者MBRC
- 采用错误的索引
- 统计信息陈旧、缺少
- 采用了匹配最多列的索引
- 完整索引访问方式的开销比索引查找后根表访问方式的开销低
链接类型
- 嵌套循环链接:外部表的每一行,访问内部表的所有行,在链接小型内部表或者使用索引查找时有用
- 散列链接:对连个较小的表执行扫描,使用结果行根据内存中的链接创建散列表,对结果航的联结列执行散列操作,用于较大的表和if、equality谓词时有用
- 排序合并链接:包括两步骤,1排序联结操作,基于联结键对两个输入都进行排序,2.合并链接操作,将排序的列表结合在一起,在表连接条件是不相等条件时非常有用
- 笛卡尔链接:两个数据源,其中一个数据源的每一行和另一个数据源每一行都进行链接,生成笛卡尔乘积的数据集,在两个数据集间没有任何条件是是唯一选择
- 外连接:返回所有满足联结条件的行,并从没有(+)的表中返回所有这样的行,在另一个表中没有满足联结条件的行
建议
- 从可消除最多行数的表进行操作
- 将受可用访问方法的很大影响
基本规则
- 总先执行生成最多生成一行的链接
- 使用外联结时,在谓词中,含有此外连接运算符的表必须在其他表之后
- 如果不能执行试图合并,在链接视图外部的表之前链接视图内部的表
分区修剪
根据数据表的数据量是否需要分区,需要考虑在分区后的查询中能否过滤大部分的数据行,以及范围查询会不会命中多个分区