如何优化ORACLE SQL执行计划

以下是分析执行计划的示例演示：假设有一个名为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执行计划一、执行计划概述二、数据访问方法详解三、执行计划层次分析四、案例说明五、表格访问策略六、表格连接方式七、运算符介绍

生成SQL语句的执行计划有多种方法：1) 最简单的方式是在SQL> set autotrace on命令下执行select * from dual; 执行后会显示执行计划和统计信息。这种方法的优点是直观，缺点是执行时间较长，特别是对于复杂的SQL语句。如果只需生成执行计划而不执行语句，可以使用SQL> set autotrace traceonly命令，这样只会列出执行计划而不执行实际语句，大大缩短了优化时间。另外，在分析用户下，可以使用SQL> @?rdbmsadminutlxplan.sql命令，或者以sys用户登陆使用SQL> @?sqlplusadminplustrce.sql，还可以授予plustrace权限给指定用户。

如何分析执行计划通过如下示例进行分析演示： 例1：假设LARGE_TABLE是一个较大的表，且username列上没有索引，则运行下面的语句：SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST';Query Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)TABLE ACCESS FULL LARGE_TABLE 在这个例子中，TABLE ACCESS FULL LARGE_TABLE是第一个操作，意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后，产生的row source中的数据被送往下一步骤进行处理，在此例中，SELECT STATEMENT操作是这个查询语句的最后一步。Optimizer=CHOOSE指明这个查询的optimizer_mode，即optimizer_mode初始化参数指定的值，它并不是指语句执行时真的使用了该优化器。决定该语句使用何种优化器的唯一方法是看后面的cost部分。如果给出的是下面的形式，则表明使用的是CBO优化器，此处的cost表示优化器认为该执行计划的代价:SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)

为了生成一条语句的执行计划，您可以采用以下三种方法：1）最简单的方法是使用Sql> set autotrace on命令，然后执行您的SQL语句。执行完成后，将显示执行计划和统计信息。尽管这种方法易于使用，但在处理较长的SQL语句时，等待执行完成会显著延长优化周期。若只需查看执行计划而无需执行实际语句，可以使用Sql> set autotrace traceonly命令。这样只会显示执行计划，大大减少了优化时间。虽然此方法也会显示统计信息，但由于未执行实际语句，因此此统计信息可能无效。若遇到执行错误，可通过以下方法解决：a. 以要分析的用户身份运行：Sqlplus > @ ?\rdbms\admin\utlxplan.sql；b. 使用sys用户登录Sqlplus > @ ?\sqlplus\admin\plustrce.sql；Sqlplus > grant plustrace to user_name；-- 其中user_name为上述分析用户。

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

在EXPLAIN PLAN输出中，执行顺序始于最右边缩进的行。接下来的步骤是该行的父级。如果两行缩进相同，则通常首先执行顶部行。通过案例分析来阐述如何阅读执行计划。

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