EXPLAIN PLAN FOR 和 SET AUTOTRACE之間的差别

通常我們使用 EXPLAIN PLAN FOR 和 SET AUTOTRACE 來檢視sql語句的執行計劃，這裡做一個小實驗來看看兩種方法對sql執行情況的差别。

yang@rac1>create table yang_t as select * from t;

Table created.

yang@rac1>set autot on exp

yang@rac1>select * from yang_t;

        ID  NAME

---------- ------------------

    130864 YANG_SEQ

    132031 YANG_A

    132032 SYS_C0066382

    132033 YANG_B

    132034 SYS_C0066383

    132035 FACT

    132036 MLOG$_YANG_A

    132037 MLOG$_YANG_B

    132038 MLOG$_FACT

    132039 T

    131949 YANG_ROWID

    131951 YANG_PK

    131952 SYS_C0066303

    131955 YANG_OBJECT

    131956 YANG_OID

    131957 SYS_C0066304

    132018 YANG_C

    132017 MV_CAPABILITIES_TABLE

    132030 MLOG$_YANG_PK

    132027 MLOG$_YANG_ROWID

           LINKORACL

           LINKYANG

22 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2508602004

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT  |        |    22 |  1738 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| YANG_T |    22 |  1738 |     3   (0)| 00:00:01 |

Note

-----

   - dynamic sampling used for this statement (level=2)

yang@rac1>set autot off

yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t';

EXECUTIONS PARSE_CALLS

---------- -----------

         1           1

yang@rac1>set autotrace  traceonly

Statistics

         21  recursive calls

         61  db block gets

         33  consistent gets

          6  physical reads

      14040  redo size

       1082  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         22  rows processed

再次查詢是否執行。可以看出使用set autotrace 檢視執行計劃時，oracle會執行一下sql語句的。

         2           2

對測試表進行dml操作。并檢視dml 的執行情況。

yang@rac1>insert into yang_t values (1,2);

1 row created.

yang@rac1>commit;

Commit complete.

yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'insert into yang_t values (1,2);';

no rows selected

yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'insert into yang_t values%';

=======EXPLAIN PLAN FOR ========

實驗一下EXPLAIN PLAN 檢視sql語句執行計劃的情況。

yang@rac1>EXPLAIN PLAN FOR SELECT * FROM YANG_T;

Explained.

yang@rac1>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |    23 |  1817 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| YANG_T |    23 |  1817 |     4   (0)| 00:00:01 |

12 rows selected.

從下面的查詢結果中可以看出使用EXPLAIN PLAN FOR 檢視執行計劃時oracle是沒有執行要檢視執行計劃的sql 語句的。

yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG_T%';

yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG%';

yang@rac1>

小結：

     EXPLAIN PLAN FOR 方式檢視執行計劃時oracle本身并不真正的執行該sql 語句，隻是對sql進行解析擷取執行計劃。

    SET AUTOTRACE   方式檢視sql語句的執行計劃則是oracle 則對sql進行 解析并執行的。