Re: intermittent commit on insert ?

In article <3CFFB100.5CD6F2BC_at_mitre.org>, Peter says...
>
>Tom,
>
>Correct me if I'm wrong on this, but doesn't sqlldr do commits as it
>goes, by default. (assuming non-direct load). I'm not sure you can turn
>this "feature" off, or is that what "commit_discontinued" is all about?
>
>-Peter
>

but -- it is restartable, there is the log that tells you how far it got and skip to skip over the already loaded records in the input file.

I do personally wish there was a way to disable the commit in sqlldr.

>Thomas Kyte wrote:
>>
>> 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
>>
>> a) coding the "solution"
>> b) fixing the mess they are in, now that they did the partial work and it
>> "failed"
>> c) 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

--
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