怎樣得到準確的執行計劃

我們知道autotrace和explain plan以及dbms_xplan.display包都是透過對plan_table的查詢和格式化後輸出執行計劃的結果，有時會不準確。
準確的執行計劃需要從V$sql_plan檢視中抓取，下面透過幾種方法從V$SQL_PLAN中得到執行計劃。[@more@]最好的方法是透過dbms_xplan.display_cursor包，執行這個包需要知道sql的sql_id以及child_number

SQL> select t.*
from v$sql s,
table(dbms_xplan.display_cursor(s.sql_id,s.child_number)) t
where s.sql_text like '%where b=5%' and s.parsing_user_id <> 0; 2 3 4

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------
SQL_ID 7zkqh4p1ud7nu, child number 0
-------------------------------------
select * from tab where b=5

Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 253K| 6 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"=5)

Note
-----
- dynamic sampling used for this statement


22 rows selected.



第二種方法是網上Toms_zhang提供的一個過程，在10g 版本中也需要先知道sql_id，不過這個格式好像不太好看

SQL> select s.sql_text, s.sql_id,s.child_number,parsing_user_id
from v$sql s where s.sql_text like '%where b=5%' and s.parsing_user_id <> 0; 2

SQL_TEXT SQL_ID CHILD_NUMBER PARSING_USER_ID
-------------------- ------------- ------------ ---------------
select * from tab wh 7zkqh4p1ud7nu 0 64
ere b=5

SQL> set serveroutput on;

SQL> exec Get_Sql_Plan(p_sql_id=>'7zkqh4p1ud7nu');
ID Parent_ID OPERATION OPTIONS OBJECT_NAME

OPTIMIZER COST CARDINALITY BYTES
------ --------- --------------------------------------------------- ------------------------------ --------------

----- ----------- ---------------- ---------- ------------ ---------
0 # SELECT STATEMENT

ALL_ROWS 6 0 0
1 0 TABLE ACCESS FULL TAB

6 9991 259766

PL/SQL procedure successfully completed.


Get_Sql_Plan過程程式碼如下：

create or replace procedure Get_Sql_Plan(p_hash_value in number default 0,
p_sid in number default 999999,
p_sql_id in varchar2 default null) is
/*
10g以前版本使用p_hash_value或p_sid引數
10g以後版本推薦使用p_sql_id
3個引數的優先順序p_sql_id > p_hash_value > p_sid
*/
v_hash_value number;
v_version number;
begin
dbms_output.enable(100000);
if p_hash_value=0 and p_sid =999999 and p_sql_id is null then
dbms_output.put_line('All input argument is default');
return;
end if;
select to_number(substr(version,1,instr(version,'.')-1)) into v_version from v$instance;
begin
if p_sql_id is not null then
if v_version > 9 then
select distinct hash_value into v_hash_value from v$sql_plan where sql_id=p_sql_id;
else
dbms_output.put_line('The current Oracle version does''t support SQL_ID column');
return;
end if;
else
if p_sid<>999999 then
select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) into v_hash_value
from v$session
where sid=p_sid;
else
v_hash_value:=p_hash_value;
end if;
end if;
exception
when OTHERS then
raise_application_error(-20001,'Invalid or Null Hash_Value');
end;

dbms_output.put_line('ID Parent_ID OPERATION OPTIONS

OBJECT_NAME OPTIMIZER COST CARDINALITY BYTES');
dbms_output.put_line('------ --------- --------------------------------------------------- -------------------

----------- ------------------------------ ---------------- ---------- ------------ ---------');

for c in (select id,
decode(parent_id,NULL,'#',parent_id) parent_id,
rpad(lpad(' ',depth)||operation,50) operation,
nvl(options,' ') options,
nvl(object_name,' ') object_name,
nvl(optimizer,' ') optimizer,
nvl(cost,'0') cost,
nvl(cardinality,'0') cardinality,
nvl(bytes,'0') bytes
from v$sql_plan
where (hash_value,address) in (select hash_value,address
from v$sqlarea
where hash_value=v_hash_value)
) loop
dbms_output.put_line(rpad(c.id,7)||rpad(c.parent_id,10)||rpad(c.operation,51)||' '||rpad(c.options,30)||'

'||rpad(c.object_name,30)||' '||rpad(c.optimizer,17)||rpad(c.cost,11)||rpad(c.cardinality,13)||rpad(c.bytes,10));
end loop;
end;
/