Re: Delete Performance Issue

Since you're using 9.2.0.4, any execution plan you offer should be at least as informative as the information from

    explain plan for ...
    select * from table(dbms_xplan.display);

The execution plan, however looks reasonable - generate the list of unique values from the subquery (you said 3 to 6) then scan the big table for a match.

Assuming this is the truth, then the delete should be efficient, and the updates to the (ASSM) bitmap blocks should be efficient. However, you record 1.2Million disk reads - which, at a typical 100 per second (assuming random I/Os) is a fair justification for the 14,000 seconds you are worried about.

In the absence of any other issue, I would suspect you are seeing excessive I/O from index maintenance - with a little extra load coming from the undo generation.

If I'm right, there isn't a lot you can do about this - perhaps drop some indexes; or possibly see if you can force an indexed access path for the delete (for reasons why this might help see

    http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/ though I have to say that I don't recall checking whether this applies to deletes as well as updates).

You don't need to run this with a trace to get better information, just take a coupld of snapshots of v$session_event to see if most of the time is on scattered reads, sequential reads, log file sync, or other; then do a number of snaps of v$session_wait to get a small sample of exactly what the wait is for.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Don Doo" <doodon_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Friday, December 22, 2006 10:56 AM Subject: Re: Delete Performance Issue

> Hi Jonathan,
>
>
> Thanks for the reply. Please see the execution plan below. The work
> area policy is manual
> and we have 8MB hash area size and 4MB sort area size. As I reported
> before, we delete
> about 200K rows daily and tht operation is complete in less than 2
> minutes. When the volume
> went up to 2.5 million (sales activity related to year-end ) the delete
> operation is taking nearly 4 hours.
> There is no change in the execution plan by the increse in volume.
>
>
> 0 DELETE STATEMENT
>
>

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