简单对比查看执行计划的两种方法EXPLAINPLAN和AUTOTRACE - 数据库编程

EXPLAIN PLAN 和 AUTOTRACE 都可以查看执行计划。值得一提的是：前者只是优化器通过读取数据字典的统计信息做出'最佳'访问路径判断，并没有真正去执行语句；后者是实际去执行了SQL语句，同时把访问记录数、执行计划、统计信息等打印出来。

下面粘出实验结果加以说明，注意对比两者的耗时：

SQL> CONNECT /AS SYSDBA
Connected.
SQL> SET LINESIZE 300;
SQL> SET TIMING ON;
SQL> SET PAGESIZE;
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM DBA_OBJECTS , DBA_OBJECTS;





Explained.
Elapsed: 00:00:00.03                 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<仅消耗0.03秒
SQL> 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2343274122
-------------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 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"."TYP

简单对比查看执行计划的两种方法EXPLAINPLAN和AUTOTRACE(一)