查看已执行过SQL的执行计划（dems_xplan.display_cursor)

最新推荐文章于 2022-08-23 17:04:04 发布

转载最新推荐文章于 2022-08-23 17:04:04 发布 · 84 阅读

本文通过一个具体的SQL查询示例，展示了如何使用Oracle数据库的dbms_xplan工具来获取SQL语句的执行计划。这有助于理解数据库如何执行查询，并进行性能调优。

SQL> select hash_value,child_number,sql_text from v$sql where sql_text like 'select status%';

HASH_VALUE CHILD_NUMBER
---------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
155446740 0
select status from test1 where wner='XDB'

SQL> select * from table(dbms_xplan.display_cursor(155446740,0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
HASH_VALUE 155446740, child number 0
-------------------------------------
select status from test1 where wner='XDB'

Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |   561 (100)|          |
|* 1 | TABLE ACCESS FULL| TEST1 | 3020 | 39260 |   561   (1)| 00:00:07 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------

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

1 - SEL$1 / TEST1@SEL$1

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

/*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST1"@"SEL$1")
      END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

1 - filter("OWNER"='XDB')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "STATUS"[VARCHAR2,7]

42 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26655292/viewspace-756055/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26655292/viewspace-756055/