Response Time When Deleting Records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Response Time When Deleting Records

  1. #1
    Join Date
    Sep 2000
    Posts
    96
    We are in the process of testing scripts that would delete approximately 30,000 rows from our database. We are running Oracle8i on an NT server.

    We are finding that it is taking about 5 minutes to delete 100 rows. The processor is running at 100% capacity. Does anyone have any ideas about how we could speed this up?

    If there is a high level of fragmentation in the indexes could this be causing a slowdown since it would take longer to locate each row to be deleted?

    Thanks for any ideas you may have!!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hard to say without seeing the Query Plan. You may not even be using indexes. What process is consuming 100% of the CPU, dbwr, lgwr, user session, etc.? What does each of the disks utilization look like?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    30000 rows deletion is a small job for Oracle. I think some user process might use the rows when you are trying to delete them. Otherwise it won't take long time.
    Also assign exclusive rollback segment for this transaction and try again.
    If you are sure that you will not issue rollback, then alter the table in NOLOGGING mode, so that redo will not be generated.

  4. #4
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    Check for cascading constraints as well. if you have them, you may find that deleting from the children up the tree may be less expensive. i doubt that is the root problem, however, unless it's a really extreme constraint cascade.

    d.

  5. #5
    Join Date
    Sep 2000
    Posts
    96
    Thanks for all your suggestions. We'll give them a try.

    One other question, though. I didn't think the NOLOGGING option could be used when deleting rows. Are you referring possibly to altering the table in some way before performing the deletes?

    Thanks for all the input!

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Nologging, I believe is only with regards to inserts.

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