从 Oracle 10g 中具有 CLOB 字段的表中缓慢删除

Slow deletes from table with CLOB fields in Oracle 10g

提问by bitstream

I am encountering an issue where Oracle is very slow when I attempt to delete rows from a table which contains two CLOB fields. The table has millions of rows, no constraints, and the deletes are based on the Primary Key. I have rebuilt indexes and recomputed statistics, to no avail.

当我尝试从包含两个 CLOB 字段的表中删除行时，我遇到了 Oracle 速度非常慢的问题。该表有数百万行，没有约束，删除基于主键。我重建了索引并重新计算了统计信息，但无济于事。

What can I do to improve the performance of deletes from this table?

我可以做些什么来提高从此表中删除的性能？

回答by Gary Myers

Trace it, with waits enabled

跟踪它，启用等待

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_monitor.htm#i1003679

Find the trace file in the UDUMP directory. TKPROF it. Look at the end and it will tell you what the database spent its time doing during that SQL. The following link is a good overview of how to analyze a performance issue.

在 UDUMP 目录中找到跟踪文件。TKPROF 吧。看看最后，它会告诉您数据库在该 SQL 期间花费了多少时间。以下链接很好地概述了如何分析性能问题。

http://www.method-r.com/downloads/doc_download/10-for-developers-making-friends-with-the-oracle-database-cary-millsap

回答by Nick

With Oracle you have to consider the amount of redo you are generating when deleting a row. If the CLOB fields are very big, it may just take awhile for Oracle to delete them due to the amount of redo being written and there may not be much you can do.

对于 Oracle，您必须考虑删除行时生成的重做量。如果 CLOB 字段非常大，由于写入的重做量很大，Oracle 可能需要一段时间才能删除它们，而且您可能无能为力。

A test you may perform is seeing if the delete takes a long time on a row, where both CLOB fields are set to null. If that's the case, then it may be the index updates taking a long time. If that is the case, you may need to investigate consolidating indexes if possible, if deletes occur very frequently.

您可以执行的测试是查看删除是否在行上花费很长时间，其中两个 CLOB 字段都设置为 null。如果是这种情况，则可能是索引更新需要很长时间。在这种情况下，如果删除非常频繁，您可能需要研究合并索引（如果可能）。

If the table is a derived table, meaning, it can be rebuilt from other tables, you may look at the NOLOGGING option on the table. You can the rebuild the table from the source table, with minimal logging.

如果表是派生表，意思是可以从其他表重建，你可以查看表上的NOLOGGING选项。您可以从源表重建表，使用最少的日志记录。

I hope this entry helps some, however some more details could help diagnose the issue.

我希望这篇文章对一些人有所帮助，但是更多的细节可以帮助诊断问题。

回答by WW.

Are there any child tables that reference this table from which are deleting? (You can do a select from user_constraintswhere r_constraint_name= primary key name on the table you are deleting from).

是否有任何引用该表的子表正在删除？（您可以在要从user_constraints中r_constraint_name删除的表上执行 select from where = primary key name）。

A delete can be slow if Oracle needs to look into another table to check there are no child records. Normal practice is to index all foreign keys on the child tables so this is not a problem.

如果 Oracle 需要查看另一个表以检查没有子记录，则删除可能会很慢。通常的做法是索引子表上的所有外键，所以这不是问题。

Follow Gary's advice, perform the trace and post the TKPROF results here someone will be able to help further.

遵循 Gary 的建议，执行跟踪并在此处发布 TKPROF 结果，有人将能够提供进一步帮助。

回答by Quassnoi

Your UNDOtablespace seems to be the bottleneck in this case.

UNDO在这种情况下，您的表空间似乎是瓶颈。

Check how long it takes to make a ROLLBACKafter you delete the data. If it takes time comparable to the time of the query itself (within 50%), then this certainly is the case.

检查ROLLBACK删除数据后需要多长时间进行制作。如果它花费的时间与查询本身的时间（在 内50%）相当，那么情况肯定是这样。

When you perform a DMLquery, your data (both original and changed) are written into redo logs and then applied to the datafiles and to the UNDOtablespace.

当您执行DML查询时，您的数据（原始数据和更改数据）将写入重做日志，然后应用到数据文件和UNDO表空间。

Deleting millions of CLOBrows takes copying several hundreds of megabytes, if not gigabytes, to the UNDOtablespace, which takes tens of seconds itself.

删除数百万CLOB行需要将数百兆字节（如果不是千兆字节）复制到UNDO表空间，这本身就需要数十秒。

What can you do about this?

你能做些什么呢？

1. Create a faster UNDO: put it onto a separate disk, make it less sparse (create a larger datafile).
2. Use ROLLBACK SEGMENTSinstead of managed UNDO, assign a ROLLBACK SEGMENTfor this very query and issue SET TRANSACTION USE ROLLBACK SEGMENTbefore running the query.

1. 创建更快UNDO：将其放在单独的磁盘上，使其不那么稀疏（创建更大的数据文件）。
2. 在运行查询之前，使用ROLLBACK SEGMENTS而不是 managed为这个查询和问题UNDO分配一个。ROLLBACK SEGMENTSET TRANSACTION USE ROLLBACK SEGMENT

If it's not the case, i. e. ROLLBACKexecutes muchfaster that the query itself, then try to play with you REDOparameters:

如果不是这种情况，即ROLLBACK执行多快，查询本身，然后尝试你玩REDO参数：

1. Increase your REDObuffer size using LOG_BUFFERparameter.
2. Increate the size of your logfiles.
3. Create your logfiles on separate disks so that reading from a first datafile does not hinder writing to a second an so on.

1. REDO使用LOG_BUFFER参数增加缓冲区大小。
2. 创建日志文件的大小。
3. 在单独的磁盘上创建日志文件，以便从第一个数据文件读取不会妨碍写入第二个数据文件，以此类推。

Note that UNDOoperations also generate REDO, so it's useful to do all this anyway.

请注意，UNDO操作也会生成REDO，因此无论如何执行所有这些操作都是有用的。

NOLOGGINGadviced before is useless, as it is applied only to certain set of operations listed here, DELETEnot being one of those operations.

NOLOGGING之前的建议是无用的，因为它仅适用于此处列出的某些操作集，DELETE而不是这些操作之一。

回答by eckes

Deleted CLOBs do not end up in the UNDOTBS since they are versioned and retented in the LOB Segment. I think it will generate some LOBINDEX changes in the undo.

删除的 CLOB 不会出现在 UNDOTBS 中，因为它们在 LOB 段中进行了版本控制和保留。我认为它会在撤消中产生一些 LOBINDEX 更改。

If you null or empty the LOBs before, did you actually measured that time with commit separate of the DELETE? If you issue thousands of deletes, do you use batch commits? Is the instance idle? Then AWR report should tell you what is going on.

如果您之前将 LOB 置空或清空，您是否实际测量了与 DELETE 分开提交的时间？如果您发出数千次删除，您是否使用批量提交？实例是否空闲？然后 AWR 报告应该告诉你发生了什么。