RE: Delete Performance Issue

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: