如何理解执行计划——Oracle性能优化高级培训

如何理解Oracle执行计划一、执行计划概述二、数据访问方法详解三、执行计划层次分析四、案例说明五、表格访问策略六、表格连接方式七、运算符介绍

使用hints提示可以帮助DBA干预ORACLE SQL执行计划，确保语句高效运行。尽管基于代价的优化器通常可靠，但有时会选择不合适的执行计划。通过指定存取路径或连接类型，DBA可以指导优化器生成更有效的执行计划。例如，当全表扫描比索引扫描更有效时，可以通过hints提示优化器使用全表扫描。在ORACLE中，hints是实现这一目的的关键工具。

要为一个语句生成执行计划，可以采用以下三种方法：1) 最简单的方法是使用Sql> set autotrace on命令来执行语句，执行完成后会显示执行计划和统计信息。这种方法的优点是简单直接，但缺点是需要等待语句执行完成才能返回执行计划，优化周期较长。如果只想得到执行计划而不执行语句，可以使用Sql> set autotrace traceonly命令，这样只会列出执行计划而不执行语句，大大减少了优化时间。虽然也会列出统计信息，但因为没有执行语句，所以统计信息的作用有限。如果在执行语句时遇到错误，可以通过在用户下运行Sqlplus > @ ?rdbmsadminutlxplan.sql 或者使用sys用户登陆并执行Sqlplus > @ ?sqlplusadminplustrce.sql 来解决。

执行计划的第三步和第六步分别从EMP表和SALGRADE表读取所有行。第五步在PK_DEPTNO索引中查找第三步返回的每个DEPTNO值，定位与DEPT表关联的行的ROWID。第四步从DEPT表中检索第五步返回的行的ROWID。这些步骤在行源上执行操作，如表关联、排序或过滤。第二步实现嵌套循环操作，将第三步和第四步的行源连接在一起，返回结果行到第一步。第一步完成过滤操作，消除第二步中与第六步相对应的行，将剩余行返回给用户或应用。

基于代价的优化器通常能够选择正确的优化路径, 但有时也会做出不佳的选择, 导致语句执行缓慢。这时DBA就需要干预, 指示优化器使用特定的存取路径或连接类型生成执行计划。 ORACLE通过hints提示机制实现对优化器的干预。hints指示优化器按照DBA的意愿生成执行计划。 hints可以用来实现: 指定优化器类型 设定基于代价优化器的优化目标 (all_rows 或 first_rows) 选择表的访问路径 (全表扫描、索引扫描、rowid访问) 指定表之间的连接类型 确定表之间的连接顺序 控制语句的并行程度

基于代价的优化器通常能够选择最佳执行计划，但有时却可能选择不合适的执行路径，导致性能低下。这时需要DBA通过hints手动干预，指导优化器选择最优的存取路径和连接类型。例如，当全表扫描比索引扫描更为有效时，可以通过hints指示优化器采用全表扫描。在ORACLE中，hints是一种机制，用于告知优化器如何生成执行计划，包括优化器类型、优化目标（如all_rows或first_rows）、表的访问路径（全表扫描、索引扫描或直接利用rowid）、表间连接类型和顺序，以及语句的并行程度。

以下是分析执行计划的示例演示：假设有一个名为LARGE_TABLE的大表，且username列上没有索引。运行以下SQL语句：SQL> SELECT * FROM LARGE_TABLE WHERE USERNAME = 'TEST'; 查询计划 ----- SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]在这个示例中，第一个操作是TABLE ACCESS FULL LARGE_TABLE，表示对LARGE_TABLE表进行全表扫描。扫描完成后，结果集数据传递到下一个处理步骤，即SELECT STATEMENT操作，它是查询语句的最后一步。Optimizer=CHOOSE指明了查询的优化器模式，即初始化参数中指定的optimizer_mode的值。这并不意味着实际执行时使用了该优化器。决定使用哪种优化器的唯一方法是查看cost部分。如果形式如下所示，则使用的是CBO优化器，cost表示优化器认为执行计划的代价。

在Oracle数据库中，分析执行计划是优化查询的重要步骤。以下是一个示例，展示了如何分析一个查询的执行计划。假设LARGE_TABLE是一个较大的表，且USERNAME列没有索引，运行如下语句： SELECT * FROM LARGE_TABLE WHERE USERNAME = 'TEST'; 执行计划: SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED] 分析结果：1. TABLE ACCESS FULL LARGE_TABLE 是第一个操作，表示对 LARGE_TABLE 表进行了全表扫描。2. Optimizer=CHOOSE 指示使用的优化器模式为 CHOOSE，这意味着查询优化器会根据表统计信息自动选择最适合的执行计划。3. Cost=1234 表示该执行计划的代价值，值越高表示执行成本越大。4. Card=1 和 Bytes=14 分别代表该操作返回的行数和字节数。5. 执行完全表扫描后，数据会被送往最终的查询结果。 这个执行计划表明查询在没有索引时，Oracle选择了全表扫描方式，这可能不是最优选择，特别是在表非常大的情况下，优化建议可能包括为USERNAME列创建索引以减少查询的I/O开销。

在ORACLE数据库中，执行计划的选择是关键优化点。所有可能的执行计划形成一个树型结构，通过有效遍历可以估算出最优的执行计划。通常由于时间限制，不可能遍历所有可能性，因此可能会选择出不正确的执行计划。