Path: news.easynews.com!easynews!priapus.visi.com!news-out.visi.com!hermes.visi.com!uunet!ash.uu.net!news.tufts.edu!newstransit.mitre.org!news.mitre.org!not-for-mail From: Peter Sylvester Newsgroups: comp.databases.oracle.server Subject: Re: intermittent commit on insert ? Date: Thu, 06 Jun 2002 14:59:12 -0400 Organization: The MITRE Corporation Lines: 106 Message-ID: <3CFFB100.5CD6F2BC@mitre.org> References: <3CFCE560.9EB15807@exesolutions.com> <7utpfuooqromk8krj1dd3nj0b73a8go8ga@4ax.com> <3cfd30be$0$8506$cc9e4d1f@news.dial.pipex.com> NNTP-Posting-Host: peters.mitre.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: newslocal.mitre.org 1023389952 3852 129.83.9.28 (6 Jun 2002 18:59:12 GMT) X-Complaints-To: news@mitre.org NNTP-Posting-Date: Thu, 6 Jun 2002 18:59:12 +0000 (UTC) X-Mailer: Mozilla 4.78 [en]C-20010724M (Windows NT 5.0; U) X-Accept-Language: en Xref: easynews comp.databases.oracle.server:149777 X-Received-Date: Thu, 06 Jun 2002 11:56:37 MST (news.easynews.com) 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 , "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