查看Oracle SQL执行计划的常用方式

最新推荐文章于 2025-05-23 09:54:10 发布

转载最新推荐文章于 2025-05-23 09:54:10 发布 · 90 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/SUN-PH/p/4837490.html

文章标签：

#数据库

本文详细介绍了在SQL查询中使用三种不同的方法来查看执行计划：通过`explain plan for`命令、`dbms_xplan.display`函数以及`dbms_xplan.display_cursor`函数。这些方法有助于理解查询执行的细节，优化查询性能。

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

在查看SQL执行计划的时候有很多方式

我常用的方式有三种

SQL> explain plan for
2 select * from scott.emp where ename='KING';

已解释。

第一种最常用的

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

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='KING')

已选择13行。

第二种

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 2637181423

---------------------------------------------------
| Id  | Operation          | Name        | E-Rows |
---------------------------------------------------
|   0 | DELETE STATEMENT   |             |        |
|   1 |  DELETE            | PLAN_TABLE$ |        |
|*  2 |   TABLE ACCESS FULL| PLAN_TABLE$ |      1 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATEMENT_ID"=:1)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


已选择26行。

第三种

SQL> select * from table(dbms_xplan.display(null,null,'ADVANCED -PROJECTION'));

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='KING')

已选择32行。