delete from table_name操作非常慢

overmars

数据库刚刚启动，表TX也是刚刚导入，数据库在我的PC机上面，没有其他人使用这个数据库。

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/tiger
已连接。
SQL> desc TX
名称                                      是否为空? 类型
----------------------------------------- -------- -------------------
col1                               NOT NULL VARCHAR2(16)  
col2                                    NOT NULL VARCHAR2(20)
col3                               NOT NULL NUMBER(1)
col4                               NOT NULL NUMBER(10,4)
col5                              NOT NULL NUMBER(10,4)
col6                              NOT NULL NUMBER(10,4)

col1和col2是主键列
SQL> select index_name from user_indexes where table_name='TX;
INDEX_NAME
------------------------------
PK_TX
SQL> select segment_name,bytes from user_segments where segment_name in('PK_TX,'TX');
SEGMENT_NAME                                                                           BYTES
---------------------------------------------------------------------------------     ----------
TX                                                                        32505856
PK_TX                                                                     28311552
SQL> set timing on
SQL> set autotrace on
SQL> delete from tx;
已删除810620行。
已用时间:  00: 01: 18.94   ===32M的表删除要80秒！
执行计划
----------------------------------------------------------
Plan hash value: 3856598301
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |               |   807K|    16M|  1190   (2)| 00:00:15 |
|   1 |  DELETE            | TX |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TX |   807K|    16M|  1190   (2)| 00:00:15 |
------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
    1621503  recursive calls   ==为啥如此多的递归调用
    3310719  db block gets
       4001  consistent gets
       3291  physical reads
  407892880  redo size   ==产生了400M日志
        833  bytes sent via SQL*Net to client
        778  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     810620  rows processed

为啥删除80万行的数据需要读取330万个数据块呢？，平均删除1行需要读取4个数据块。

[ 本帖最后由 overmars 于 2012-5-31 16:22 编辑 ]

Maclean Liu(刘相兵

action plan:

1.  收集该SQL运行的  10046 trace level 12  
2.  如果无法重现该SQL，考虑针对该SQL的SQL_ID做一个 sqltrpt 报告上传

@?/rdbms/admin/sqltrpt

wkc168

1621503  recursive calls   ==为啥如此多的递归调用

检查此表上是否有外键关联，以及触发器 ；
也可以上ML 说的做哥10046 12级的看看就知道


也没加任何条件 等同于全表删除 为啥不考虑效率较高的TRUANCATE TABLE TBNAME

overmars

触发器是肯定没有的。
EM中看到这个"剩余等待数"设个什么等待事件呢？ 不在oracle的12个等待事件类型中。

10046,level 12结果：
delete from TX

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     28.18      58.77        186       7718    3311348      810620
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     28.18      58.77        186       7718    3311348      810620
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 63  
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  TX (cr=7946 pr=186 pw=0 time=58792014 us)
    810620     810620     810620   TABLE ACCESS FULL TX (cr=3828 pr=49 pw=0 time=2623902 us cost=1053 size=17833640 card=810620)

Elapsed times include waiting on following events:
  Event waited on                                     Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                            145        0.33          8.57
  db file scattered read                         11        0.39          0.46
  log file switch completion                      7        1.70          4.62
  log file switch (checkpoint incomplete)        17        2.31         15.03
  log buffer space                                4        0.22          0.49
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       10.25         10.25
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2     28.18      58.77        186       7718    3311348      810620
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     28.18      58.77        186       7718    3311348      810620
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------       Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2      599.86        610.12
  db file sequential read                       145        0.33          8.57
  db file scattered read                         11        0.39          0.46
  log file switch completion                      7        1.70          4.62
  log file switch (checkpoint incomplete)        17        2.31         15.03
  log buffer space                                4        0.22          0.49

Maclean Liu(刘相兵

请上传 原始的10046 TRACE 为附件形式

magic007

从你的case来看，删除了80来万行数据，而有160万次recursive call，当然于1行调用了2次。可能是有其他表的外键引用了这张删除的表，甚至可能是两张外引用了这张表。外键指定了on delete cascade，所以删除的时候外键表也会删除。并且删除主表一行都要执行一次外键表的删除。

select  owner,table_name,constraint_name,constraint_type from dba_constraints where r_owner='SCOTT' and r_constraint_name='PK_TX';

Maclean Liu(刘相兵

确实很有可能如老熊所说的是由约束造成的问题，列出TX表的DDL及其依赖关系。

overmars

不好意思，今天才回复2位大佬！

C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on 星期一 6月 4 11:09:03 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select  owner,table_name,constraint_name,constraint_type from dba_constraints where r_owner='SCOTT' and r_constrain
t_name='PK_TX';
未选定行

SQL>


COPY一个表测试一次：
SQL> create table tx2 as select * from TX;
表已创建。
已用时间:  00: 00: 02.70
SQL> alter table tx2 add constraint pk_tx2 primary key(COL1,COL2);
表已更改。
已用时间:  00: 00: 04.40
SQL> set autotrace on
SQL> delete from tx2;
已删除810620行。
已用时间:  00: 02: 36.63
执行计划
----------------------------------------------------------
Plan hash value: 2562528842
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    22 |  1058   (1)| 00:00:13 |
|   1 |  DELETE            | TX2  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TX2  |     1 |    22 |  1058   (1)| 00:00:13 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
    1621533  recursive calls   ==还是一样多
    3310720  db block gets
       4067  consistent gets
       7258  physical reads
  407890616  redo size
        848  bytes sent via SQL*Net to client
        768  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
     810620  rows processed
SQL>
SQL>

Maclean Liu(刘相兵

原始10046 指的是 没有经过tkprof 的raw 10046 trace

就你经过处理的TKPROF信息看

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       88      0.03       0.01          0          0          2           0
Execute    155      0.07       0.17          0          0          0           0
Fetch      272      0.01       0.00          0        479          0         340
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      515      0.12       0.19          0        479          2         340

递归调用没有那么多 大约在515次




ODM TEST:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL> create table testme as select 'AAAAAAAAAAAAAAAAAAAA' col1,'AAAAAAAAAAAAAAAAAAAA' col2 , 10 col3,9999999 col4,99999999 col5,999999999 col6 from dual connect by level<=810621;

Table created.


SQL> create index ind_testme on testme(col1,col2);

Index created.


SQL> oradebug setmypid;
Statement processed.


SQL> oradebug 10046 trace name context forever,level 12;


delete testme


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1     38.72      38.59      11213       7893    3332686      810621
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     38.72      38.59      11213       7895    3332686      810621

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  TESTME (cr=8245 pr=11213 pw=0 time=38612357 us)
810621   TABLE ACCESS FULL TESTME (cr=7805 pr=6259 pw=0 time=21076214 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      4964        0.00          0.15
  db file scattered read                        429        0.00          0.03
  log file switch completion                     23        0.06          0.12
  latch: cache buffers lru chain                  2        0.00          0.00
  log buffer space                                5        0.28          0.54
  latch: cache buffers chains                     1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       11.51         11.51

同样消耗了较多的current block gets  3332686       次


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       89      0.00       0.00          0          0          0           0
Execute     89      0.01       0.00          0          0          0           0
Fetch      177      0.02       0.01        160        423          0          89
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      355      0.04       0.03        160        423          0          89


355次递归调用



delete 全表删除缓慢是正常现象， delete需要记录删除记录的景象到undo 并写出大量日志 是oracle中比较缓慢的一种 DML