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
   1     0 DELETE
   2     1 HASH JOIN
   4132
   3     2 VIEW
   4     3 SORT               UNIQUE
   5     4 HASH
JOIN                                                               13
   6     5 TABLE ACCESS       FULL            C_RUN
6
   7     5 NESTED LOO
      6
   8     7 TABLE ACCESS       BY INDEX ROWID  C_STAGE       1
   9     8 INDEX              UNIQUE SCAN     C_STAGE_AK1
   10     7 TABLE ACCESS       FULL            C_SUMM
5
   11     2 TABLE ACCESS       FULL            C_TRACE             4113

Regards,

Don

On 12/22/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> I may have missed it since I only get the digest, but
> have we seen a proper execution plan for this query ?
> Until we there is little point in trying to guess what's
> going wrong.
>
>
> I note that the original question shows us the hash_area_size
> and sort_area_size - but since it's 9.2.0.4, I'd like to know
> if the workarea_size_policy is set to the default AUTO, and
> what the pga_aggregate_target is.
>
>
> 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
>
>
> > Date: Thu, 21 Dec 2006 12:46:54 -0500
> > From: "Don Doo" <doodon_at_gmail.com>
> > Subject: Delete Performance Issue
> >
> >
> > 1 select OPNAME||' '||MESSAGE||' '||ELAPSED_SECONDS from
> > v$session_longops
> > 2* where sql_hash_value=2467621466
> > SQL> /
> > OPNAME||''||MESSAGE||''||ELA
> >
> ----------------------------------------------------------------------------------------
> >
> > Hash Join Hash Join: : 6592 out of 6592 Blocks done 13688
> > Hash Join Hash Join: : 6272 out of 6272 Blocks done 12753
> > Hash Join Hash Join: : 6272 out of 6272 Blocks done 13594
> > Hash Join Hash Join: : 7488 out of 7488 Blocks done 14050
> >
> > Looks like it takes 14050 seconds to complete the hash join which
> > matches the time taken to complete the delete.
> >
> > select HASH_VALUE,CPU_TIME,elapsed_time/(1000000*60),
> > fetches,disk_reads,
> > BUFFER_GETS,ROWS_PROCESsed
> > from v$sql where hash_value = 2467621466
> >
>
>
> > Oracle version 9.2.0.4
> > Hash_area_size 8 MB
> > Sort_area_size 4 MB
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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