Oracle SQL优化要点.pdf

Oracle SQL 优化 作者：丁俊，新炬网络性能优化专家。 目录 1. SQL 优化的本质 2. SQL 优化 Road Map 2.1 制定 SQL 优化目标 2.2 检查执行计划 2.3 检查统计信息 2.4 检查高效访问结构 2.5 检查影响优化器的参数 2.6 SQL 语句编写问题 2.7 SQL 优化器限制导致的执行计划差 3. SQL 优化案例 4. SQL 执行计划获取 4.1 如何获取准确的执行计划 4.2 看懂执行计划执行顺序 SQL 优化的本质 一般来说，SQL 优化是让 SQL 运行得更快，使 SQL 更快的方式有很多，比如提高索引的使 用效率，或者并行查询。可以看到里面的公式： 执行效率或者一般说的执行时间，是和完成一次 SQL 所需要访问的资源总量（S ）成正比以 及单位时间内能够访问的资源量（V ）成反比，S 越大，效率越低，V 越大效率越高。 比如 通过并行查询，则可以提升单位时间内访问的资源量。 当然，这仅仅是从执行时间上考虑，SQL 优化肯定不仅仅是执行时间降低，应该是资源使用 与执行时间降低之间寻求一种平衡，否则，盲目并行，可能提升不了效率，反而让系统资源 消耗殆尽。 最终来说，SQL 优化的本质就是：1、缩短响应时间；2、提升系统吞吐量；3、提升系统负 载能力。要使用多种手段，在提升系统吞吐量和增加系统负载能力，提高单个 SQL 效率之 间寻求一种平衡。就是要尽量减少一条 SQL 需要访问的资源总量，比如走索引更好，那么 不要使用全表扫描。 二SQL 优化Road Map 一条 SQL 的优化路线图如下所示： 具体操作步骤： 2.1 制定 SQL 优化目标 获取待优化 SQL、制定优化目标：从 AWR、ASH、ORA 工具等主动发现有问题的 SQL、 用户报告有性能问题 DBA 介入等，通过对 SQL 的执行情况进行了解，先初步制定 SQL 的 优化目标。 2.2 检查执行计划 explain 工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql 等。 执 行计划是我们进行 SQL 优化的核心内容，无计划，不优化。看执行计划有一些技巧，也有 很多方式，各种方式之间是有区别的。 2.3 检查统计信息 ORACLE 使用 DBMS_STATS 包对统计信息进行管理，涉及系统统计信息、表、列、索引、 分区等对象的统计信息，统计信息是 SQL 能够使用正确执行计划的保证。我们知道， ORACLE CBO 优化器是利用统计信息来判断正确的执行路径，JOIN 方式的，因此，准确的 统计信息是产生正确执行计划的首要条件。 可以从这个图看出，一条 SQL 产生执行计划需要经过哪些步骤，在我看来：1、正确的查询 转换；2、准确的统计信息，是产生正确执行计划的重要保证。当然，还有 BUG ，或优化器 限制等也会导致 SQL 效率低下，无法产生正确的执行计划。 如图所示： 2.4 检查高效访问结构 重要的访问结构，诸如索引、分区等能够快速提高 SQL 执行效率。表存储的数据本身，如 碎片过多、数据倾斜严重、数据存储离散度大，也会影响效率。 2.5 检查影响优化器的参数 optimizer_mode 、 optimizer_index_cost_adj 、 optimizer_dynamic sampling 、 _optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable 等对 SQL 执行计划影响较大。比如有时候我们通过禁用_optimizer_mjc_enabled 参数， 让执行计划不要使用笛卡尔积来提升效率，因为这个参数开启有很多问题，所以一般生产库 都要求禁用。 还有什么能够影响执行计划呢？对，new features,每个版本的 new features ，引入的目的 都是好的，但是实际使用中，可能触发 BUG。比如 11g 的ACS( 自适应游标共享）、automatic serial direct path （自动串行直接路径读）、extended statistics、SQL query result cache 等。有的新特性会导致问题，所以需要谨慎使用。 比如 11g adaptive cursor sharing,自适应游标共享，它的引入是为了解决使