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

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

news 2025/6/26 8:51:47

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

1. 使用 EXPLAIN PLAN FOR 和 DBMS_XPLAN.DISPLAY

方法

  1. 执行 EXPLAIN PLAN FOR 语句：

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

  2. 查看执行计划：

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
优点
  • 简单易用：适用于大多数情况，操作简单。
  • 详细信息：可以提供详细的执行计划信息，包括操作类型、成本、行数等。
缺点
  • 不反映实际执行：EXPLAIN PLAN 只是模拟执行计划，不一定反映实际执行情况。
  • 需要权限：需要 EXPLAIN PLAN 权限。

2. 使用 DBMS_XPLAN.DISPLAY_CURSOR

方法

  1. 执行 SQL 语句：

    SELECT * FROM your_table WHERE your_column = 'some_value';

  2. 查看执行计划：

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
优点
  • 反映实际执行：查看的是实际执行的计划，更能反映真实的性能情况。
  • 详细统计信息：可以提供实际的执行统计信息，如 I/O 次数、CPU 时间等。
缺点
  • 需要执行 SQL：必须先执行 SQL 语句，才能查看执行计划。
  • 依赖共享池：只能查看在共享池中的 SQL 语句的执行计划。

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

方法

  1. 启用 AUTOTRACE：

    SET AUTOTRACE ON EXPLAIN;

  2. 执行 SQL 语句：

    SELECT * FROM your_table WHERE your_column = 'some_value';

  3. 禁用 AUTOTRACE：

    SET AUTOTRACE OFF;
优点
  • 集成在 SQL*Plus：适用于 SQL*Plus 用户，操作简便。
  • 即时反馈：执行 SQL 语句时立即显示执行计划。
缺点
  • 仅限 SQL*Plus：只能在 SQL*Plus 中使用。
  • 功能有限：不如 DBMS_XPLAN.DISPLAY 提供的信息详细。

4. 使用 V$SQL_PLAN 视图

方法

  1. 找到 SQL 语句的 SQL_ID：

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

  2. 查询执行计划：

    SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id';
优点
  • 灵活性高：可以直接查询视图，灵活度高。
  • 实时信息：可以查看当前正在执行的 SQL 语句的执行计划。
缺点
  • 复杂性：需要手动查询视图，操作相对复杂。
  • 信息冗余：返回的信息较多，需要筛选有用的部分。

5. 使用 Oracle Enterprise Manager (OEM)

方法
  1. 登录 OEM。
  2. 导航到 SQL 性能页面。
  3. 输入 SQL 语句并查看执行计划。
优点
  • 图形界面：提供图形化的用户界面，易于理解和操作。
  • 综合信息：可以查看多种性能指标，不仅仅是执行计划。
缺点
  • 需要 OEM：需要安装和配置 Oracle Enterprise Manager。
  • 资源消耗：图形界面可能消耗更多系统资源。

6. 使用 DBMS_XPLAN.DISPLAY_AWR

方法

  1. 找到 SQL 语句的 SQL_ID 和 PLAN_HASH_VALUE：

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

  2. 查询执行计划：

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', 'your_plan_hash_value'));
优点
  • 历史信息：可以查看 AWR 中的历史执行计划，有助于长期性能分析。
  • 详细统计：提供详细的执行统计信息。
缺点
  • 需要 AWR：需要 AWR 功能开启，且需要相应的权限。
  • 复杂性：操作相对复杂，需要查找 SQL_ID 和 PLAN_HASH_VALUE。

7. 使用事件 10046 跟踪

方法

  1. 启用事件 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);

  2. 执行 SQL 语句：

    SELECT * FROM your_table WHERE your_column = 'some_value';

  3. 禁用事件 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);

  4. 查看跟踪文件：

    • 查找跟踪文件的位置，通常在

      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

方法

  1. 设置统计级别为 ALL：

    ALTER SESSION SET STATISTICS_LEVEL=ALL;

  2. 执行 SQL 语句：

    SELECT * FROM your_table WHERE your_column = 'some_value';

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

    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：适用于需要详细统计信息和反映实际执行情况的场景，操作简单但有性能开销。

希望这些方法和优劣比较对你有所帮助！

http://www.lryc.cn/news/491090.html

相关文章：

  • TCL大数据面试题及参考答案
  • 九、FOC原理详解
  • vue页面成绩案例(for渲染表格/删除/添加/统计总分/平均分/不及格显红色/输入内容去首尾空格trim/输入内容转数字number)
  • STM32编程小工具FlyMcu和STLINK Utility 《通俗易懂》破解
  • Centos使用docker搭建Graylog日志平台
  • 自定义 Kafka 脚本 kf-use.sh 的解析与功能与应用示例
  • 【SQL】【数据库】语句翻译例题
  • linux基本命令2
  • Spring Boot项目集成Redisson 原始依赖与 Spring Boot Starter 的流程
  • Git命令使用与原理详解
  • Linux：自定义Shell
  • vue项目中中怎么获取环境变量
  • C#里怎么样使用正则表达式？
  • 《生成式 AI》课程 第5講：訓練不了人工智慧？你可以訓練你自己 (下)
  • Vue 动态给 data 添加新属性深度解析：问题、原理与解决方案
  • 【Pytest+Yaml+Allure】实现接口自动化测试框架
  • el-input绑定点击回车事件意外触发页面刷新
  • Golang的语言特性与鸭子类型
  • 如何在Linux系统中排查GPU上运行的程序
  • VSCode 新建 Python 包/模块 Pylance 无法解析
  • Unet++改进44：添加MogaBlock(2024最新改进模块)|在纯基于卷积神经网络的模型中进行判别视觉表示学习，具有良好的复杂性和性能权衡。
  • 计算机网络（14）ip地址超详解
  • 【C语言】野指针问题详解及防范方法
  • 【SVN和GIT】版本控制系统详细下载使用教程
  • 【Vue】Vue3.0（二十六）Vue3.0中的作用域插槽
  • 神经网络（系统性学习二）：单层神经网络（感知机）
  • CTF之密码学（BF与Ook）
  • 【TEST】Apache JMeter + Influxdb + Grafana
  • SpringBoot集成多个rabbitmq
  • 从零开始学习数据库 day0（基础）