1.Enterprise Manager，Grid Control，Cloud Control
2.SQLT工具（需要安装，这个工具非常不错）
3.其他一些第三方脚本(个人觉得比较好用的是Adrian Billington写的，无需创建对象,可从https://github.com/oracle-developer/xplan 获取)
Oracle SQL的执行计划其实也是二叉树，下面将通过创建一个临时表，来获取SQL的执行顺序。
[oracle@ohs1 admin]$ sqlplus scott/oracle
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 10 23:16:13 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jan 10 2017 23:15:57 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> create global temporary table ohs_plan_table
(
operation varchar2(30),
options varchar2(255),
object_owner varchar2(128),
object_name varchar2(128),
object_type varchar2(30),
id numeric,
parent_id numeric,
depth numeric,
position numeric,
exec_order numeric
) on commit preserve rows
/ 3 4 5 6 7 8 9 10 11 12 13 14
Table created.
SQL> desc ohs_plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(30)
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
EXEC_ORDER NUMBER(38)
SQL>
explain一个语句explain plan for select ename,sal,dname,loc from emp a,dept b where a.deptno=b.deptno; SQL> explain plan for select ename,sal,dname,loc from emp a,dept b where a.deptno=b.deptno; Explained. SQL>将执行计划信息从plan_table抽取到建的测试临时表ohs_plan_table
insert into ohs_plan_table (operation, options, object_owner, object_name, object_type, id, parent_id, depth, position) select operation, options, object_owner, object_name, object_type, id, parent_id, depth, position from plan_table; SQL> insert into ohs_plan_table (operation, options, object_owner, object_name, object_type, id, parent_id, depth, position) select operation, options, object_owner, object_name, object_type, id, parent_id, depth, position from plan_table; 6 rows created. SQL>通过ohs_plan_table临时表生成执行顺序
set serveroutput on
declare
procedure execution_order is
l_exec_order number;
procedure assign_execution_order(p_id in number) is
begin
for j in (select id, position
from ohs_plan_table
where parent_id = p_id
order by position) loop
--dbms_output.put_line(rpad('A.j Id:'||j.id,10,'*')||lpad('Pid:'||p_id,10,'*')||lpad('position '||j.position,15,'*')||lpad('order '||l_exec_order,15,'*'));
assign_execution_order(p_id => j.id);
dbms_output.put_line(rpad('B.j Id:' || j.id, 10, '*') ||
lpad('Pid:' || p_id, 10, '*') ||
lpad('position ' || j.position, 15, '*') ||
lpad('order ' || l_exec_order, 15, '*'));
end loop;
l_exec_order := l_exec_order + 1;
--dbms_output.put_line(rpad('Update Id:'||p_id,20,'*')||lpad(l_exec_order,15,'*'));
update ohs_plan_table set exec_order = l_exec_order where id = p_id;
end assign_execution_order;
begin
for i in (select id, parent_id, position
from ohs_plan_table
where parent_id is null) loop
l_exec_order := 0;
--dbms_output.put_line(rpad('C.i Id:'||i.id,10,'*')||lpad('Pid:'||nvl(i.parent_id,''),10,'*')||lpad('position '||i.position,15,'*')||lpad('order '||l_exec_order,15,'*'));
assign_execution_order(p_id => i.id);
dbms_output.put_line(rpad('D.i Id:' || i.id, 10, '*') ||
lpad('Pid:' || nvl(i.parent_id, ''), 10, '*') ||
lpad('position ' || i.position, 15, '*') ||
lpad('order ' || l_exec_order, 15, '*'));
end loop;
commit;
exception
when others then
dbms_output.put_line('execution_order: ' || sqlerrm);
end;
begin
execution_order;
end;
/
B.j Id:3*******Pid:2*****position 1********order 1
B.j Id:2*******Pid:1*****position 1********order 2
B.j Id:5*******Pid:4*****position 1********order 3
B.j Id:4*******Pid:1*****position 2********order 4
B.j Id:1*******Pid:0*****position 1********order 5
D.i Id:0********Pid:*****position 6********order 6
PL/SQL procedure successfully completed.
SQL>
注意：通过声明一个存储过程，存储过程中用了递归，有兴趣调试的，可以将上面代码中的--去掉，看看具体的执行情况。查询ohs_plan_table临时表获取sql的执行顺序
col operation format a20
col options format a20
col object_name format a30
col id format 999
col plan for a60
set lines 156 pages 1000
select id,parent_id,exec_order,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' ' ||decode(id,0,'Cost = '||position) plan from ohs_plan_table
start with id = 0 connect by prior id = parent_id;
ID PARENT_ID EXEC_ORDER PLAN
---- ---------- ---------- ------------------------------------------------------------
0 6 SELECT STATEMENT Cost = 6
1 0 5 MERGE JOIN
2 1 2 TABLE ACCESS BY INDEX ROWID DEPT
3 2 1 INDEX FULL SCAN PK_DEPT
4 1 4 SORT JOIN
5 4 3 TABLE ACCESS FULL EMP
6 rows selected.
SQL>
直接查询plan_table获取执行顺序
SQL> @/home/oracle/xplan.sql
old 3: from &v_xp_plan_table
new 3: from PLAN_TABLE
old 4: where plan_id = &v_xp_plan_id
new 4: where plan_id = 18
old 30: from table(dbms_xplan.display('&v_xp_plan_table','&v_xp_stmt_id','&v_xp_format')) x
new 30: from table(dbms_xplan.display('PLAN_TABLE','','typical')) x
old 73: ' - XPlan v&v_xp_version by Adrian Billington (http://www.oracle-developer.net)'
new 73: ' - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)'
通过比对，这两种方式生成的SQL执行顺序是一致的
用二叉树表示为
从图中可以看到，这颗树遍历后，我们得到执行顺序为3,2,5,4,1,0（从左到右，从下到上的顺序）。
SQL：select ename,sal,dname,loc from emp a,dept b where a.deptno=b.deptno;
10g之后如何重建plan_table
- 'PLAN_TABLE' is old version，如果plan_table是老版本，10g之后可以通过catplan.sql脚本重建plan_table。 sqlplus / as sysdba SQL> drop table plan_table$; SQL> @?/rdbms/admin/catplan.sql
http://baike.baidu.com/item/%E4%BA%8C%E5%8F%89%E6%A0%91
http://www.oracle-developer.net/utilities.php
https://github.com/oracle-developer/xplan
