Re: Excessive cpu usage on 10.2.0.3 linux 64bit

Hi Dennis,

    thanks for the reply, I try to share more informations. the system is a 10.2 new install with a patch 2 installed. I'm deleting all rows, and the data are only 10% more than the previous delete, the tablespace has an uniform extent allocation and the extent size is 200mb
The file in the trace file are the index and data tablespaces, I don't have the partition option installed.
I issued delete from tableXX so there are no intermediate commits. I have redologs (about 1,2gb) on two separate 15k rpm disks in raid0. the fk are indexed and I'd like to maintain the constraints since I'll not allowed to drop constraints when the system will go in production. I will check the system statistics in order to better understand what it's going on.
What really puzzles me is that I cannot understand where the cpu is spent, because it's the cpu usage that slows the entire delete. Thanks a lot,
Giovanni

On Wed, Jun 25, 2008 at 11:56 PM, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
> 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

-- 
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
--
http://www.freelists.org/webpage/oracle-l