导航：首页 > 服务器 >

EXPLAIN PLAN FOR 和 SET AUTOTRACE之间的差别是什么

发表于：2024-10-26 作者：千家信息网编辑

千家信息网最后更新 2024年10月26日，本篇文章为大家展示了EXPLAIN PLAN FOR 和 SET AUTOTRACE之间的差别是什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。通常我们使用

千家信息网最后更新 2024年10月26日EXPLAIN PLAN FOR 和 SET AUTOTRACE之间的差别是什么

本篇文章为大家展示了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 autot on exp
yang@rac1>set autotrace traceonly
yang@rac1>select * from yang_t;
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)
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
yang@rac1>set autot off
再次查询是否执行。可以看出使用set autotrace 查看执行计划时，oracle会执行一下sql语句的。
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t';
EXECUTIONS PARSE_CALLS
---------- -----------
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%';
EXECUTIONS PARSE_CALLS
---------- -----------
1 1
=======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
----------------------------------------------------------------------------------------
Plan hash value: 2508602004
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1817 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| YANG_T | 23 | 1817 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
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%';
no rows selected
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG%';

no rows selected

yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t';
EXECUTIONS PARSE_CALLS
---------- -----------
2 2
yang@rac1>
小结：
EXPLAIN PLAN FOR 方式查看执行计划时oracle本身并不真正的执行该sql 语句，只是对sql进行解析获取执行计划。
SET AUTOTRACE 方式查看sql语句的执行计划则是oracle 则对sql进行解析并执行的。