首页 > 代码库 > 简单对比查看执行计划的两种方法EXPLAIN PLAN 和 AUTOTRACE
简单对比查看执行计划的两种方法EXPLAIN PLAN 和 AUTOTRACE
EXPLAIN PLAN 和 AUTOTRACE 都可以查看执行计划。 值得一提的是：前者只是优化器通过读取数据字典的统计信息做出‘最佳‘访问路径判断，并没有真正去执行语句；后者是实际去执行了SQL语句，同时把访问记录数、执行计划、统计信息等打印出来。
下面粘出实验结果加以说明，注意对比两者的耗时：
<p>SQL> CONNECT /AS SYSDBAConnected.</p><p>SQL> SET LINESIZE 300;SQL> SET TIMING ON;SQL> SET PAGESIZE;SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM DBA_OBJECTS , DBA_OBJECTS;</p><p>Explained.</p><p>Elapsed: 00:00:00.03 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<仅消耗0.03秒SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);Plan hash value: 2343274122</p><p>-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 7187K (5)| 23:57:25 || 1 | SORT AGGREGATE | | 1 | | | || 2 | MERGE JOIN CARTESIAN | | 2325M| | 7187K (5)| 23:57:25 || 3 | VIEW | DBA_OBJECTS | 48221 | | 149 (5)| 00:00:02 || 4 | UNION-ALL | | | | | ||* 5 | FILTER | | | | | ||* 6 | HASH JOIN | | 51192 | 4099K| 148 (5)| 00:00:02 || 7 | TABLE ACCESS FULL | USER$ | 59 | 177 | 2 (0)| 00:00:01 ||* 8 | TABLE ACCESS FULL | OBJ$ | 51192 | 3949K| 145 (5)| 00:00:02 ||* 9 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 ||* 10 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 || 11 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 || 12 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 || 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 ||* 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 || 15 | BUFFER SORT | | 48221 | | 7187K (5)| 23:57:25 || 16 | VIEW | DBA_OBJECTS | 48221 | | 149 (5)| 00:00:02 || 17 | UNION-ALL | | | | | ||* 18 | FILTER | | | | | ||* 19 | HASH JOIN | | 51192 | 4099K| 148 (5)| 00:00:02 || 20 | TABLE ACCESS FULL | USER$ | 59 | 177 | 2 (0)| 00:00:01 ||* 21 | TABLE ACCESS FULL | OBJ$ | 51192 | 3949K| 145 (5)| 00:00:02 ||* 22 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 ||* 23 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 || 24 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 || 25 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 || 26 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 ||* 27 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------</p><p>Predicate Information (identified by operation id):---------------------------------------------------</p><p> 5 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) 6 - access("O"."OWNER#"="U"."USER#") 8 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL) 9 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) 10 - access("I"."OBJ#"=:B1) 14 - access("L"."OWNER#"="U"."USER#") 18 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) 19 - access("O"."OWNER#"="U"."USER#") 21 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL) 22 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) 23 - access("I"."OBJ#"=:B1) 27 - access("L"."OWNER#"="U"."USER#")</p><p>58 rows selected.</p><p>Elapsed: 00:00:00.04</p>
<p>SQL> SET AUTOTRACE; Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]SQL> SET AUTOTRACE ON;SQL> SELECT COUNT(*) FROM DBA_OBJECTS , DBA_OBJECTS;2508707569 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<count()</p><p>Elapsed: 00:01:41.97 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<消耗1分41.97秒</p><p>Execution Plan----------------------------------------------------------Plan hash value: 2343274122</p><p>-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 7187K (5)| 23:57:25 || 1 | SORT AGGREGATE | | 1 | | | || 2 | MERGE JOIN CARTESIAN | | 2325M| | 7187K (5)| 23:57:25 || 3 | VIEW | DBA_OBJECTS | 48221 | | 149 (5)| 00:00:02 || 4 | UNION-ALL | | | | | ||* 5 | FILTER | | | | | ||* 6 | HASH JOIN | | 51192 | 4099K| 148 (5)| 00:00:02 || 7 | TABLE ACCESS FULL | USER$ | 59 | 177 | 2 (0)| 00:00:01 ||* 8 | TABLE ACCESS FULL | OBJ$ | 51192 | 3949K| 145 (5)| 00:00:02 ||* 9 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 ||* 10 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 || 11 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 || 12 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 || 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 ||* 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 || 15 | BUFFER SORT | | 48221 | | 7187K (5)| 23:57:25 || 16 | VIEW | DBA_OBJECTS | 48221 | | 149 (5)| 00:00:02 || 17 | UNION-ALL | | | | | ||* 18 | FILTER | | | | | ||* 19 | HASH JOIN | | 51192 | 4099K| 148 (5)| 00:00:02 || 20 | TABLE ACCESS FULL | USER$ | 59 | 177 | 2 (0)| 00:00:01 ||* 21 | TABLE ACCESS FULL | OBJ$ | 51192 | 3949K| 145 (5)| 00:00:02 ||* 22 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 ||* 23 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 || 24 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 || 25 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 || 26 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 ||* 27 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------</p><p>Predicate Information (identified by operation id):---------------------------------------------------</p><p> 5 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) 6 - access("O"."OWNER#"="U"."USER#") 8 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL) 9 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) 10 - access("I"."OBJ#"=:B1) 14 - access("L"."OWNER#"="U"."USER#") 18 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) 19 - access("O"."OWNER#"="U"."USER#") 21 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL) 22 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) 23 - access("I"."OBJ#"=:B1) 27 - access("L"."OWNER#"="U"."USER#")</p><p>Statistics---------------------------------------------------------- 15 recursive calls 0 db block gets 10208 consistent gets 0 physical reads 0 redo size 519 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<返回1条结果集</p>
因此，EXPLAIN PLAN时常不能反应真实的执行计划，尤其是在绑定变量的应用中。PLSQL/DEVELOPER的F5能快速查看执行计划，其实就是EXPLAIN PLAN的方法。
如果想知道SQL语句的真实执行计划，可能通过以下几种方法：
o SET AUOTRACE
o 10046
o DBMS_XPLAN.DISPLAY_COURSOR 或 DBMS_XPLAN.DISPLAY_AWR
o 查询V$SQL_PLAN
简单对比查看执行计划的两种方法EXPLAIN PLAN 和 AUTOTRACE
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。