truncate表和update 效率_TRUNCATE语句时间过长的诊断

在ITPUB上看到有人提问，为什么TRUNCATE语句要比DELETE慢，由于默认TRUNCATE要回收空间，且会将所有的脏块写回到数据文件，因此其实对于小数据量而言，TRUNCATE比DELETE慢也是正常的。不过这个例子中，TRUNCATE慢的有点离谱了。

这是根据问题模拟的例子，表中只有5条记录，可以看到，TRUNCATE操作要比DELETE慢得多：

SQL> SET TIMING ON

SQL> DELETE T_BIG;

4 rows deleted.

Elapsed: 00:00:00.01

SQL> TRUNCATE TABLE T_BIG;

Table truncated.

Elapsed: 00:00:03.25

对于表中数据量不大的情况，TRUNCATE比DELETE慢一点是正常的，但是二者一般是同一个数量级的：

SQL> CREATE TABLE T_TRUNCATE (ID NUMBER);

Table created.

Elapsed: 00:00:00.06

SQL> INSERT INTO T_TRUNCATE VALUES (1);

1 row created.

Elapsed: 00:00:00.00

SQL> DELETE T_TRUNCATE;

1 row deleted.

Elapsed: 00:00:00.03

SQL> TRUNCATE TABLE T_TRUNCATE;

Table truncated.

Elapsed: 00:00:00.10

现在这个差别有点过大了，肯定不是简单的数据量较小就可以解释得通了。

这个问题的描述者除了给出TRUNCATE语句和DELETE语句的时间对比外，还对TRUNCATE语句设置了10046事件进行TRACE：

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Session altered.

Elapsed: 00:00:00.04

SQL> TRUNCATE TABLE T_BIG;

Table truncated.

Elapsed: 00:00:01.52

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Session altered.

Elapsed: 00:00:00.00

第二次运行TRUNCATE，虽然时间比第一次运行要短1/3，但是仍然要花费将近2秒的时间。

问题描述者通过TKPROF对得到的TRACE文件进行了格式化，并将格式化后的文件发到了论坛上，其中格式化后的结果类似于：

[oracle@yans1 udump]$ more trace_result.txt

TKPROF: Release 10.2.0.3.0 - Production on星期四7月1 15:07:05 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: test08_ora_20627.trc

Sort options: default

********************************************************************************

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current  = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call

********************************************************************************

TRUNCATE TABLE T_BIG

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.36       1.47       4453        365       9211           0

Fetch        0      0.00       0.00          0          0          0           0

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

total        2      0.37       1.48       4453        365       9211           0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 74

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

----------------------------------------   Waited  ----------  ------------

db file sequential read                      4452        0.00          0.06

reliable message                                5        0.00          0.00

enq: RO - fast object reuse                    11        0.03          0.22

local write wait                             2229        0.00          0.67

log file sync                                   1        0.00          0.00

SQL*Net message to client                       1        0.00          0.00

SQL*Net message from client                     1       10.76         10.76

********************************************************************************

.

.

.</