删除操作间歇减慢oracle 11g

Question

我们在生产环境中有一个审计表，我们每天删除PL/SQL批处理中的近1500万个数据(比某些天更旧的数据)。在好的一天，这个查询需要1.5小时才能删除相同的数据。但有时断断续续，这并不是删除相同数量的数据，即使在4小时内。此表上没有触发器，并对创建的列进行索引。

DELETE FROM SIEBEL.CX_AUDIT_SEARCH
WHERE CREATED < '20-MAy-2020' AND rownum <= 10000;

解释pla：

DELETE STATEMENT    ALL_ROWS    6   10000   180000                  
DELETE SIEBEL.CX_AUDIT_SEARCH                                   
COUNT(STOPKEY)                                  ROWNUM<=10000
INDEX(RANGE SCAN) SIEBEL.CX_AUDIT_SEARCH_U1 ANALYZED    6   170692  3072456             "CREATED"<'20-MAy-2020' 

你能提出任何可能的理由吗。

Answer 1

很明显，这张桌子很忙。15M行/天意味着大约10416行/分钟，这意味着在删除这些行时，将插入数千条记录。当Oracle尝试删除旧行并更新创建的索引时，会插入和提交更多行。

我看你一次要删除10000行。我假设您在这10000行之后提交，所以下面的方法可能会更快一些，因为SELECT只运行了一次，并且没有重新扫描1500次繁忙的表.

declare 
cursor c_rowids is
  SELECT T.ROWID
      FROM SIEBEL.CX_AUDIT_SEARCH T
     WHERE CREATED < '20-MAy-2020';
type t_tbl_rowids is table of rowid;
tbl_rowids t_tbl_rowids;
begin
  open c_rowids;
  Loop
    fetch c_rowids bulk collect into tbl_rowids limit 10000;
    exit when tbl_rowids.count = 0;
    forall i in 1..tbl_rowids.count
      DELETE FROM SIEBEL.CX_AUDIT_SEARCH
        WHERE ROWID = tbl_rowids(i);

    COMMIT;
  End loop;
  close c_rowids;
end;

Answer 2

这是一个有点长的评论。

对于此过程，您希望对表进行分区。这为每个分区将表存储在单独的“文件”中。也可以删除分区--这个过程比删除行要快得多。

您已经将要删除的行描述为比特定日期更早的行。这是分区解决方案的亮点。

您可以在文档中了解更多有关它的信息。我很惊讶你会有这么大的数据库而不知道这个功能。