management paranoia about 2 million rows updated in 1 commit
Date: Wed, 22 Aug 2012 11:41:11 -0400
Message-ID: <CAE-dsOKmwCFJYSkRBGjZo8YJKxwQmtZ5NxWFZf5AuLe43P7Zpw_at_mail.gmail.com>
I don't think this is a big deal, but I'm looking for suggestions on what to tell them to get them to let this go. DB Version: 10.2.0.5
OS: HP Unix.
I have to update 2 million rows in a 100 GB table that is partitioned. I had to get the rows to update from 2 large tables. So I create a small table that has all the data needed for the update and it is partitioned the same was as the large table I am updating. I added a local unique constraint to the small table. This let me run the whole thing in parallel (I used parallel DML). I turned off triggers. The update runs in 5 minutes. It takes about 2-3 more minutes to build my small update table. This relatively simple update took me about 1.5 hours to write and test. So in my opinion, I am done.
I really don't think this is a big. Management is 'worried' that if we have to rollback it could take hours. So they want me to re-write it with pl/sql and commit every 100 records( I have plenty of undo, so fetch across commits won't be an issue). First off this will take much longer to run and be a big hassle cause I would probably need to spin off 1 session for each partition to get this to finish in a timely manner.
We have an extended outage while we do this for a release. There is alot going on so the system will be offline for a day. If I kill it and it has to rollback my worst case scenario is about 30 minutes. (I did kill it at 4 minutes and 55 seconds in a DB that is a clone of production. It took about 10 minutes to rollback). Managers keep going 'I have seen cases where it takes days to rollback'. This is only 2m rows, no trigger. If there is a problem with the rollback (the odds of this are really low, the odds of needing to rollback are so low to be almost non-existent. I have plenty of undo space), I say just shutdown abort and restart. This generally just takes a few minutes, but managers tend to freak when they hear 'shutdown abort'. I have done this kind of thing many times before. I cannot speak to 'other times we have seen rollbacks take awhile' since I was not there and I did not write the update.
Sometimes managers turn simple things into a hassle. This little update took me about an hour to write an update. Including building the small update table, this thing ran in about 8-9 minutes. But dealing with the overhead is taking forever.
any suggestions on what to say?
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 22 2012 - 10:41:11 CDT