Set autorace traceonly 的假象

最新推荐文章于 2024-02-01 16:40:25 发布

cpofk44286

最新推荐文章于 2024-02-01 16:40:25 发布

阅读量162

点赞数

文章标签：数据库

本文通过实例演示了在Oracle数据库中使用setautotracetraceonlyexplain时的常见误区，揭示了其不仅显示执行计划还会实际执行SQL语句的问题，并提供了正确的执行计划查看方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

LL@dbfs1>set autotrace

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

有些朋友喜欢用set autotrace 来查看执行计划
可是却不知道它的假象，有些朋友认为，set autotrace traceonly explain只是出现执行计划，却不知道其实它已经在后台默默的执行了。

例：
1.创建表

create table t as select object_name,owner,object_type,object_id from dba_objects;

2.查看表中数据
set line 160 pagesize 500

col object_name for a50

col OWNER for a10

select * from t where rownum<10;

OBJECT_NAME OWNER OBJECT_TYPE OBJECT_ID

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

ICOL$ SYS TABLE 20

I_USER1 SYS INDEX 46

CON$ SYS TABLE 28

UNDO$ SYS TABLE 15

C_COBJ# SYS CLUSTER 29

I_OBJ# SYS INDEX 3

PROXY_ROLE_DATA$ SYS TABLE 25

I_IND1 SYS INDEX 41

I_CDEF2 SYS INDEX 54

LL@dbfs1>select count(*) from t;

COUNT(*)

----------

15774

新建的表有15774行数据。

3.执行set autotrace traceonly explain

LL@dbfs1>set autotrace traceonly explain;

LL@dbfs1>delete from t where object_id<10000;

9806 rows deleted. --已经给出提示删除了9806行

Execution Plan --同时也给出了执行计划

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

Plan hash value: 3335594643

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

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

| 0 | DELETE STATEMENT | | 1 | 13 | 28 (0)| 00:00:01 |

| 1 | DELETE | T | | | | |

|* 2 | TABLE ACCESS STORAGE FULL| T | 1 | 13 | 28 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - storage("OBJECT_ID"<10000)

filter("OBJECT_ID"<10000)

Note

-----

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

4.确认此事表中数据

LL@dbfs1>set autotrace off;

LL@dbfs1>select count(*) from t;

COUNT(*)

----------

5968 --已经被删除了，如果此时你还没有推出事务，rollback 还是可以找回数据的。

LL@dbfs1>exit --不幸的是你已经退出事务了，这时会触发数据库隐式提交，数据彻底丢失，如果你有flashback或者备份的话，你还是幸运的，否则你可能要卷铺盖走人了，如果不知道什么是隐式提交，就需要去百度一下了。

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[oracle@dm01db01 ~]$ sqlplus ll/ll

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 16 10:00:50 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

LL@dbfs1>select count(*) from t;

COUNT(*)

----------

5968 --真正丢失了。呵呵

我们在需要查看执行计划的时候，要使用
explain plan for select * from t;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5303 | 554K| 28 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| T | 5303 | 554K| 28 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

12 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26474945/viewspace-1369159/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26474945/viewspace-1369159/