oracle查看执行计划的三种方法

最新推荐文章于 2025-06-28 14:00:00 发布

香雪海之孤狐

最新推荐文章于 2025-06-28 14:00:00 发布

阅读量605

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/ltt4172406/article/details/38369911

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了Oracle数据库中Autotrace功能的设置方法及其应用场景，包括如何通过不同命令展示执行计划与统计信息，并提供了使用SQL及Toad等工具的具体示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.1设置autotrace

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

SQL>set autotrace on

SQL>select * from dave;

ID NAME

---------- ----------

8 安庆

1 dave

2 bl

1 bl

2 dave

3 dba

4 sf-express

5 dmm

已选择8行。

执行计划

----------------------------------------------------------

Plan hash value: 3458767806

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

609 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

SQL>

1.2使用SQL

SQL>EXPLAIN PLAN FOR sql语句;

SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

示例：

SQL>EXPLAIN PLAN FOR SELECT * FROM DAVE;

已解释。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3458767806

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

已选择8行。

执行计划

----------------------------------------------------------

Plan hash value: 2137789089

--------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

25 recursive calls

12 db block gets

168 consistent gets

0 physical reads

0 redo size

974 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

8 rows processed

SQL>

1.3使用Toad,PL/SQL Developer工具