当前位置：首页 > article >正文

Oracle 执行计划查看方法汇总及优劣对比

article 2024/11/26 2:04:17

在 Oracle 数据库中，查看执行计划是优化 SQL 语句性能的重要工具。以下是几种常用的查看执行计划的方法及其优劣比较：

1. 使用 `EXPLAIN PLAN FOR` 和 `DBMS_XPLAN.DISPLAY`

方法

执行 EXPLAIN PLAN FOR 语句：

EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE your_column = 'some_value';

查看执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

优点

简单易用：适用于大多数情况，操作简单。
详细信息：可以提供详细的执行计划信息，包括操作类型、成本、行数等。

缺点

不反映实际执行：EXPLAIN PLAN 只是模拟执行计划，不一定反映实际执行情况。
需要权限：需要 EXPLAIN PLAN 权限。

2. 使用 `DBMS_XPLAN.DISPLAY_CURSOR`

方法

执行 SQL 语句：

SELECT * FROM your_table WHERE your_column = 'some_value';

查看执行计划：

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

优点

反映实际执行：查看的是实际执行的计划，更能反映真实的性能情况。
详细统计信息：可以提供实际的执行统计信息，如 I/O 次数、CPU 时间等。

缺点

需要执行 SQL：必须先执行 SQL 语句，才能查看执行计划。
依赖共享池：只能查看在共享池中的 SQL 语句的执行计划。

3. 使用 `AUTOTRACE`（仅限 SQL*Plus）

方法

启用 AUTOTRACE：
```
SET AUTOTRACE ON EXPLAIN;
```

执行 SQL 语句：

SELECT * FROM your_table WHERE your_column = 'some_value';

禁用 AUTOTRACE：
```
SET AUTOTRACE OFF;
```

优点

集成在 SQL*Plus：适用于 SQL*Plus 用户，操作简便。
即时反馈：执行 SQL 语句时立即显示执行计划。

缺点

仅限 SQL*Plus：只能在 SQL*Plus 中使用。
功能有限：不如 DBMS_XPLAN.DISPLAY 提供的信息详细。

4. 使用 `V$SQL_PLAN` 视图

方法

找到 SQL 语句的 SQL_ID：

SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%your_sql_statement%';

查询执行计划：

SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id';

优点

灵活性高：可以直接查询视图，灵活度高。
实时信息：可以查看当前正在执行的 SQL 语句的执行计划。

缺点

复杂性：需要手动查询视图，操作相对复杂。
信息冗余：返回的信息较多，需要筛选有用的部分。

5. 使用 Oracle Enterprise Manager (OEM)

方法

登录 OEM。
导航到 SQL 性能页面。
输入 SQL 语句并查看执行计划。

优点

图形界面：提供图形化的用户界面，易于理解和操作。
综合信息：可以查看多种性能指标，不仅仅是执行计划。

缺点

需要 OEM：需要安装和配置 Oracle Enterprise Manager。
资源消耗：图形界面可能消耗更多系统资源。

6. 使用 `DBMS_XPLAN.DISPLAY_AWR`

方法

找到 SQL 语句的 SQL_ID 和 PLAN_HASH_VALUE：

SELECT sql_id, plan_hash_value FROM dba_hist_sqlstat WHERE sql_text LIKE '%your_sql_statement%';

查询执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', 'your_plan_hash_value'));

优点

历史信息：可以查看 AWR 中的历史执行计划，有助于长期性能分析。
详细统计：提供详细的执行统计信息。

缺点

需要 AWR：需要 AWR 功能开启，且需要相应的权限。
复杂性：操作相对复杂，需要查找 SQL_ID 和 PLAN_HASH_VALUE。

7. 使用事件 10046 跟踪

方法

启用事件 10046 跟踪：

对于当前会话：

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

对于特定的会话（假设 SID 为 123，SERIAL# 为 456）：
```
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, TRUE);
```

执行 SQL 语句：

SELECT * FROM your_table WHERE your_column = 'some_value';

禁用事件 10046 跟踪：

对于当前会话：

ALTER SESSION SET EVENTS '10046 trace name context off';

对于特定的会话（假设 SID 为 123，SERIAL# 为 456）：
```
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, FALSE);
```

查看跟踪文件：
- 查找跟踪文件的位置，通常在
```
user_dump_dest
```
  参数指定的目录下。
```
SHOW PARAMETER user_dump_dest
```
- 使用
```
tkprof
```
  工具格式化跟踪文件：
```
tkprof trace_file.trc output_file.txt explain=your_username/your_password
```
- 查看生成的 output_file.txt 文件，其中包含详细的执行计划和性能信息。

优点

详细信息：提供详细的执行计划、执行时间和等待事件等信息，有助于深入分析性能问题。
灵活性：可以针对特定的会话或当前会话启用跟踪。
历史信息：可以保留长时间的跟踪信息，便于后续分析。

缺点

性能开销：启用跟踪会增加系统开销，特别是在高负载情况下。
复杂性：操作相对复杂，需要手动启用和禁用跟踪，以及使用 tkprof 格式化跟踪文件。
文件管理：需要管理和清理生成的跟踪文件，以免占用过多磁盘空间。

8. 使用 `STATISTICS_LEVEL=ALL`

方法

设置统计级别为 ALL：

ALTER SESSION SET STATISTICS_LEVEL=ALL;

执行 SQL 语句：

SELECT * FROM your_table WHERE your_column = 'some_value';

查看执行计划和统计信息：

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

优点

详细统计信息：可以提供详细的执行计划和统计信息，包括 I/O 次数、CPU 时间等。
反映实际执行：查看的是实际执行的计划，更能反映真实的性能情况。
操作简单：只需设置统计级别并执行 SQL 语句即可。

缺点

性能开销：设置 STATISTICS_LEVEL 为 ALL 会增加执行 SQL 语句的性能开销。
临时设置：仅对当前会话有效，需要在每个会话中手动设置。

总结

方法	优点	缺点
`EXPLAIN PLAN FOR` + `DBMS_XPLAN.DISPLAY`	简单易用，详细信息	不反映实际执行，需要权限
`DBMS_XPLAN.DISPLAY_CURSOR`	反映实际执行，详细统计信息	需要执行 SQL，依赖共享池
`AUTOTRACE`	集成在 SQL*Plus，即时反馈	仅限 SQL*Plus，功能有限
`V$SQL_PLAN`	灵活性高，实时信息	复杂性高，信息冗余
Oracle Enterprise Manager (OEM)	图形界面，综合信息	需要 OEM，资源消耗
`DBMS_XPLAN.DISPLAY_AWR`	历史信息，详细统计	需要 AWR，复杂性
事件 10046 跟踪	详细信息，灵活性高，历史信息	性能开销，复杂性，文件管理
`ALTER SESSION SET STATISTICS_LEVEL=ALL`	详细统计信息，反映实际执行，操作简单	性能开销，临时设置