RE: Delete Performance Issue

small hash_area_size?  

Igor

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Doo Sent: Thursday, December 21, 2006 4:39 PM To: Stephane Faroult
Cc: oracle-l_at_freelists.org
Subject: Re: Delete Performance Issue

Hi Stephane,  

The targetperiod and runseq are not part of primary key. The target period represent weeks
and we have about 100 distinct values for that ( 2 years of data) and runseq has about 750
distinct values. The table has 65 million rows. Just for testing I created an index on these
two columns and the query was not using the index. I think that is the right thing.
The query has the same execution plan when it deletes 200,000 rows and 2.5 million rows.
It is using full scans for three tables and one NL and two hash joins. What could make a
hash join delete so slow when the number of records increases. I would really appreciate if
somebody could shed some light into that.  

Regards,  

Don  

On 12/21/06, Stephane Faroult <sfaroult_at_roughsea.com> wrote:

        Don,         

	   You don't say what your primary key actually is. Might it be
	(targetperiod, runseq)? This would not sound too bad to me. In
that 
	case, perhaps that
	
	delete from c_trace
	where (targetperiod, runseq)  in
	(select 200612, RunSeq
	from   C_Run pr,
	              C_Summary ss,
	              C_Stage st
	     where  pr.RunSeq = ss.RunSeq 
	               and    ss.stageType = st.stageType
	               and    st.name = 'load'
	             and    pr.period = 361
	      and    ss.Active   = 'yes')
	
	would help Oracle to see the light. I think that in such a case
I'd pay
	much attention to things such as the clustering factor (for
which, of
	course, you cannot do much. But it may give you an idea about
what you 
	can hope for) and the order of the columns in the PK index, that
may or
	may not favor an effective index scan.
	
	HTH
	
	Stephane Faroult
	
	
	
	
	Don Doo wrote:


	>

	> Hi,

	>

	>  We are facing a serious performance 

	> issue.  This is a delete statement and

	> it takes 4 hours to delete 2.3 million

	> rows from a 65 million row table.

	> The c_trace table has only one index

	> (the primary key) The table is not 

	> partitioned (we don't have the budget

	> to pay for partition option) Other tables

	>  have less than 5000 rows.

	> The sub-query returns 3 to 6 rows

	> depending on the values

	>

	> Query

	>

	> delete from c_trace

	>  where targetperiod= 200612

	>  and RUNSEQ in (select

	>  RunSeq from   C_Run pr,

	>                C_Summary ss,

	>                C_Stage st

	>       where  pr.RunSeq = ss.RunSeq

	>                 and    ss.stageType = st.stageType

	>                 and    st.name <http://st.name> = 'load' 

	>               and    pr.period = 361

	>        and    ss.Active   = 'yes')

	>

	>

	>  The V$session_longops shows

	>

	>   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

	>

	> HASH_VALUE   CPU_TIME     FETCHES DISK_READS BUFFER_GETS

ROWS_PROCESSED


	> ---------- ---------- ------------------------- ----------

---------- 


	> ----------- --------------

	> 2467621466  193010000                0

1265770


	> 13820713         2325397

	>

	> Oracle version 9.2.0.4 < http://9.2.0.4 <http://9.2.0.4> >

	> Hash_area_size  8 MB

	> Sort_area_size   4 MB

	> Statistics are current..

	> We are using ASSM  for these tables.

	> I would really appreciate any ideas to improve this statement.

We 


	> cannot do a event trace

	> here until mid of January next year because we are behind the

SLA and


	> don't want to make it slower.

	>

	> Regards,

	>

	> Don

	>

	
	

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