如何查詢sql 語法的Execution Plan

要能顯示Execution Plan 首先一定要先執行utlxplan.sql，一般而言這個檔案會被放在
$ORACLE_HOME/rdbms/admin中，因此可透過sqlplus來執行
SQL>@?/rdbms/admin/utlxplan.sql

Table created.

建立好後，顯示execution plan的方法有：
1、利用autotrace
set autotrace on (explain) (statistics)
on 查詢執行路徑和效能統計
on explain 只產生查詢執行路徑
on statistics 只產生效能統計
off 預設值 不產生報表

ex. select count(*) from dba_users;
SQL> /

COUNT(*)
----------
26


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=23 Card=1 Bytes=
195)

1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=23 Card=6698 Bytes=1306110)
3 2 NESTED LOOPS (Cost=23 Card=82 Bytes=14924)
4 3 HASH JOIN (Cost=23 Card=1 Bytes=169)
5 4 HASH JOIN (Cost=21 Card=1 Bytes=156)
6 5 HASH JOIN (Cost=19 Card=1 Bytes=143)
7 6 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=78
)

8 7 TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE$'
(Cost=1 Card=1 Bytes=39)

9 8 INDEX (RANGE SCAN) OF 'I_PROFILE' (NON-UNI
QUE) (Cost=1 Card=1)

10 7 SORT (JOIN) (Cost=1 Card=1 Bytes=39)
11 10 TABLE ACCESS (FULL) OF 'PROFILE$' (Cost=1
Card=1 Bytes=39)

12 6 TABLE ACCESS (FULL) OF 'USER$' (Cost=16 Card=8
2 Bytes=5330)

13 5 TABLE ACCESS (FULL) OF 'PROFNAME$' (Cost=1 Card=
82 Bytes=1066)

14 4 TABLE ACCESS (FULL) OF 'USER_ASTATUS_MAP' (Cost=1
Card=82 Bytes=1066)

15 3 TABLE ACCESS (CLUSTER) OF 'TS$'
16 15 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
17 2 TABLE ACCESS (CLUSTER) OF 'TS$'
18 17 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

2、利用tkprof 來進行sql trace
決定trace的level
a、db level
修改init中，timed_statistics=true
b、session level
alter session set sql_trace=true;
c、對某個session進行trace ，可用dbms_session
exec dbms_session.set_sql_trace_in_session(sid,serial#,true);
產生的trace會在user_dump_dest所指定的目錄中。

利用tkprof程式來分析trace file
tkprof tracefile outfile explain=user/passwd #記得user要和執行trace 的session相同

tkprof ora_21968_t.trc outfile.txt explain=xxx/ooo

TKPROF: Release 8.1.7.4.0 - Production on Mon Feb 12 14:08:02 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: ora_21968_t.trc
Sort options: default

******************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
******************************************************************************

select KCDE_1,KRMK,KCDE_2,KCNT
from
SRKEYN where 1=1 and KCDE_1='06' and substr(KCDE_2,1,2) <> '/*' order by
KCDE_1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 11 0 177
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 11 0 177

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 24 (MOICAD)

Rows Row Source Operation
------- ---------------------------------------------------
177 INDEX RANGE SCAN (object id 3291)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
177 INDEX (RANGE SCAN) OF 'RKEYN_IDX1' (UNIQUE)

********************************************************************************

SELECT LPAD(TO_CHAR(SYSDATE,'YYYY')-1911,3,'0')
||TO_CHAR(SYSDATE,'MMDDHH24MISS') from DUAL

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 8 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 2 8 2

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 24 (MOICAD)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS

SELECT LPAD(TO_CHAR(SYSDATE,'YYYY')-1911,3,'0')
||TO_CHAR(SYSDATE,'MMDDHH24MISS') from DUAL

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 8 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 2 8 2

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 24 (MOICAD)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
...

中間就省略不顯示。

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
0 SORT (UNIQUE)
0 UNION-ALL
0 CONCATENATION
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CRSMS'
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CRSMS'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CRSMS_IDX1'
(UNIQUE)
0 CONCATENATION
0 FILTER
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CRSMS'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CRSMS_IDX3'

只列部份內容參考…

。