Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Delete Performance Issue

Re: Delete Performance Issue

From: Don Doo <doodon_at_gmail.com>
Date: Fri, 22 Dec 2006 06:09:04 -0500
Message-ID: <36f8e4b00612220309m21654ae2pf37f8d72e0669be9@mail.gmail.com>


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
Received on Fri Dec 22 2006 - 05:09:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US