我要评论在 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执行计划查看方法的资料请关注代码网其它相关文章！
发表评论