Deleting records takes long time and unltimately servers hang
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Deleting records takes long time and unltimately servers hang

  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Cool 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!!!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace it - see where your time is being taken

  3. #3
    Join Date
    Apr 2009
    Posts
    6
    Can you please suggest how can I trace it?

  4. #4
    Join Date
    Oct 2006
    Posts
    175
    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 03:39 AM.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ask your dba, they will know how to trace sessions for you

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    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.
    this space intentionally left blank

  7. #7
    Join Date
    Nov 2005
    Location
    USA
    Posts
    32
    Consider partitioning the table and alter table drop partition will address your issue

  8. #8
    Join Date
    Apr 2009
    Posts
    6
    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.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by CoolGuyz View Post
    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.

  10. #10
    Join Date
    Apr 2009
    Posts
    6
    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
  •  


Click Here to Expand Forum to Full Width