A-A+
查看sql执行计划的方法
对于oracle dba来说，其中最多的时间是放在数据库优化上的，数据库优化也是衡量一个dba价值的重要方面，这个也在很多刚入门的dba去面试dba职位时被问及的最多并且也是必不可少的一个问题。而对于数据库优化，基本是对sql的优化，而要优化一条sql，则获取sql的执行计划便是第一步。
下边整理了7种oracle常用的获取sql执行计划的途径，包括获取当前sql的执行计划，获取sql的历史执行计划等。希望能帮助到大家。
（一）使用explain for 解释sql的执行计划，通过dbms_xplan()函数查看解释后的执行计划
注意：该方法并没有真正的执行指定的sql，而是将指定的sql进行了解释而已，并且在library cache中存在的该sql将是explain for select…
而不是指定的sql。
示例如下：
SQL> explain plan for
2 select * from scott.emp e where e.deptno in (select deptno from scott.dept);
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3074306753
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 560 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="DEPTNO")
15 rows selected.
（二）使用dbms_xplan.display_cursor查看真实执行计划（10G以上）
使用这个函数可以显示内存中一个或者多个游标的执行计划。
语法：
Select * from table(dbms_xplan.display_cursor('hash_value','child_number','advanced'));
注意：如果第一个和第二个参数未设置，则会返回内存中最近的一个sql的执行计划，第三个参数用来设置格式输出
如下：
SQL> select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ah916jr1wmqbg, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in
(select deptno from scott.dept) and rownum<3
Plan hash value: 653357053
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 2 | 74 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
4 - access("E"."DEPTNO"="DEPTNO")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23 rows selected.
可以通过设置第一个和第二个参数显示指定的sql的真实执行计划
示例：
SQL> set line 200
SQL> select /*yallonking*/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL> col SQL_TEXT for a70
SQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%yallonking%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ ----------------------------------------------------------------------
abcarhr7w5g8b 3485646091 0 select /*yallonking*/ * from scott.emp e where e.deptno in (select dep
tno from scott.dept) and rownum<3
d625bbv7utkq6 3484207814 0 select sql_id,hash_value,child_number,sql_text from v$sql where sql_te
xt like '%yallonking%'
dymhjyqqc7x7z 2898523391 0 select hash_value,child_number,sql_text from v$sql where sql_text like
'%yallonking%'
注意：此处人为执行了一个sql（该sql加了注释，便于后边识别），然后从找到该sql的hash_value和子游标号（3485646091 0），此处也可以看到该游标是父游标，如果该sql在不同的环境下执行，将会产生不同的version count，这样的话子游标在该处会有多个。
然后进行设置函数dbms_xplan.display_cursor()的第一个和第二个参数查看指定的sql的执行计划。
使用格式化参数BASIC LAST ALLSTATS
SQL> select * from table(dbms_xplan.display_cursor(3485646091,0,'BASIC LAST ALLSTATS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*yallonking*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3
Plan hash value: 653357053
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
|* 1 | COUNT STOPKEY | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | NESTED LOOPS | | 2 |
| 3 | TABLE ACCESS FULL| EMP | 2 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
4 - access("E"."DEPTNO"="DEPTNO")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
28 rows selected.
使用格式化参数advanced
SQL> select * from table(dbms_xplan.display_cursor(3485646091,0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE 3485646091, child number 0
--------------------------------------
select /*yallonking*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3
Plan hash value: 653357053
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 2 | 74 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / E@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 - SEL$5DA710D3 / DEPT@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "E"@"SEL$1")
INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "DEPT"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "DEPT"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<3)
4 - access("E"."DEPTNO"="DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
62 rows selected.
使用hint gather_plan_statistics结合函数dbms_xplan.display_cursor()输出含有统计信息的详细的sql的执行计划
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%scott%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ --------------------------------------------------------------------------------
g071m9pbmk8a0 1463361856 0 select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%
scott%'
ah916jr1wmqbg 3284785519 0 select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3
SQL> select * from table(dbms_xplan.display_cursor(3284785519,0,'BASIC LAST ALLSTATS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+gather_plan_statistics*/ * from scott.emp e where e.deptno in (select
deptno from scott.dept) and rownum<3
Plan hash value: 653357053
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:00.01 | 10 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 10 |
| 3 | TABLE ACCESS FULL| EMP | 1 | 2 | 2 |00:00:00.01 | 8 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 2 | 1 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
4 - access("E"."DEPTNO"="DEPTNO")
22 rows selected.
（三）使用dbms_xplan.display_awr()函数查看存储在awr中的历史执行计划
首先找个历史的sql_id
SQL> set line 300
SQL> select * from dba_hist_sqltext where rownum<3 and COMMAND_TYPE=3;
DBID SQL_ID SQL_TEXT COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
4093928674 byaarkxm6b0r1 SELECT /*+ use_nl(u,o,t) */ NVL(T.DEGREE,1) FROM USER$ U,OBJ$ O,TAB$ T WHERE U.N 3
4093928674 4qfx5ur8a90ym select u.name,o.name from tab$ t,user$ u,obj$ o where t.bobj#=:1 and t.obj#=o.ob 3
然后使用dbms_xplan.display_awr()函数查看历史执行计划
SQL> select * from table(dbms_xplan.display_awr('4qfx5ur8a90ym'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4qfx5ur8a90ym
--------------------
select u.name,o.name from tab$ t,user$ u,obj$ o where t.bobj#=:1 and
t.obj#=o.obj# and o.owner#=u.user#
Plan hash value: 1863015984
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 2 | 96 | 6 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 62 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TAB$ | 2 | 12 | 2 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | I_TAB1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 25 | 1 (0)| 00:00:01 |
| 6 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| |
| 7 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
| 8 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------
21 rows selected.
（四）使用$ORACLE_HOME/rdbms/admin/awrsqrpt.sql 生成指定sql_id的历史执行计划
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
4093928674 ORA10GR2 1 ora10gr2
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 4093928674 1 ORA10GR2 ora10gr2 rhl5.5
* 4093928674 1 ORA10GR2 ora10gr2 ora10gr2
* 4093928674 1 ORA10GR2 ora10gr2 ora11gr2
Using 4093928674 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ora10gr2 ORA10GR2 18 21 Feb 2013 19:42 1
19 21 Feb 2013 21:00 1
20 21 Feb 2013 22:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 18
Begin Snapshot Id specified: 18
Enter value for end_snap: 20
End Snapshot Id specified: 20
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 393c9swv9f3gt
… …
以下省略
注意：该处需要指定问题sql的sql_id，而sql_id可以通过awr报告或者addm等事先获取。
（五）通过客户端sql*plus自带的autotrace工具自动生成sql执行计划
首先，需要创建plustrace角色并给用户赋予相关的plustrace角色
SQL> grant connect,resource,select any table to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> set autotrace traceonly
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> show user
USER is "SYS"
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
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 test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> set autotrace traceonly
SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;
Execution Plan
----------------------------------------------------------
Plan hash value: 653357053
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 2 | 74 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
4 - access("E"."DEPTNO"="DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
898 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
（六）通过用户的sql_trace的dump文件查看用户sql的执行计划
首先，便于查找当前用户的trace文件，先标示该用户的trace文件。
SQL> alter session set tracefile_identifier='yallonking'; Session altered. SQL> set line 200
打开用户的sql_trace并收集sql执行的信息后关闭sql_trace
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL> alter session set sql_trace=false;
Session altered.
查找用户trace文件
SQL> !ls -ltr /u01/app/oracle/admin/ora10gr2/udump/*yallonking* -rw-r----- 1 oracle oinstall 70 Feb 21 23:15 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9540_yallonking.trc -rw-r----- 1 oracle oinstall 2003 Feb 21 23:16 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9647_yallonking.trc -rw-r----- 1 oracle oinstall 2146 Feb 21 23:17 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc
查看用户trace文件中的执行计划
SQL> !vi /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: ora10gr2 Release: 2.6.18-194.el5 Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010 Machine: i686 Instance name: ora10gr2 Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 9655, image: oracle@ora10gr2 (TNS V1-V3) *** 2013-02-21 23:17:11.037 *** SERVICE NAME:(SYS$USERS) 2013-02-21 23:17:11.037 *** SESSION ID:(159.38) 2013-02-21 23:17:11.037 ===================== PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1329606866247139 hv=1569151342 ad='3e3f7fbc' alter session set sql_trace=true END OF STMT EXEC #1:c=1000,e=674,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606866247129 ===================== PARSING IN CURSOR #1 len=88 dep=0 uid=0 oct=3 lid=0 tim=1329606871299685 hv=747907697 ad='43f3aef4' select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3 END OF STMT PARSE #1:c=1000,e=1402,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606871299673 EXEC #1:c=0,e=268,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606871300323 FETCH #1:c=1999,e=1761,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=1,tim=1329606871302309 FETCH #1:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=1329606871304230 STAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=10 pr=0 pw=0 time=1756 us)' STAT #1 id=2 cnt=2 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=10 pr=0 pw=0 time=1711 us)' STAT #1 id=3 cnt=2 pid=2 pos=1 obj=51148 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=1091 us)' STAT #1 id=4 cnt=2 pid=2 pos=2 obj=51147 op='INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=637 us)' *** 2013-02-21 23:17:28.830 ===================== PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1329606883623491 hv=525901419 ad='43c4d108' alter session set sql_trace=false END OF STMT PARSE #1:c=10999,e=10341,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1329606883623481 EXEC #1:c=999,e=588,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606883624361
（七）使用_optimizer_trace或者10053跟踪事件生成sql的执行计划
SQL> set line 200
SQL> alter session set "_optimizer_trace"=all;
Session altered.
SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
SQL> alter session set "_optimizer_trace"=none;
Session altered.
查看当前会话的trace文件位置
SQL> SELECT a.VALUE 2 || b.symbol 3 || c.instance_name 4 || '_ora_' 5 || d.spid 6 || '.trc' trace_file 7 FROM (SELECT VALUE 8 FROM v$parameter 9 WHERE NAME = 'user_dump_dest') a, 10 (SELECT SUBSTR (VALUE, -6, 1) symbol 11 FROM v$parameter 12 WHERE NAME = 'user_dump_dest') b, 13 (SELECT instance_name 14 FROM v$instance) c, 15 (SELECT spid 16 FROM v$session s, v$process p, v$mystat m 17 WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d 18 / TRACE_FILE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9759.trc
查看该trace文件中的部分
… …
sql_id=16v398fw5h696.
Current SQL statement for this session:
select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4
============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 3 | 120 | 2 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 3 | 111 | 2 | 00:00:01 |
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 | |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<4)
4 - access("E"."DEPTNO"="DEPTNO")
… …
以上便是常用的查看一个sql的执行计划的方法，且均在oracle10g版本实验通过。
写的很详尽，也很实用