Re: intermittent commit on insert ?

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

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
Received on Thu Jun 06 2002 - 13:59:12 CDT