验证 set autotrace查看DML语句的执行计划会被实际执行

很多时候我们总是喜欢使用set autotrace on、set autotrace traceonly和set autotrace traceonly explain这种方式来查看目标SQL的执行计划,但是你知道吗?autotrace方式查看执行计划,如果目标SQL是DML语句会被实际执行。
先测试select 

SQL> select SQL_TEXT,EXECUTIONS from v$sqlarea where sql_text like 'select * from test where id=1';
SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from test where id=1                                         1

SQL> set autotrace traceonly explain
SQL> select * from test where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   142 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |   142 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)

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

SQL> set autotrace off
SQL> select SQL_TEXT,EXECUTIONS from v$sqlarea where sql_text like 'select * from test where id=1';

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from test where id=1                                         1

通过上述,可以证明在 set autotrace 语句执行select不会实际执行。

我们在来看下当使用set autorace on时,执行DML语句会怎么样?

SQL> select * from test;

        ID NAME
---------- ------------------------------
         1 zhang
         2 wang

SQL> set autotrace on
SQL> delete from test where id=1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 180605370

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  DELETE            | TEST |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=1)

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

Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        682  bytes sent via SQL*Net to client
        599  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL> select * from test;

        ID NAME
---------- ------------------------------
         2 wang

可以看出,DML语句已经被实际执行。

继续接着上述实验,我们看下set autotrace traceonly时,执行insert会怎么样?

SQL> set autotrace traceonly
SQL> insert into test values('1','zhang');

1 row created.

Execution Plan
----------------------------------------------------------

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

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

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

|   0 | INSERT STATEMENT         |      |     1 |   100 |     1   (0)| 00:00:01
|

|   1 |  LOAD TABLE CONVENTIONAL | TEST |       |       |            |
|
--------------------------------------------------------------------------------
-

Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
        300  redo size
        682  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL> select * from test;

        ID NAME
---------- ------------------------------
         1 zhang
         2 wang

同样也可以看到insert语句也被实际执行了。

我们再来验证下set autotrace traconly explain查看DML语句的执行计划是否会被实际执行:
SQL> select SQL_TEXT,EXECUTIONS from v$sqlarea where sql_text like 'delete from test where id=1';

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
delete from test where id=1                                           0

SQL> set autotrace traceonly explain
SQL> delete from test where id=1;

1 row deleted.  --注意此处提示

Execution Plan
----------------------------------------------------------
Plan hash value: 180605370

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  DELETE            | TEST |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"=1)

Note
-----
- dynamic sampling used for this statement (level=2)
--可以看出数据已经被删除
SQL> select * from test where id=1;

no rows selected

SQL> select SQL_TEXT,EXECUTIONS from v$sqlarea where sql_text like 'delete from test where id=1';

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
delete from test where id=1                                           1

同样我们可以看到DML语句已被实际执行。

从上述的例子我们可以看出使用set autotrace后执行DML语句,该DML会被实际执行。所以在使用set autotrace来获取DML语句的执行计划时要特别小心,因为这些DML会被实际执行。

留下评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据