DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Looking for ideas for a very large delete

  1. #1
    Join Date
    Apr 2001
    Posts
    108

    Looking for ideas for a very large delete

    Morning all,

    I'm looking for a way to perform a very large delete and I'm looking for some help.

    This issue is in a production instance running 8.1.7.4 (cannot upgrade due to vendor restrictions) and the instance is in archive mode. There is a message table that is used to hold informational & error messages from app processing. This table grows at about 3 million rows per day.

    We have a purge process that runs nightly to delete all informational messages. This purge process also deletes error messages after 30 days. But a delete on this table creates large numbers of archive logs. This occasionally becomes a problem as the log destination fills up. We use RMAN to back up the instance with a cron job to review the size of the volumn holding the archive logs. When this volumn reaches 60 percent full, it kicks off a job to RMAN to back up the archive logs. Occasionally, RMAN is unable to find a free tape drive in the library so it fails.

    I believe we could get by without the archive logs for this table. So we have been playing with several different options to achive this and I'm looking for opinions and ideas.

    1) We could create a temp table and insert append nologging into this table the rows we want to keep. Then drop indexes on the message table and truncate the table. Then insert append nologging from the temp table back into the message table and build the indexes. The drawback to this is that we have about 13 million rows that must still be kept in the table.

    2) We could drop the indexes and put the table into nologging mode. This might work as I think the only bit of data recorded in the archive logs then is the rowid for the deleted rows. After the delete we could rebuild the indexes. Doesn't the majority of archive log creation here actually have to do with the index maintenance?

    3) We could increase the size of the volumn where the archived logs are kept. Currently this is a 13 Gbyte volumn. Under normal conditions this does not cause a problem. It's also difficult to get additional disk assigned to this instance as most of the available disk in the Shark is already spoken for and the next budget year doesn't start until January.

    While I'm sure I didn't cover everything, I'm hoping for some great ideas from people here. Thanks in advance.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is it possible to tell whether a row is to be kept in the future or not at the time that it is inserted, or does that only become apparant later?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Is there a column that would allow you to partition informational vs. error messages? (Slim, is this where you are heading?)
    Do you have a period when the application doesn't run? (sounds like you do) - so you could drop & recreate the "informational" partition?

    (Best solution is to hire Mr.H to beat up your vendor, so only useful messages are produced.)

  4. #4
    Join Date
    Apr 2001
    Posts
    108
    The informational messages are kept for 24 hours, then deleted. The error messages are kept for 30 days to allow them time to process the errors.

    As to partitioning, it is a single column that is used to mark the type of error message. But we would also have to partition on the error grouping (member, member api, pricing, capitation, etc) as some error groups are never deleted. But it all becomes a mute point as management will not purchase partitioning due to the cost. We have the partitioning license on the data warehouse, but that is a different server. We have too many CPU's to purchase partitioning for the production instances.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Although it's deprecated in later versions, you might look at partition views -- ie. "poor-man's partitioning" where a number of underlying tables are union-all'ed together in a view that will allow inserts. It'd be fine for your own situation, I'd think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Apr 2001
    Posts
    108

    re: partitioned views

    No, that would entail changing the current vendor table. We try rather hard to not change the vendor code or structure as it makes upgrades much more difficult. Getting management approval to modify vendor code or ddl is almost impossible.

    I am in the process of creating this table in a sandbox instance so that I can play with various methods of deleting the rows. I hope to find a method where I can reduce the number of redo logs generated.

    Thanks for the help so far, it's just not that I can use the current suggestions.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    In that case, option 1 sounds like a reasonable solution, as long as you can ensure that no new non-deletable records get inserted into the table between copying out the ones you want to keep and truncating the table.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot avoid redo in a delete operation period

    What I have done in the past is write several dynamic pl/sql code (reusable for most tables) which disable indexes and PK/UK and insert into a temp table the data I need then truncate the original and move back the data and finally enable the constraints and rebuild the indexes. Wasnt too bad, deleted 450 million of rows and rebuilt 45 indexes in 3.5 hours in a 6 CPU server and Symmetrix array

  9. #9
    Join Date
    Apr 2001
    Posts
    108
    Yeah, I know you can't avoid redo in a delete, I am just trying to reduce the amount of redo.

    This is a simple table, no primary key and no referential integrity constraints to any other tables. There are also four indexes on the table.

    In a test run in a sandbox instance, I received the following results. I only checked for log switches in the alert log, I did not bother to turn on archiving.

    1) For the normal delete process as it runs today, to delete 1.8 million rows generated 17 log switches.

    2) I then rebuilt the table. Dropped all indexes, placed the table into nologging status, deleted the rows, placed the table back into logging status. Finally built all four indexes. This generated a total of 8 log switches.

    While I havn't had time to pull up logminer to see exactly what is in the redo logs, I can see that one of my ideas was correct. The lack of indexes greatly reduced the amount of redo that was generated. I will continue to play with this and will report back anything new that I find. If anybody else has any great ideas please let me know.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Why you set to table to nologging? That only works for direct loads

    From 17 to 8 log switches are because of indexes are disabled

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