Re: TPC-A and -B Benchmarks

In <1993Jul7.130922.10217@pyra.co.uk> graeme@uk.pyramid.com writes >In <21dk8rINNhjm@emory.mathcs.emory.edu> mrichardso@shl.com writes: >>In a recent append, gainer@almaden.ibm.com >>>In a recent append, alexk@infmuc.informix.com writes... >>>>To run TPC-C benchmarks a certain isolation level is required (as far as >>>>I heard). This isolation level is not yet supported by Oracle... >>>>Any comments on this?? >>>The TPC-C benchmark, as I was given to understand, runs 4 of its 5 >>>transactions using the Repeatable Read isolation level and the fifth >>>(the stocklevel transaction) using the Cursor Stability isolation level. >>>Repeatable Read implies row locks are held until a commit point is >>>encountered. I don't really know what Oracle supports as regards row >>>locking. Could someone from Oracle enlighten us? >>I know that Oracle 7 (and 6) supports repeatable read (which means one can [...] >>lack of published benchmark results! >>I believe cursor stability implies the maintenance of cursor positioning >>across commits and/or rollbacks. Note that in some cases this could violate >No, that is what is known as a HOLD CURSOR. CURSOR STABILITY means that >the current row of your cursor is stable while it remains the current >row - ie no-one else can update it while you are looking at it. >>ANSI standards. Oracle supports the ANSI mode and (I think) a more complete >>implementation of cursor stability. Note that I said "I believe" at the >More complete than what? More complete than ANSI HOLD CURSOR, of course. As you point out, I was describing HOLD CURSOR rather than the originally requested topic CURSOR STABILITY. As it turns out ANSI X3.135-1989 Level II requires that all explicitly declared cursors be closed upon commit or rollback. This is supported by the Oracle pre-compilers V1.5 when using MODE=ANSI. Otherwise Oracle supports the more flexible non-compliant capability of keeping all explicit cursors open which are not referred to by a CURRENT OF clause (the FOR UPDATE clause is no longer required). I have yet to examine ANSI SQL92 to see what it has to say on this topic. >The only way I can think of to implement >CURSOR STABILITY under Oracle (6 or 7) is to SELECT ... FOR UPDATE, but >this is overkill, as it does not release the lock until the commit, >whereas CURSOR STABILITY allows it to be released on the next FETCH. >>start of this paragraph and the relevant information is book knowledge, not >>practical experience. >>Regards, Marv >>mrichardso@shl.com >graeme Hope this clarifies what I was trying to say.....regards, Marv