方法一：先执行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
------------------------------------------------------------------
-----------------------------------------------------------------