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_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