Re: intermittent commit on insert ?

In article <adl8ln$1tk4$1_at_ID-85580.news.dfncis.de>, "Chuck" says...
>
>"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
>news:3cfd30be$0$8506$cc9e4d1f_at_news.dial.pipex.com...
>> "Chuck" <chuckhNOSPAM_at_softhome.net> wrote in message
>> news:adj4tq$116qbh$1_at_ID-85580.news.dfncis.de...
>> > Why would smaller commits increase the log volume? You're still
>inserting
>> > the same amount of data into the table. The miniscule amount of log
>space
>> to
>> > hold the commit records isn't even worth mentioning.
>>
>> No-one said it would change the log volume. Redo isn't the issue here.
>Undo
>> is.
>>
>
>Actually Sy did.
>
>
>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
>news:7utpfuooqromk8krj1dd3nj0b73a8go8ga_at_4ax.com...
>> And, oh yes, just to inform you : every transaction has overhead.
>> Limiting the number of rows committed, will increase the overhead,
>> will increase the redolog volume etc.
>
>Switching back to current article...
>
>"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
>news:3cfd30be$0$8506$cc9e4d1f_at_news.dial.pipex.com...
>>The point is that the rollback system will suffer under artificially high
>>numbers of transactions. You commit at the end of a transaction and the
>>DBA's job (pain that it is) is to size rollback segments appropriately.
>
>
>In an ideal world and in text books that would be possible - but so would
>having one CPU for each process, and terabytes of real memory so no tasks
>ever swap out. In the real world rollback segments fill up, disk size is
>limited, and sometimes you have to commit more frequently than you want to.
>I would not want to be in the position of having to explain to my boss why
>I'm tying up gigabytes of space for a rollback segment that gets used once
>in a blue moon.
>
>
>

Well, I take a different view on this.

I wouldn't want to be in the position of explaining to my boss why I spent untold hours on a one time process (and how many "one time processes" do i see, oh, about 1 a week probably) wasting my salary when a $250 (usd) 36gig scsi drive would have solved the problem in a heartbeat. Not to mention that the data we need to make available -- just isn't -- during all of this.

I see these intermittent commit programs and get a chill. 99% (or more) of them are not "restartable". That is, if they FAIL in the middle, they cannot be safely restarted -- they will update the same row again (incrementing that salary twice) or they will duplicate the data by insertined it twice. The most recent one I saw was an "upsert" type of program. It did an insert if the row didn't exist, it added an amount to a column if it did. Well, the intermittent commit caused an ora-1555 (surprise surprise, fetch across a commit), and it fails. If they restart it -- it would then UPDATE the rows it just inserted -- in effect doubling the amount stored, in reality corrupting the data!

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4092701511352

(it's real). Now, I think of the amount of time that person spent/will spend

1. coding the "solution"
2. fixing the mess they are in, now that they did the partial work and it "failed"
3. debugging this code (there were other bugs hidden in there)

All because of "tight disk space". I'll bet that if you multiply their hourly rate times the hours spent and add in the lost opportunity cost of not having this data available for use by the people who need it -- even worse -- having it in a corrupt, unreliable state -- that it would be significantly more then sizing the system for what the system needs to do. Especially since these "one time jobs" seem to happen again and again and again...

These "one time" jobs (and if you have more then one, as you will -- they are jobs "plural") are among the most frequent cause of pain for people. The time spent coding, debugging, fixing bad data after you find you cannot restart (and if you do it restartable, double or triple the code development time and quadruple the debug time)

That is what I wouldn't want to have to explain to my boss... I'd rather justify gigabytes of disk (fixed cost, done). How many schedules have slipped due to these "one time" things....

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp