RE: Delete Performance Issue
- From: "Kerber, Andrew" <Andrew.Kerber@xxxxxxx>
- To: rprabha01@xxxxxxxxx, doodon@xxxxxxxxx
- Date: Thu, 21 Dec 2006 11:59:45 -0600
Could you rewrite the subquery using exists and accomplish the same
purpose? Something like this (may not be quite right), the idea is to
only hit the first matching record in the subselect, instead of getting
all of them:
delete from cs_trace cs
where cs.targetperiod= 200612
and exists (select
RunSeq from C_Run pr,
C_Summary ss,
C_Stage st
where cs.RunSeq = ss.RunSeq
and ss.stageType = st.stageType
and st.name <http://st.name/> = 'load'
and pr.period = 361
and ss.Active = 'yes')
Andrew W. Kerber
Oracle DBA
UMB
816-860-3921
andrew.kerber@xxxxxxx
"If at first you dont succeed, dont take up skydiving"
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rajeev Prabhakar
Sent: Thursday, December 21, 2006 11:55 AM
To: doodon@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Delete Performance Issue
Hi Don
One of the things you could try is parallel DML if
your database server is not too cpu bound.
Plus, verify that relevant indices are present.
HTH
-Rajeev
On 12/21/06, Don Doo <doodon@xxxxxxxxx> 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/>
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
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
Other related posts:
