简单对比查看执行计划的两种方法EXPLAIN PLAN 和 AUTOTRACE

时间:2022-06-09 03:55:28

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

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

 

<p>SQL> CONNECT /AS SYSDBA
Connected.</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