如何利用SQLPLUS取得SQL執行計劃 Oracle How to get query execution plan using sqlplus

Query Executing SQL

-- Standalone
col username format a13
col osuser format a13
set line 200
select s.username, s.sid, s.osuser, t.sql_id, t.sql_text, s.sql_child_number
from v$sqltext_with_new_line t, v$session s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.username = :username
order by s.sid, t.piece;

-- RAC
col username format a13
col osuser format a13
set line 200
select s.username, s.sid, s.osuser, t.sql_id, t.sql_text, s.sql_child_number, s.inst_id
from gv$sqltext_with_new_line t, gv$session s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.username = :username
order by s.sid, t.piece;

 

Query Plan By SQL_ID

--Basic
select * from table(dbms_xplan.display_cursor('sql_id', child_number));

--More Info
select * from table(dbms_xplan.display_cursor('sql_id', child_number, FORMAT=>'ALL +OUTLINE'));

Query Plan By SQL Statement

explain plan for
select * from table_name;

set line 200
set pagesize 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL +OUTLINE'));