[Oracle]如何查看SQL的执行计划 - DBMS_XPLAN Package

查看执行计划除了AUTOTRACE的方法外，还可以使用DBMS_XPLAN Package来查看

1.执行"explain Plan"

语法:
SQL> Explain plan for

Try this command:
SQL> explain plan for select * from mtl_system_items_b where inventory_item_id = 149 and organization_id = 207;
Explained

这个命令会产生执行计划，并把执行计划保存到"PLAN_TABLE"中。

2.查看执行计划
SQL> select * from table(dbms_xplan.display);

(dbms_xplan.display() is a packaged function used to view the execution plan generated.)

Output like:

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 780806759

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |     1 |   554 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MTL_SYSTEM_ITEMS_B    |     1 |   554 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | MTL_SYSTEM_ITEMS_B_U1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("INVENTORY_ITEM_ID"=149 AND "ORGANIZATION_ID"=207)

已选择14行。