Re: Delete Performance Issue


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@xxxxxxxxx>
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


Other related posts: