Oracle执行计划详解

一、执行计划的基本概念

Oracle执行计划是优化器读取用户提交的SQL语句后，生成的一份查询执行的步骤及各步骤的执行顺序和资源消耗情况的详细说明。

执行计划可以通过多种方式获取，比如执行explain plan语句、通过SQL Developer的执行计划分析器、观察AWR快照等。

执行计划中包含的最重要的信息是该语句的I/O操作和CPU资源的利用情况，执行计划中的每个步骤都会告诉我们有多少行数据是被访问的，以及每条记录在内存或IO上的耗时和资源消耗情况，然后我们就可以对这个结果进行分析来确定SQL查询的优化空间。

二、执行计划中的基础内容

执行计划中包含了许多重要的信息，比如访问路径、访问操作的类型等，这些信息是我们进行SQL优化的基础。下面我们介绍一下执行计划中的基础内容。

1. 访问方式

执行计划中的访问方式用来表示访问底层数据的方式，主要有几种访问方式：

• Full Table Scan（全表扫描）：扫描整个表，找出符合条件的数据。
• Index Unique Scan（唯一索引扫描）：在索引树中找到唯一一行数据。
• Index Range Scan（范围索引扫描）：在索引树中找到一段范围内的数据。
• Index Full Scan（索引全扫描）：把索引树上的所有数据都扫描一遍。

2. Join方式

Join是SQL中的一个重要操作，它包括几种常见的方式：

• Nested Loop Join（嵌套循环）：对于每个主表数据，访问从表查询匹配条件。
• Hash Join（哈希连接）：把较小表的数据读入内存，建立哈希表，然后把另一个较大的表数据与哈希表上匹配。
• Sort Merge Join（排序合并）：对连接的两个表都进行排序，然后进行合并操作。

3. 计划步骤

执行计划中的每一个步骤都表示一个SQL执行过程中的某个阶段，每个步骤都会执行一个SQL操作。一个SQL语句可以有多个执行步骤，每个步骤都会将前一个步骤的结果作为输入，依次处理每个步骤的数据，最终得到结果。

在每个步骤中会包括访问方式、访问对象、数据输入输出信息等详细的信息。

三、执行计划的优化

为了让SQL语句的执行效率更快，我们需要根据执行计划中的信息进行优化。

1. 优化数据访问路径

通过修改执行计划中的访问方式，我们可以通过建立索引或者修改查询语句等方式来优化数据的访问路径，从而提高SQL语句的执行效率。

2. 数据库统计信息的优化

数据库统计信息会直接影响到查询优化器生成的执行计划，因此我们需要定期收集统计信息，并且对于频繁执行的SQL，可以通过使用添加一些hint提示来引导查询优化器制定最优的执行计划。

3. 减少排序和分组操作

排序和分组是SQL语句中常见的操作，但是如果排序和分组操作过多，就会导致SQL执行效率低下。因此我们需要尽量通过减少排序和分组操作或者通过优化SQL语句来提高SQL执行效率。

四、示例代码

1. 获取执行计划

-- 获取SQL执行计划
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
-- 查看执行计划
SELECT * FROM TABLE(dbms_xplan.display);

2. 优化SQL语句

-- 添加hint提示进行优化
SELECT /*+ INDEX(emp emp_dept_idx) */ * FROM employees emp WHERE department_id = 10;

3. 收集统计信息

-- 收集表的统计信息
ANALYZE TABLE employees COMPUTE STATISTICS;
-- 收集索引的统计信息
ANALYZE INDEX emp_dept_idx COMPUTE STATISTICS;

4. 使用SQL Tuning Advisor进行优化

-- 使用SQL Tuning Advisor自动优化SQL
DECLARE
  advisor_task_id    VARCHAR2(30);
BEGIN
  advisor_task_id := DBMS_SQLTUNE.create_tuning_task(sql_text => 'SELECT * FROM employees WHERE department_id = 10',
                                                    scope    => DBMS_SQLTUNE.scope_comprehensive,
                                                    time_limit => 60);
  DBMS_OUTPUT.put_line('Advisor Task Id: ' || advisor_task_id);
END;
/

5. 分析AWR报告

-- 生成AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
-- 分析当前实例在过去1小时内的AWR报告
SELECT *
FROM TABLE(dbms_workload_repository.awr_report_html(DBMS_WORKLOAD_REPOSITORY.Report_Package_Type_Hourly,SYSDATE-1/24, SYSDATE));

总结

在进行SQL语句的优化过程中，执行计划是一个非常重要的工具，通过分析执行计划中的信息，我们可以找到SQL执行中存在的问题，然后根据问题进行相应的优化，最终提高SQL执行效率。