Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Aug 2004
    Location
    France
    Posts
    754

    Unanswered: Very slow deletes

    Hello everyone,

    I'm running 9i v9.0.1.0 on a Sun Sparc 64 machine with Sun Solaris 2.9.

    I defined my undo tablespace as follows in my CREATE DATABASE statement :

    UNDO TABLESPACE undotbs DATAFILE '/disk03/oracle/oradata/Bourse/undotbs01.dbf' SIZE 750M REUSE

    I had already noticed that each time i perform a delete on lots of rows, it takes a very long time. Now, I've tried to delete 4,2 millions of rows at once, and after 2 hours and a half, the query execution stopped with error message :

    ORA-30036: unable to extend segment by 4096 in undo tablespace 'UNDOTBS'

    First of all, as far as I've understood, the UNDO TABLESPACE role is to write operations that are to be used if we afterwards execute a rollback to cancel the transaction. Is it correct ? And don't you think 750 M for undo is enough ? Would adding a datafile to UNDOTBS solve the problem ?

    Secondly, could someone tell me the difference between UNDO TABLESPACE and ROLLBACK SEGMENTS ? (i must admit it is quite unclear for me)

    Thirdly, I'm not sure, but I feel like one or more parameters are poorly initialized, and make my DML operations very slow, but I don't know which ones it could be. For now, in the development environment I can save data I don't want to delete by creating a new table (as select...) , then use TRUNCATE on my "big table", and finally re-insert data, but in the near future, i won't be able to do such a trick : i'll have to do deletes, and deletes that don't last for ages !

    For info, my "big table" (though for Oracle, I think it souldn't be that big) has two indexes : one unique on 4 fields (PK), and another one, non unique, on two fields.

    Thanks in advance for your help,

    Regards,

    RBARAER

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by RBARAER
    First of all, as far as I've understood, the UNDO TABLESPACE role is to write operations that are to be used if we afterwards execute a rollback to cancel the transaction. Is it correct ? And don't you think 750 M for undo is enough ?
    Well, if you are deleting 4.2 million rows then if the average row size exceeds around 187 bytes then you will run out of UNDO according to my quick calc. The UNDO has to contain an image of each deleted row, so that it can be re-inserted if you roll back.

    Quote Originally Posted by RBARAER
    Would adding a datafile to UNDOTBS solve the problem ?
    I guess so.

    Quote Originally Posted by RBARAER
    Secondly, could someone tell me the difference between UNDO TABLESPACE and ROLLBACK SEGMENTS ? (i must admit it is quite unclear for me)
    UNDO TABLESPACE is the more modern way to handle undo - Automatic Undo Management.

    Hopefully you won't be deleting such large quantities of data often in future? If so, why? Maybe you should be looking at partitioning.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    There are a number of things you can look at

    1. Increasing your undo tablespace may allow your operation to complete but it isnt going to make it any quicker. Dont forget index changes are also logged to the undo tablespace. Note undo is like the old rollback tablespace except that oracle manages the size and shrinks of the rollback segments.

    2. You redo logs should be on separate disks to the data, indexes and undo(and they should be unstriped but mirrored preferably, avoid RAID 5 at all costs for redo). The undo disks (or stripes) should also be seperate from the data and indexes.

    3. Look at your log_buffer (make sure it is a big value say 1M min) and your redo logs should be quite big (say 100M min).

    4. If you are deleteing a sizeable portion of the table look at partitioning (if you have that) using your delete criteria. Also look at deleting by creating a new table using direct path inserts,nolooging and without indexes. To do this on a production environment look at dbms_redefinition which can do this online without user interruption .

    5. If you are deleting a small percentage of the table is your delete going in on an index, if not maybe create an index (though this will add overhead on the delete).

    6. Benchmark on your prod environment before and after using a copy of the actual table, what applies to dev wont neccessarily apply on prod.

    7. If it is a heavily loaded system schedule the delete during a quiet period.

    Hope these pointer help.

    Alan

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thank you very much both of you !

    Tony,

    I may have to delete between approximatively 250 000 to 5 000 000 rows at a time in the future, several times a day, at least if I don't want my table to grow up to billions of records. I will have to record, from different sources of data and at different periods in the day, between 250 000 and 1 500 000 rows per day per source in the same table. I will only need these data for 1 or 2 weeks, so, for performance reason, I think I will delete all the rows older than 2 weeks. And if I do that every day, I may have to delete between 250 000 to 5 000 000 rows at a time, as I said.

    AlanP,

    May I precise my config a little more so that you may give me some more tips to optimize it ?

    I currently have 3 redo log files, 300M each. The log_buffer parameter is set to 163000 (I'm going to test with 1M as you preconized). Redo log files, undo tablespace and indexes are on the same disk, while data are on another one. In fact, I'm running two instances (each for a different db) and i got 3 disks : all redo log files, undo tablespace and indexes are on the same, as well as the system tablespace (each of these being on a different partition of the disk, though), while user data of the first db is on another one, and user data of the second db is on another one. Could you please tell me if I could organize this better with my 3 disks ?

    Why did I choose to have 2 instances ? Because the two databases may need different parameters (such as optimizer_mode : first_rows/all_rows, more importantly block_size, etc... and one makes a huge use of text indexes on CLOBS and varchar2 fields), but it was a cautionous choice from a developer, beginner as a DBA. It is subject to change if I find a better solution, speaking of performance. It's been 10 month that I study the Oracle 9i doc now, and about 3 month that I participate on this forum. I've learnt a lot of things, and understand Oracle more and more every day, but there is still a LONG way to go, and while some areas are clear enough (for now), some other ones are as dark as a cloudy night... But hey, learning Oracle is GREAT !

    And BTW, LOTS OF THANKS to everyone on this forum !

    Anyway, back to my problems, I currently don't use partitioning. Do you think it would be of a great help in my situation ? For info, isn't partitioning only in the Enterprise version of Oracle (the more expensive one) ?

    When I made these deletes, the system wasn't loaded at all, which surprised me even more, but I plan to do these on relatively calm periods.

    I'm aware of the surprises i may experience when passing to prod : i will test then, but I hope there won't be that much to change !

    Regards,

    RBARAER

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    UNDO is the way to go, forget rollback segments.

    although you didn't get this error, it would be important to be aware of this
    parameter:
    undo_retention

    It is not uncommon to have an UNDO tablspace size grow to about 2gig or
    more depending on the transactions issued.

    4.2mil rows is a good amount.
    be sure you have an index against the where clause.
    OR, you could select the rows you WANT into another table (nologging).
    After that, rename the tables so that your temp is now your official table.

    askTom.com has a good article regarding this.

    yes, partitioning comes with enterprise edition so if you do not have that
    then it would cost more.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) Is this your production instance or dev/test instance. I hope it is your dev/test instance as 3 disks is too little for this kind of job.

    2) With 3 disks try the following (and try other variations aswell)
    Assuming instance A & B
    Disk1 - Redo(A), Temp(A), Redo(B), Temp(B),
    Disk2 - Tables(A), Indexes(B), Undo(B), archivedlogs(A)
    Disk3 - Undo(A), Tables(B), Indexes(A), archivedlogs(B)

    The basic idea is to spread the IO especially the writes across the disks. Look at the OS stats to help you (sar).

    3) Partioning is a enterprise only addon option
    But you could do some sort of manual partitioning using code i.e. have multiple tables to hold your different data sets. Use a view to select from a union of all the tables. And to delete a dataset just truncate the appropriate table. This would give you instant deletes

    4) Is your delete going in on the right index or is it doing a FTS. Look at the clustering factor on the index, you sometimes find that changing the ordering of the columns can have a big effect on the usage of the index.

    5) Run statspack before and after your delete as this might point to bottlenecks in your system.

    Alan

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Ok.

    The_Duck,

    I've read AskTom's article. Thanks a lot. To sum up, I should either use partitioning by date, or copy data i want to keep in a new table, index it, grant it, drop the old one and rename the new one to the old name. Provided I, for now, choose the second solution, I think there might be a problem if I try to query this table while the "drop old - rename new to old name" phase, don't you think ?

    Alan,

    Thanks for your answers. Don't worry, i'm still in dev/test, but we should go to prod in some 2 or 3 months... And i'm not exactly sure of what to do. Concerning disks, you say 3 are not enough. Could you please propose, according to you, what would be a "good" solution for running my two instances ? That is : number of disks, and what to put on them. You highlighted the fact that undo/redo logs/data & indexes should be on separate disks. What of TEMP ? Wouldn't putting data and indexes on different disks provide better performance ? I thought so.

    Your "manual partitioning" proposal is interesting, I will consider it. By the way, what do you think of the "create new table - copy - drop old table - rename new" (see 1st paragraph) solution ?

    I do have an index on the fields of the where clause of my delete, so it should be chosen by the optimizer, but I will check that with explain plan.

    Concerning statspack, I understand it must be very useful, but I still didn't have time to check the doc for that, and still never used it. When i've time, I'll have a look at it. Could you, by chance, give me a quick example ?

    Best Regards,

    RBARAER

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) Re disks, I would say ideally you need 6 or more disks (or disk sets if you have striping) with mirroring on each one so you can keep the redo, temp, undo, archived logs, tables and indexes all seperate.

    2) If you cant afford partioning on the priod instance , manual partioning and multiple tables is your best bet as truncate doesnt generate significant redo.

    3) do you need 2 instances, having 2 schemas in one instance is more efficient in terms of memory and processes. Also if some processes need theior own parameters say first_rows say then can you do an alter sesssion to set the parameters you need for that job.

    4) see this for more info on statspack
    http://download-west.oracle.com/docs...a96533/toc.htm

    Alan

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thank you very much.

    I will try your suggestions, and see what seems the best for me.

    If you have anything else to add, do not hesitate !

    I'm about to start a new thread about "how to choose db_block_size", because if I join the two schemas in one single instance, i will have to use the same for both.

    Best regards,

    RBARAER

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Dont forget in 9i you can have different block sizes for different tablespaces/buffer caches.

    http://www.dbazine.com/burleson2.shtml

    Alan

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by RBARAER
    Ok.

    The_Duck,

    I've read AskTom's article. Thanks a lot. To sum up, I should either use partitioning by date, or copy data i want to keep in a new table, index it, grant it, drop the old one and rename the new one to the old name. Provided I, for now, choose the second solution, I think there might be a problem if I try to query this table while the "drop old - rename new to old name" phase, don't you think ?
    With option#2:
    Close call. A possible solution would be use a synonym. The synonym starts
    as pointing to Table_A. You copy data you want to Table_B, set everything
    else up as you state above (be sure to add FKs if needed). THEN, all you do
    is alter the synonym to point to Table_B. I can imagine it would be pretty seamless.

    What do you think>?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thanks Alan for this remark, I will have a look at this possibility... That would avoid me finding a compromise between my 2Kb-Blocks instance and my 8Kb-Blocks instance. The article you pointed me is very interesting.

    The_Duck, you're right, with solution 2, a synonym would surely avoid the problem of switching from one table to the other. Good idea !

    Thanks again to both of you !

    Best regards,

    RBARAER

  13. #13
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36

    Cool

    Can also consider a separate table for each day of the month, and a dynamic view that selects the past 14 days. Then he can drop or trucnate the table at will...

  14. #14
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Re-reading this thread,

    One or two questions arose :

    1) The_Duck, I thought of using your idea, but the problem is that I may be inserting lots of data as well as querying it at the moment I would switch tables, so, even if I re-synch the new table after the synonym redefinition by inserting data from the old table that had not yet been inserted in the new, there will be a small period during which I could not query all available data. Or I could simply lock the tables (read & write) during the synonym redefinition, but IMO it's not a good solution. What do you think ?

    2) Alan, I looked at DBMS_REDEFINITION in the Oracle doc ("Oracle9i Supplied PL/SQL Packages and Types Reference"), from what you say it seems to be an interesting solution, but I can't see how it works. Would you have a concrete example ?

    3) Alan, if I use "manual partitioning", won't SELECT queries suffer from querying several tables through a view instead of only one partitioned table ? This would be a quite simple view, so I could insert through it, couldn't I ? This is important for me because it would not be practical at all to change the table name in which to insert data in my inserting client programs.

    4) Would real partitioning have any drawback compared to the other solutions we spoke of, or only advantages ? In your opinion, would it be worth the price (about 8000 euros / cpu) ?

    5) Related to 4), if I want a rock-solid DBMS where performance and availability (24/24 6/7 minimum, ideally 24/24 7/7) are critical, do I really have the choice between different Oracle packages (edition one, standard, enterprise), or is Enterprise the only choice ? Is the difference between standard and enterprise only in the options one can add, such as partitioning ?

    Thanks and regards

    RBARAER

    PS : Alan, I realize it was you who pointed Burleson's article to me (I speak of it in my last thread when talking to anacedent). Thanks again.

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    2) Excellant example of dbms_redefinition here

    http://www.orafaq.com/articles/archives/2003_08.htm

    I have actually done this a few time on a production system with live tables and it works

    3) The select would have an overhead in doing the select against multiple tables but if it has some indexed criteria it should reduce this overhead. To insert into the view you would probably need an INSTEAD OF trigger to choose which table to insert into but this would have a performance overhead.

    4) the only drawback with real partitioning is the price

    5) standard is just as reliable as enterprise BUT enterprise has nice features like online index rebuilds which help in increasing availability. There is a doc in technet somewhere which lists exactly what standard lacks.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •