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

在 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

参数指定的目录下。

使用

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 详细统计信息,反映实际执行,操作简单 性能开销,临时设置

适用场景

  • EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY:适用于简单的查询优化,快速查看执行计划。
  • DBMS_XPLAN.DISPLAY_CURSOR:适用于已经执行的 SQL 语句,需要查看实际执行情况。
  • AUTOTRACE:适用于 SQL*Plus 用户,需要快速反馈。
  • V$SQL_PLAN:适用于需要灵活查询执行计划的场景。
  • Oracle Enterprise Manager (OEM):适用于需要图形化界面和综合性能信息的场景。
  • DBMS_XPLAN.DISPLAY_AWR:适用于需要查看历史执行计划的场景。
  • 事件 10046 跟踪:适用于需要深入分析性能问题,特别是涉及执行时间和等待事件的场景。
  • STATISTICS_LEVEL=ALL:适用于需要详细统计信息和反映实际执行情况的场景,操作简单但有性能开销。

以上就是Oracle执行计划查看方法汇总及优劣对比的详细内容,更多关于Oracle执行计划查看方法的资料请关注脚本之家其它相关文章!

来源链接:https://www.jb51.net/database/3313117al.htm

© 版权声明
THE END
支持一下吧
点赞12 分享
评论 抢沙发
头像
请文明发言!
提交
头像

昵称

取消
昵称表情代码快捷回复

    暂无评论内容