OraFAQ Forum: Performance Tuning » To increase performance Delete statement

Home » RDBMS Server » Performance Tuning » To increase performance Delete statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To increase performance Delete statement [message #135340]

Wed, 31 August 2005 03:12

ashishodia
Messages: 10
Registered: August 2005
Location: Mumbai

Junior Member

Hi All,

I have a procedure to purging tables using followins delete statement each statement have large number of records to delete and take vaery long time .ther is any way to off logging (no need to maintain log for these statement ) or anyother way to improve performance of these stmt.

DELETE FROM CS_ACTIVATE_DEACTIVATE_SERVICE WHERE TRUNC(TIME_STAMP) < TRUNC(SYSDATE) - 30;
COMMIT;
( more then 60,000 record to delete )
take more time

DELETE FROM P_CS_ACT_DEACT_SERVICE WHERE TRUNC(TIME_STAMP) < TRUNC(SYSDATE) - 7;
COMMIT;

( more then 60,000 record to delete )
take more time

DELETE FROM CS_SEND_SMS WHERE TRUNC(MAKEDATE) < TRUNC(SYSDATE) - 30;
COMMIT;

( more then 60,000 record to delete )
take more time
DELETE FROM ERROR_LOG WHERE PROCESS IN ('XLU','XLD','SMS','SC') AND TRUNC(SYSDATE) -TRUNC(RUN_DATE) > 7;
COMMIT;
( max 100 record to delete )

DELETE FROM UPLOAD_DETAILS_TD A WHERE A.MAP_CODE IN ('XLU','XLD','SMS','SC') AND A.BATCH_ID IN
(SELECT B.BATCH_ID FROM UPLOAD_SUMMARY_TH B WHERE A.BATCH_ID=B.BATCH_ID AND A.MAP_CODE=B.MAP_CODE
AND TRUNC(SYSDATE) - TRUNC(B.MAKEDATE) > 15);
COMMIT;
( max 20 record to delete )

DELETE FROM UPLOAD_SUMMARY_TH WHERE MAP_CODE IN ('XLU','XLD','SMS','SC') AND
TRUNC(SYSDATE) - TRUNC(MAKEDATE) > 15;
COMMIT;
( max 20 record to delete )

DELETE FROM CS_CASE_DETAILS WHERE
TRUNC(SYSDATE) - TRUNC(CONTACT_DATE) > 91;
COMMIT;
( more than 5000 records to delete )

Regards,

Amit hishodia

Report message to a moderator

Re: To increase performance Delete statement [message #135402 is a reply to message #135340]

Wed, 31 August 2005 07:50

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Few thoughts here.
1. First do not commit between the deletes.
Do a one final commit.
2. When was the last time you analyzed / gathered the statistics for the
concerned table and index (if any )
After every major delte, you need to gather stats again.
3. It may also becuase of allocated extents.
Every time you delete, YOu only delete records.
Extents are never deallocated.
try this ( this will not move your indexes. you have to it seperately)
alter table mytable move tablespace same_tablspace;
try your delete now ( after gathering stats)
4. 60,000 is not at all a big deal.
First apply the above methods. You will see the difference.
If you have already done, we may need to look into other.

60,000 records is what you delete.
what is the total count of records?
say if you have 70,000 records and you are deleting 60,000 records out of it,
then the CTAS should help. DO something like this

create table another_mytable NOLOGGING as select (all the 10,000 records you want to keep) from mytable;
truncate table mytable.
insert /*+ append */ into mytable as select * from another_mytable.

Report message to a moderator

Re: To increase performance Delete statement [message #135424 is a reply to message #135340]

Wed, 31 August 2005 09:16

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Mahesh mentioned a lot of good ones, and 60,000 isn't very many, unless you have a very slow hardware. If after trying all the above you still don't see improvement, may try disabling many of your indexes on tables before the delete, then rebuilding them afterwards. Especially if you have lots of indexes that won't actually aid the deletion but slow it down due to them being maintained.

Report message to a moderator

Re: To increase performance Delete statement [message #135494 is a reply to message #135340]

Wed, 31 August 2005 17:05

ChrisJr
Messages: 17
Registered: August 2005

Junior Member

Other thought is mytable can be dropped and another_mytable can be renaned to mytable.Might be faster.

create table another_mytable NOLOGGING as select (all the 10,000 records you want to keep) from mytable;
drop table mytable.
rename another_mytable to mytable;

Report message to a moderator