ORACLE 笔记

SQL PV:

SQL执行过程

1.解析

目标 –> 语法语义分析 、权限校验 、生成执行计划
（绑定变量好，但不要过多 使用绑定变量可以减少解析开销，但绑定变量不是越多越好）
解析的方式有以下两种：

  • 1.硬解析
  • 2.软解析

2.执行

目标 –> 按照执行计划 、获取数据
（本该走索引的结果全表扫描 本该分区裁切结果全表扫描 引发性能悲剧）
获取数据的方式：

  • 1.全表扫描
  • 2.索引
  • 3.分区裁切

3.获取

目标 –> 返回数据给客户端
（（不要过多的排序和不必要的排序）逻辑读取优于物理读取，过多的物理读取 过多的排序和不必要的排序引发性能悲剧）
操作不当容易造成排序的消耗较高
读取的方式：

  • 1.物理读取
  • 2.逻辑读取

1.严重类的 （一旦出现，严重影响性能，甚至引发灾难）

引发问题

  1. 产生笛卡儿积 （执行计划中出现的关键字 “ MERGE JOIN CARTESIAN” 说明了笛卡儿集的出现）
    • 数据量几何增长（数据为相关表数据条数的乘积）
    • 原因 无连接条件或缺少链接条件
    • 规避方法 使用主键或唯一索引作为连接条件
  2. 无法使用索引
    • 在索引列上做了相关计算或转换
    • where条件中太多的 or
    • 对空值查询 - 这个跟索引的创建有关不一定用不到，一般的创建会导致无法使用到索引
  3. 无法使用分区裁剪
    • 原因
      1. 限制条件中缺少分区键
      2. 在分区键列上做了计算或转换
    • 导致 数据将会做全分区扫描
  4. 对分区列进行更新绝对也是一场灾难
    • 尽量避免在分区键列上的一切计算操作

常见问题

  1. 缺少连接条件
    • 多表关联时，where条件缺少表连接条件
    • 多表关联时缺少连接条件，说明存在逻辑缺陷，会导致执行计划出现笛卡儿积，严重影响SQL性能，SQL返回的也会是非预期的结果集
  2. 查询条件与列类型不一致
    • 在查询列上使用表达式、函数调用，或者传递的变量和列类型不一致，例如列类型为int型，但传递的变量时varchar类型的，这时候就会发生隐式转换，导致执行计划无法走索引。
  3. 缺少分区键
    • 查询分区表时，where条件中没有使用分区键进行必要的过滤，导致全表过滤
  4. DML语句缺少where条件
    • DML语句缺少where条件
    • UPDATE | DELETE | 语句缺少 where条件，或where条件是同列匹配，存在错误更新全表数据的风险
    • 另 ,有些SQL的where条件是自动拼接的，需要避免未拼接任何条件的DML操作
  5. 查询列上使用表达式
    • 在查询列上使用表达式，可能会导致执行计划无法使用列上索引

2.警告类*（可能会引发性能问题）

引发问题

  1. 影响程序健壮性
    • insert语句中没有指定列信息，新增列后会引发值不足的错误，同样问题也可能发生在merge语句中
  2. 无法使用索引而导致全表扫描
    • 左右模糊查询
      • 仅左模糊可以使用到反向索引或仅右模糊可以使用到一般索引
      • 太多的OR 条件
      • 对空值的查询
  3. 过多的硬解析
    • 未使用绑定变量
    • 过多的SQL动态拼接

常见问题

  1. insert语句缺少列信息
    • insert语句插入时没有指明字段及对应关系
  2. 使用左模糊
    • 使用左模糊查询
    • 在使用like进行模糊匹配的时，在字符串左侧出现% ，例如
      where cardno like ‘%6214%’ 由于左模糊查询的时候，会导致优化器无法利用索引，降低查询效率。
  3. 执行计划中存在全表扫描
    • 由于SQL写法不当，或表中缺少相关索引，业务逻辑实现设计不合理等，导致本该走索引的执行计划中出现全表扫描
  4. 未使用绑定变量
    • 未使用绑定变量的SQL语句如果过多，硬解析会占用较多的CPU等数据库资源，影响数据库性能；存在SQL注入风险；
      存在造成共享池碎片化严重的隐患；在AMM模式下，硬解析严重的情况下，会使共享池暴增使得缓冲区减少，从而造成大量的块适时间
      内写回磁盘，造成IO激增的情况
  5. 执行计划中出现笛卡儿积
    • 缺少关联条件、当多表关联时，其中一个只返回一行数据，没有合适的索引等都可能发生笛卡儿积操作，无法走索引
    • 缺少连接条件时，经常会出现笛卡儿积，导致SQL性能下降
    • 确定存在关联条件后，如果还是出现了笛卡儿积，需要确定表的统计信息是否准确，如果因为统计信息不准，
      可能导致优化器估算出的只有一行（实际情况可能有很多行），这时出现的笛卡儿积，也会导致SQL性能下降
  6. 多个条件OR查询
    • 多个条件OR查询可能会导致SQL无法正确使用索引
  7. 存在回表filter
    • SQL执行计划出现在TABLE ACCESS BY LOCAL ROWID 上的filter 过滤操作
    • sql对某个表的访问中，索引不能覆盖全部的谓语条件，使索引访问之后，仍然需要根据其他谓语条件过滤，
      如果索引过滤后，仍有大量数据行执行filter操作，且语句执行较频繁，则会对系统性能产生一定影响
  8. 执行计划中存在filter操作
    • 执行计划存在filter操作
    • filter的操作是对外部查询的每一行，都要对子查询执行一次全表扫描，所以很多时候提到filter时都是性能很差的操作，
      当外部查询需要传递给子查询输入的distinct value 非常小时，filter 的效率还是非常高的
  9. 存在多表连接
    • 一般在OLAP应用中使用多个大表进行连接，OLTP类型应用应避免使用此类SQL或者SQL中使用了6个（数量可自定义）以上的表
  10. 存在多层嵌套
    • 存在多层次嵌套
    • 执行计划中嵌套深度超过5层（层次可自定义）嵌套过深
  11. 使用with查询
    • SQL中使用with查询，如果多次调用with，oracle需要写数据到temp表空间。这是会出现direct path read，
      进而影响SQL性能。同事在写temp时oracle需要像数据库集群注册信息，有时会因为无法写注册信息到集群，导致ORA-01839的出现
  12. 反向查询
    • 反向查询时where子句使用 != 、 <> 、not in 、not exists 、 not like 、 not等。
      在索引列上使用反向查询，经常会无法走索引，导致全表扫描。
  13. 执行计划中存在索引全扫
    • 在我们使用排序操作时，如果排序使用到的列都在索引中，这是oracle可能会选择索引全扫描。索引全扫时一种顺序的单块读，
      如果排序本身的消耗并不高，则可能索引快速扫描和全表扫更快
  14. 执行计划中存在索引跳扫
    • 当查询列不是索引前导列时，可能产生索引跳跃扫描（index skip scan），如果查询列快速性较好，甚至由于前导列，则此时的索引跳扫性能并不是最佳的。
  15. 查询语句使用 union（能用union all尽量使用union all）
    • 查询语句使用了union操作，排序的消耗较高
    • 如果查询SQL仅仅只是将两个结果集合起来，并不需要排序、去重等操作，则union操作会产生多余的排序和去重消耗。
  16. SQL中包含hint
    • 在表中的列数据分布不均的情况下，如果强制指定唯一执行计划，可能导致性能问题
    • 数据分布变更后，也会对性能造成冲击
  17. 使用嵌套子查询
    • 在where条件子查询中嵌套另一个子查询
    • 因为子查询需先遍历子查询在遍历外层查询，如果外层查询行数较多

3.提示类 （可能引发一般性问题）

引发问题

  1. 影响程序健壮性
  2. 影响sql性能稳定性
  3. 取出不必要的值，会导致不必要的回表操作

常见问题

  1. 使用了SQL Profile
    • 使用了SQL Profile的SQL，这类SQL在SQL文本发生变化时会导致执行计划变化，无法使用SQL Profile。
      SQL Profile 本是SQL优化方式中的一种，常用于解决复杂、计划多变的SQL、性能波动较大的SQL，使SQL的执行计划牌较优状态。
      • 若SQL代码不会变化和更新，本身没有坏处，若SQL文本发生变化，则可能不能与SQL Profile 匹配，导致执行计划发生变化，影响性能。
      • 常用于解决较紧急问题。事后需要对SQL进行详细分析，找到执行计划发生变更的原因并完善，不要高度依赖于SQL Profile。
  2. 使用了select … for update
    • 禁止在SQL中使用悲观锁。ORACLE有事务机制，一般情况不需要使用for update 来操作，能不使用尽量不使用。单纯的使用for update，
      若有锁被持有且一直不被释放，会导致长时间的等待，如果有较好的并发，可能出来长时间的并发等待。
  3. 执行计划中存在全分区扫描
    • 该类SQL一般缺少分区条件，或者在分区键上发生操作、导致无法进行分区裁剪
  4. 执行计划发生变化
    • 由于统计信息更新，表上的索引发生变更，表的数据量发生变更，绑定变量的值分布不一致，数据库参数发生变更等情况，
      都会导致SQL的执行计划发生变化。
  5. 绑定变量过多
    • 绑定变量不宜过多，比如 in（p1,p2,p3,..px），过多的绑定变量会影响执行计划的评估，绑定变量的赋值也会导致SQL响应时间变长。
    • 可能引发bug而产生性能代价
  6. 查询语句缺少where条件
    • 查询条件缺少where条件时，会导致全表扫描，尤其是对大表的全表扫描的代价会很高，性能也很差。
      另外：有些 SQL 的where 1 = 1，后面根据用户勾选情况自动拼接的 ，需要限制用户不勾选任何条件就可以提交查询。
  7. 使用标量子查询（标量子查询的使用是有条件的）
    • 标量子查询涉及的表的关联列必须有索引或者关联列是主键列；
    • 标量子查询涉及的表的数据量不能太大（有人说是100w），否则即使有索引也会很慢
    • 对同一个表不能有多个标量子查询，这样性能也不会很高
  8. 使用select *
    • SQL中为了方便直接使用了select * ，来代替需要查询的列，这样的SQL在执行时会造成不必要的回表操作，浪费数据库资源，
      同时也降低SQL的性能。

4.常见的不规范操作

  • 在列上做操作
    1. 在索引列上操作导致无法使用索引
    2. 在分区键列上操作导致全分区扫描
  • 不必要的排序操作
    1. order by - 查询不需要的排序而使用了排序
    2. distinct - 在唯一键或主键上去重
    3. union - 无重复数据或使用union all 不影响最终的结果
    4. 其他存在排序操作
      • group by
      • 集合运算（set操作）
        1. 交集 intersect
        2. 差集 minus
        3. union 并集 （union all 无排序不去除重复值）
  • 过多的OR条件
    • 尤其是 OR EXISTS
  • 不必要的NULL数据检索
  • 隐式转换
    • 检索列与值类型不一致

Powered by Hexo

Copyright © 2016 - 2019 When I think of you, I smile. All Rights Reserved.

UV : | PV :