【Oracle】25.谨慎使用set autotrace traceonly explain

1.SELECT 语句执行计划查询

SQL>  create table test03 (id int,name varchar2(20));

Table created.

SQL> set autotrace traceonly explain ; 
SQL> select * from test03;

Execution Plan
----------------------------------------------------------
Plan hash value: 290625895

----------------------------------------------------------------------------
| Id  | Operation	  | Name   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	   |	 1 |	25 |	 2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST03 |	 1 |	25 |	 2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2.INSERT 语句执行计划查询

--该设置会真实执行DML语句,所以针对DML语句
--谨慎使用:set autotrace traceonly explain;
SQL> insert into test03 values(1,'xsq1');

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 | TEST03 |	  |	  |	       |  |
--------------------------------------------------------------------------------

--3.检查,果然执行。

SQL> set autot off
SQL> select * from test03;

	ID NAME
---------- --------------------
	 1 xsq1

4.针对DML语句的执行计划如何查看.

而又不真实执行DML语句呢。

SQL> select * from test03;
	ID NAME
---------- --------------------
	 1 xsq1

SQL> explain plan for insert into test03 values(2,'xsq2');
Explained.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
| Id  | Operation		 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT	 |	  |	1 |   100 |	1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST03 |	  |	  |	       |	  |
-----------------------------------------------------------------------------------

SQL> select * from test03;

	ID NAME
---------- --------------------
	 1 xsq1

5.总结 

(1) 会执行DML语句。
set autotrace traceonly explain;
(2)如下可以查看DML执行计划但不执行。
explain plan for DMLSQL;
select * from table(dbms_xplan.display());