Re: Does Informix do Versioning Read-Only Transactions?

In article: <3lb8n5$ecm@sword.eng.pyramid.com> graeme@pyramid.com (Graeme Sargent) writes: > > Tony Rothwell (Tony@santen.demon.co.uk) wrote: > : In article: <3l9l2j$7bm@sword.eng.pyramid.com> (Graeme Sargent) writes: > : > > : > In order to achieve Dirty Read type levels of performance ORACLE7 has to > : > resort to kludges like Discrete Transactions (which break Oracle's Read > : > Consistency Model). Most people consider this to be impractical in the > : > real world. As most of my work tends to be performance oriented, I tend > : > to lean towards favouring Dirty Read over Read Consistency (as do > : > Informix, presumably). I do not recall anything other than MRP II where > : > I saw Read-Consistency or Repeatable Read as a better tradeoff than > : > Cursor Stability, but then it is many years since I was an application > : > programmer! > > : Discrete transactions have (virtually) no place in real-world apps, > : and to my knowledge, no Oracle7 benchmarks use them (now) because of > : this (ie it's a dead issue). Do you not think dirty read is > : basically a similar kludge (that's a serious question; I'm not > : slamming you!)? What types of real-world application successfully > > I've not thought so, although it's an interesting viepoint. > > : use dirty read (once again, a serious question)? Does the > > I think most could use the technique. Many queries and reports are used > to gain an appreciation of the approximate state of the data (eg "Have > we made more or less profit/revenue/widgets than last week/month/day/ > year?", rather than "How much more down to the last cent/penny/unit?"). > Those that *do* need to get a consistent view could usually usefully be > update transactions (eg record the fact that it does/does not balance, > summarise or archive the result etc). As an application designer, the situations in which a query that only gave 'approximately' accurate results could be acceptable to me would be far outweighed by those in which it was wholly unacceptable. This obviously depends on the nature of the application, but I think you're being very optimistic about how acceptable dirty-read is. Do you label your reports that use dirty reads with warnings so that your users know not to put reliance on the detailed data they contain? Let's face it, dirty read is not something people want; it's something people *may* be prepared to put up with if the database they use is not capable of delivering consistent reads whilst maintaining high concurrency. Once you'd got used to not having to resort to dirty reads, you'd probably not be too happy about going back to them. I know I certainly wouldn't. > > : application code need to be more sophisticated if dirty reads are > : being used? > > I don't think the code does. Maybe the designers/developers do. It sounds like the users need to be more sophisticated (psychic maybe?), so that they can 'guess' which data on their screens and in their reports is accurate and which isn't. Are you seriously telling me that most of the Informix apps in production rely on dirty-reads? I can't believe that people don't find this a problem. It may just be a case of "what you've never had, you don't miss", but I'm a bit dubious. > > : Out of interest, when you were an application programmer, which > : database or databases did you use the most? > > Informix (pre-SQL). Doesn't it show? :-) I guessed so! Most people seem to be a bit biased towards the database they know best or use most. You can see where my 'loyalties' lie :) > > : On a different note, does Informix have anything equivalent to > : Oracle7's shared SQL pool? Does contention during parsing ever > > No. The shared SQL pool is a brilliant idea, although the > implementation could be a bit better. I've believe that Oracle 7.3 has got a redesigned library cache (shared SQL pool), but I don't know what changes this entails. > > : become a problem with Informix, particulary for SQL that's not > : in stored procedures? > > The onus is on the developer to use PREPARE intelligently. I've not > seen it become a problem, but I haven't been involved with running as > many users against Informix as I have with Oracle, and I still have > minimal experience of DSA. > > Even if it was the bottleneck in Informix, I'm not sure how I could > identify it as such. In Oracle a latch is held during parsing to prevent object definitions changing whilst the statement is being parsed. Contention on this latch is the bottleneck you can see. Shared SQL reduces the time this latch is held. It doesn't just remove/reduce bottlenecks, though, it also reduces the time taken (and CPU used) during parsing, so parse calls are faster and general database performance is improved as less resources are being used. I'd be interested to hear of Informix apps (particularly OLTP) with >1000 concurrent users, to see whether the volume of parse calls is a problem. > > Most of the Oracle apps I encounter still negate much of the benefit of > the Shared Pool by using constants where they should use bind variables. > Ha! Most of the Oracle apps I see (new ones) make excellent use of shared SQL. Pretty much all the tools that Oracle now ship that issue SQL or generate dynamic SQL use bind variables *everywhere*, so most people don't even need to think about whether the SQL in their apps is shareable: it always is. This wasn't the case before, admittedly, (and probably still isn't for many 3rd party products) and I reckon it was one of the reasons that some people who switched production apps from V6 to Oracle7 saw performance degrade initially. Cheers, Tony.