Re: Delete Performance Issue

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 22 Dec 2006 09:10:02 -0000

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, I'd like to know
if the workarea_size_policy is set to the default AUTO, and
what the pga_aggregate_target is.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

Date: Thu, 21 Dec 2006 12:46:54 -0500
From: "Don Doo" <doodon@xxxxxxxxx>
Subject: Delete Performance Issue

 1  select OPNAME||' '||MESSAGE||' '||ELAPSED_SECONDS  from
 2* where sql_hash_value=2467621466
SQL> /

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),
                 from v$sql where hash_value = 2467621466

Oracle version
Hash_area_size  8 MB
Sort_area_size   4 MB


Other related posts: