ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看SQL语句的执行计划

查看SQL语句的执行计划

原创 Linux操作系统 作者:gaiguoxiang 时间:2012-05-20 13:28:52 0 删除 编辑

方法一:先执行SQL再出执行计划

SQL> set autotrace on explain
SQL> select username from V$session where username is not null;

USERNAME
------------------------------
SYS


执行计划
----------------------------------------------------------
Plan hash value: 644658511

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 108 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 95 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 69 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

3 - filter("S"."KSUUDLNA" IS NOT NULL AND "S"."INST_ID"=USERENV('INSTANCE') AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."INDX"="W"."KSLWTSID")
5 - filter("W"."KSLWTEVT"="E"."INDX")

SQL> set autotrace off;

方法二:在执行SQL之前先看执行计划

SQL> explain plan for select username from V$session where username is not null;

已解释。

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

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

Plan hash value: 644658511

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 108 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 95 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 69 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

3 - filter("S"."KSUUDLNA" IS NOT NULL AND "S"."INST_ID"=USERENV('INSTANCE') AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."INDX"="W"."KSLWTSID")
5 - filter("W"."KSLWTEVT"="E"."INDX")

已选择20行。

方法三:通过SQL ID来查看SQL的执行过程

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('089dbukv1aanh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID 089dbukv1aanh
--------------------
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

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

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

请登录后发表评论 登录
全部评论

注册时间:2011-06-22

  • 博文量
    41
  • 访问量
    110354