Re: Excessive cpu usage on 10.2.0.3 linux 64bit
- From: "Giovanni Cuccu" <giovanni.cuccu@xxxxxxxxx>
- To: Oracle-L <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 26 Jun 2008 11:25:27 +0200
Hi,
I know that a delete is an expensive operation, the problem is
different. the difference is that four days ago the delete operation
on 4m rows took 20 minutes, two days ago with the same amount of data
took more than 20 hours. today I reissued the delete (yesterday we
drop the table) and it took the usual 20 minutes.
My investigations were aimed to undestand whey the delete was so slow.
the trace file showed more than 10 seconds before one db read and the
following one.
During these seconds the cpu was near 100% and I cannot figure why.
the only difference that I found this morning is tha another developer
saturated the data tablespace. after moving the offeding tables to a
new tabelspace the performance seems to be ok.
thanks,
Giovanni
>
>
> On Thu, Jun 26, 2008 at 10:59 AM, Yechiel Adar <adar666@xxxxxxxxxxxx> wrote:
>> Think about it.
>> For each record:
>> 1) For each fk:
>> - calculate the value for the the referencing fk.
>> - search the index to see if you do not invalidate the fk by deleting the
>> record.
>> 2) For each index on the table:
>> - calculate the index value.
>> - get the block that contain that value, may cause 0 or more physical I/O.
>> - update the index tree
>> 3) Delete the record from the block.
>>
>> Multiply this by 4 M records and you get a lot of work.
>>
>> Check the logical reads and I think you will see a lot of them.
>>
>> Adar Yechiel
>> Rechovot, Israel
>>
>>
>>
>> Giovanni Cuccu wrote:
>>>
>>> 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
>>>
>>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
>
> --
> --------------------------------------------------------------------
> "You don't know the power of dark side" - Darth Vader
>
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
--
//www.freelists.org/webpage/oracle-l
Other related posts:
