关于#oracle#的问题：explain执行计划解读

Question

查看oracle的sql执行计划，怎么从几百行执行步骤里找出缩进最多的操作？毕竟这个explain是按缩进量多少判断执行先后顺序的，步骤多了缩进多少又难以用肉眼区分排序

Answer 1

下午好🌅🌅🌅
本答案参考通义千问

在 Oracle 中，使用 EXPLAIN PLAN 查看 SQL 的执行计划时，确实会遇到很多行的执行步骤，尤其是当 SQL 复杂、涉及多个表连接或子查询时。执行顺序是通过缩进量来判断的，缩进越多，表示该操作越靠后执行，通常也意味着它是父操作的一部分。

---

一、为什么需要找出“缩进最多”的操作？

重点说明：

• 缩进最多的操作通常是最内层的执行步骤，也就是最先执行的操作。
• 它可能是一个表扫描（如 TABLE ACCESS FULL）、索引扫描（INDEX RANGE SCAN）等。
• 找出它有助于理解整个 SQL 的执行流程，尤其是从底层数据获取开始的逻辑。

---

二、如何高效地找出缩进最多的操作？

方法一：使用 PLAN_TABLE 并按缩进排序

你可以将执行计划插入到一个临时表中，然后通过 ID 和 PARENT_ID 来判断缩进层级，从而找到最深的节点。

示例代码：

-- 创建执行计划表（如果未创建）
BEGIN
  DBMS_SQL.EXECUTE_IMMEDIATE('DROP TABLE plan_table');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;
/

CREATE TABLE plan_table (
  statement_id VARCHAR2(30),
  plan_id      NUMBER,
  timestamp    DATE,
  operation    VARCHAR2(32),
  options      VARCHAR2(32),
  object_name  VARCHAR2(64),
  cost         NUMBER,
  bytes        NUMBER,
  cpu_cost     NUMBER,
  io_cost      NUMBER,
  time         NUMBER,
  access_predicates VARCHAR2(4000),
  partition_start VARCHAR2(255),
  partition_stop  VARCHAR2(255),
  other_segment VARCHAR2(255),
  other        VARCHAR2(255)
);

-- 设置当前SQL的执行计划
SET AUTOTRACE OFF
EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE some_column = 'some_value';

-- 插入到plan_table
INSERT INTO plan_table (statement_id, plan_id, timestamp, operation, options, object_name, cost, bytes, cpu_cost, io_cost, time, access_predicates, partition_start, partition_stop, other_segment, other)
SELECT 
  'YOUR_STATEMENT',
  plan_id,
  timestamp,
  operation,
  options,
  object_name,
  cost,
  bytes,
  cpu_cost,
  io_cost,
  time,
  access_predicates,
  partition_start,
  partition_stop,
  other_segment,
  other
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));

-- 查询最深的节点（缩进最多）
SELECT *
FROM plan_table
WHERE statement_id = 'YOUR_STATEMENT'
ORDER BY plan_id DESC;

注意： plan_id 是递增的，但不是严格的缩进层级。更准确的是通过 parent_id 来判断层级。

---

方法二：使用 DBMS_XPLAN.DISPLAY_CURSOR 直接查看带缩进的执行计划

如果你不想自己处理 plan_table，可以直接使用以下方式查看带有缩进的执行计划，并用文本编辑器（如 VSCode、Notepad++）进行搜索：

SET LINESIZE 1000
SET PAGESIZE 1000
SET LONG 1000000

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'TYPICAL'));

在输出结果中，每一行前面的空格数量代表了缩进层级，空格越多，表示该操作越深，即越靠近底层操作。

---

方法三：使用工具自动分析执行计划

如果你经常需要分析执行计划，可以考虑使用以下工具：

| 工具名称 | 说明 | |----------|------| | SQL Developer | 提供图形化界面，可清晰展示执行计划树状图，便于理解缩进层级 | | Toad for Oracle | 同样支持图形化执行计划分析 | | Oracle Enterprise Manager (OEM) | 支持高级执行计划分析和性能调优 |

---

三、总结：如何快速找到缩进最多的操作？

1. 使用 DBMS_XPLAN.DISPLAY_CURSOR 查看带缩进的执行计划

   重点关注每一行前的空格数量。

2. 使用 plan_table 或 v$sql_plan 表，结合 id 和 parent_id 判断层级

   可以编写 SQL 查询，找到最深层的节点。

3. 借助图形化工具（如 SQL Developer、Toad）

   更直观，适合复杂执行计划分析。

4. 编写自定义 SQL 脚本，自动识别最深的执行步骤

   可用于自动化分析或性能监控。

---

四、示例：如何手动找最深的节点？

假设你有如下执行计划片段（简化版）：

   0 SELECT STATEMENT Optimizer=ALL_ROWS
   1   HASH JOIN
   2     TABLE ACCESS FULL T1
   3     TABLE ACCESS FULL T2

• 最深的节点是 TABLE ACCESS FULL T1 和 TABLE ACCESS FULL T2，它们的缩进最多（两层），是最先执行的操作。

---

五、注意事项

• 执行计划的缩进仅反映逻辑结构，不一定是物理执行顺序（例如，某些并行操作可能实际同时执行）。
• 使用 EXPLAIN PLAN 时，确保 SQL 语句被正确解析，否则可能导致执行计划不准确。
• 对于复杂的 SQL，建议结合 V$SQL_PLAN 或 V$SQL 进行分析。

---

如果你能提供具体的执行计划内容或 SQL 语句，我可以进一步帮你分析哪些操作是最深层的。