-
Deleting records takes long time and unltimately servers hang
We have a simple query to delete huge records (Keeping only records from last two days) from database and it runs during night time. But it takes around 45 mins and by that time transactions just wait for data and servers utilization go high.
delete from table where date < sysdate-2;
Any suggestions or ulternatives how we can improve the time of this job?
Your help will be greatly appreciated!
Thanks in advance!!!
-
trace it - see where your time is being taken
-
Can you please suggest how can I trace it?
-
Code:
Any suggestions or ulternatives how we can improve the time of this job?
Your help will be greatly appreciated!
Thanks in advance!!!
If the ratio is like 10:1(ie., i mean very high) or more between what gets deleted and what is left behind, a better approach would be to CTAS what you want to retain and drop/truncate the old table. Rename it to original, if dropped.
If you can't do this, there are lots of ways to improve it; at first, do as Davey said, trace. Analyze it. Diagnose. Tune it.
Last edited by gtcol; 04-12-2009 at 02:39 AM.
-
ask your dba, they will know how to trace sessions for you
-
You can also do date range partitioning. Every day would be one partition, and every day you would drop the partition from two days ago. Just be aware that an on delete row level trigger would probably be expensive to maintain.
-
Consider partitioning the table and alter table drop partition will address your issue
-
Query is -
DELETE FROM ECOOVERLAYEVALUATION
WHERE creationdate < sysdate - 2;
COMMIT;
This table "ECOOVERLAYEVALUATION" in turn connects to other tables and deletes records from them.
I can not use Truncate as I do want to retain data for past two days.
-
Originally Posted by CoolGuyz
Query is -
DELETE FROM ECOOVERLAYEVALUATION
WHERE creationdate < sysdate - 2;
COMMIT;
This table "ECOOVERLAYEVALUATION" in turn connects to other tables and deletes records from them.
I can not use Truncate as I do want to retain data for past two days.
So you are cascade deleting? I see, you are adding pieces of information one-at-a-time, some people would get crazy about that
What else should we know?
By the way, are you sure your predicate is no like: ... < trunc(sysdate - 2) ?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
can we do something like "< trunc(sysdate - 2)'
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|