很多时候我们总是喜欢使用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会被实际执行。