oracle 查看SQL执行计划

看一段sql的执行计划

explain plan for ...<sql>... 

select * from table(dbms_xplan.display);
 

例子：

--执行需要查看执行计划的sql（提示plan FOR 成功。）
explain plan for
select A.contract_id from test_table1 a
inner join 
(select b.contract_line_id,sum(b.stock_num) as stock_num,sum(b.in_qty) as in_qty,sum(c.in_num) as in_num,sum(nvl(c.has_num,0)) as has_num from test_table2 b 
inner join test_table3 c on b.id = c.order_detail_id
group by b.contract_line_id having sum(c.in_num) >= sum(b.stock_num) and sum(nvl(c.has_num,0)) < sum(b.stock_num)
) os on os.contract_line_id = A.contract_line_id
left join (
select ur.account_id from test_table4 ur inner join test_table5 ar on ur.account_id = ar.account_id and ur.r_id = ar.id where ar.name like '%采购员%' group by ur.account_id
) aur on a.account_id = aur.account_id
where a.contract_id in (select m.contract_code from test_table6 m where m.contract_type not in ('03','04','05','09'))
group by A.contract_id
order by A.contract_id;

--上面sql执行后 再执行此语句即可查看执行计划
select * from table(dbms_xplan.display);