Re: Excessive cpu usage on 10.2.0.3 linux 64bit

Giovanni,

Since nobody has replied to your question yet, here are a few ideas:

• Realize that deletes are expensive. If you are deleting many rows, it is usually faster to copy the rows you want to keep into another table, trunctate the original table, then copy the saved rows back.
• If you regularly delete many rows from a table, consider using EXCHANGE PARTITION in your architecture.
• What changed since this delete worked fast and now? More rows? Is it possible that successive deletes have changed the row population? Maybe your first deletes were from a few concentrated blocks, and these deletes are now spread out across more database blocks?
• Have you confirmed the FK has an index? Can you drop the FK for the delete? If there is a chain of dependencies, well, that just compounds the performance problems.
• I notice your timing statistics mentions two files: #2 and #8. What is up with that?
• Part of the reason deletes are expensive is that not only does Oracle have to locate and load the block containing the row to be deleted, but undo and redo are exercised. Your performance bottleneck may lie there. You have provided the session wait statistics. Consider investigating the system wait statistics during the delete.
• Are you committing frequently? This reduces the undo, redo, and number of copies of data blocks in the data buffer.

Sorry, I have no firm answers for you, but perhaps my reply will cause someone to reply with better suggestions.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l