Re: Excessive cpu usage on linux 64bit

From: Dennis Williams <>
Date: Wed, 25 Jun 2008 16:56:58 -0500
Message-ID: <>


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

Received on Wed Jun 25 2008 - 16:56:58 CDT

Original text of this message