Path: news.easynews.com!easynews!news-out.visi.com!hermes.visi.com!nycmny1-snh1.gtei.net!news.gtei.net!bloom-beacon.mit.edu!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: intermittent commit on insert ? Date: 6 Jun 2002 15:23:09 -0700 Organization: Oracle Lines: 121 Message-ID: References: <3CFCE560.9EB15807@exesolutions.com> <7utpfuooqromk8krj1dd3nj0b73a8go8ga@4ax.com> <3cfd30be$0$8506$cc9e4d1f@news.dial.pipex.com> <3CFFB100.5CD6F2BC@mitre.org> NNTP-Posting-Host: p-389.newsdawg.com X-Newsreader: Direct Read News 2.96 Xref: easynews comp.databases.oracle.server:149803 X-Received-Date: Thu, 06 Jun 2002 15:48:08 MST (news.easynews.com) In article <3CFFB100.5CD6F2BC@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 , "Chuck" says... >> > >> >"Niall Litchfield" wrote in message >> >news:3cfd30be$0$8506$cc9e4d1f@news.dial.pipex.com... >> >> "Chuck" wrote in message >> >> news:adj4tq$116qbh$1@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" wrote in message >> >news:7utpfuooqromk8krj1dd3nj0b73a8go8ga@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" wrote in message >> >news:3cfd30be$0$8506$cc9e4d1f@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