在不使用第三方工具的前提下，目前总结了四种查看执行计划的方法。供大家参考和指正。若有补充和疑义，欢迎大家一起探讨。
方法1：explain for <SQL语句>;
select * from table(dbms_xplan.display);
例如：
SQL> explain plan for select * from t2;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 128 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
方法2：set autotrace on
执行<SQL语句>
例如：
SQL> set autotrace on
SQL> select * from hr.t2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 128 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
注意：若是使用非DBA角色的用户下进行执行计划的查看,则要用DBA权限的用户通过执行plustrce.sql脚本来先创建角色‘PLUSTRACE’，然后将角色赋予当前用户。操作方法类似如下：
SQL> show user
USER is "SYS"
SQL> @?/sqlplus/admin/plustrce.sql
--以下为自动输出
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
--以下需要手工输入该命令
SQL> grant plustrace to hr;
Grant succeeded.
方法3：查看v$sql_plan表
通过SQL语句的SQL_ID和子游标号，可以在V$SQL_PLAN表中查看到该SQL语句的执行计划。
例如：执行了语句“select * from t1;',先通过v$sqlarea和v$sql找到该语句的SQL_ID和子游标号。
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r select * from t1
bf45pybkumcx5 select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%'
SQL> select sql_id,child_number,sql_text from v$sql where sql_id='27uhu2q2xuu7r';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r 0 select * from t1
--通过以上两条查询语句，查得目标SQL语句的SQL_ID为 “27uhu2q2xuu7r”， 子游标号为“0”.
SQL> select timestamp,operation,options,object_name,cost,id,parent_id from v$sql_plan where sql_id='27uhu2q2xuu7r' and child_number=0;
TIMESTAMP OPERATION OPTIONS OBJECT_NAME COST IDPARENT_ID
----------------- -------------------- ---------- ------------------------------ ---------- ---------- ----------
20131221 20:49:14 SELECT STATEMENT 3 0
20131221 20:49:14 TABLE ACCESS FULL T1 3 1 0
方法4：查到指定语句的SQL_ID和子游标号后（查找方法请见方法3），通过DBMS_XPLAN包进行查看。
例如：假设目标语句的SQL_ID和子游标号同方法3.
SQL> select * from table(dbms_xplan.display_cursor('27uhu2q2xuu7r',0,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| T1 | 6 | 96 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
13 rows selected.
