RE: Informix beats Oracle

-----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Data Cruncher Sent: 17 June 2007 10:33 PM To: informix-list@iiug.org Subject: Re: Informix beats Oracle >"DA Morgan" <damorgan@psoug.org> wrote in message >news:1182106348.366899@bubbleator.drizzle.com... >> 1. The kernel parameters were not set per Oracle's docs. >> >> 2. No source code for the test and as everyone knows a >> decent developer can manufacture any result they want >> by using the most or least efficient method of >> accomplishing a task. [snip] >To give a simple example, top command shows Oracle >processes right at top where as Informix oninit process >somewhere at the bottom, with db2 somewhere in >the middle. Sometimes I wonder whether Informix >is doing anything :-) How busy is Informix, compared to Oracle, on this machine ? How much work is being done by both - which system is bigger ? Dirk *** Disclaimer *** The information contained in this e-mail is confidential and legally privileged and is intended solely for the addressee and to others who have the authority to receive it. Access to this e-mail by anyone else is unauthorized and as such, any disclosure, copying, distribution or any action taken or omitted in reliance on it is unlawful. If you have received this e-mail in error, please notify the sender immediately. The views expressed in this e-mail are the views of the individual sender and should in no way be construed as the views of the Company. The Company is not liable to ensure that outgoing e-mails are virus-free. The Company is not liable, should information or data, for whatever reason, be corrupted or fail to reach its intended addressee. The Company is not liable for any loss or damage of whatsoever nature and howsoever arising resulting from the opening or the use of the information in this e-mail, including its attachments and links. The sender of this e-mail is subject to and bound by the terms and conditions of Company’s Electronic Communications Usage Policy.

Dirk Moolman wrote: > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] On Behalf Of Data Cruncher > Sent: 17 June 2007 10:33 PM > To: informix-list@iiug.org > Subject: Re: Informix beats Oracle > > >> "DA Morgan" <damorgan@psoug.org> wrote in message >> news:1182106348.366899@bubbleator.drizzle.com... >>> 1. The kernel parameters were not set per Oracle's docs. >>> >>> 2. No source code for the test and as everyone knows a >>> decent developer can manufacture any result they want >>> by using the most or least efficient method of >>> accomplishing a task. > > [snip] > >> To give a simple example, top command shows Oracle >> processes right at top where as Informix oninit process >> somewhere at the bottom, with db2 somewhere in >> the middle. Sometimes I wonder whether Informix >> is doing anything :-) > > > How busy is Informix, compared to Oracle, on this machine ? How much > work is being done by both - which system is bigger ? > > Dirk And what is the value of an idle CPU? Is there some kind of prize given for the most wasted cycles? I vote we all go back to coding in assembly language. Who will second the motion? "POP, JMP, LODSB, NOP, STI." That was both efficient and fun. <g> No CTO has ever purchased an RDBMS based on efficient use of CPU. Instead of following the bouncing ball how about asking IBM to make the same changes to Informix they just made to Viper 2 so it will support major business applications? -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

>From: DA Morgan <damorgan@psoug.org> > > How busy is Informix, compared to Oracle, on this machine ? How much > > work is being done by both - which system is bigger ? > > > > Dirk > >And what is the value of an idle CPU? Is there some kind of prize given >for the most wasted cycles? > > Seems to me that Da Maroon is yet again in reverse spin. Dude, you're a hardware vendor's wet dream. The point of having spare cycles is that the machine is now free to do other tasks and at the same time have the head room to grow as the company grows or the amount of data tossed at the application grows. _________________________________________________________________ Make every IM count. Download Messenger and join the i'm Initiative now. It's free. http://im.live.com/messenger/im/home/?source=TAGHM_June07

DA Morgan wrote: > No CTO has ever purchased an RDBMS based on efficient use of CPU. > Instead of following the bouncing ball how about asking IBM to make the > same changes to Informix they just made to Viper 2 so it will support > major business applications? Hmmm... nice stitching. IDS has traditionally watched Oracle closer than DB2 has, so many of the features you allude to are already present (like DECODE, UNIQUE, Oracle sequence syntax...). also IDS has "readers don't block writers, ..." which eliminated the perhaps single biggest stumbling block for porting Oracle apps. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Serge, Got me to want to actually ask you a question. :-) It's my understanding that all other DBMS products are pessimistic-locking, ORACLE is optimistic-locking, Oracle being only one in this category of mainstream DBMS products to have optimistic locking. Does this mean Informix has both pessimistic _and_ optimistic locking? Thanks, -t- Serge Rielau wrote: > DA Morgan wrote: >> No CTO has ever purchased an RDBMS based on efficient use of CPU. >> Instead of following the bouncing ball how about asking IBM to make the >> same changes to Informix they just made to Viper 2 so it will support >> major business applications? > Hmmm... nice stitching. IDS has traditionally watched Oracle closer than > DB2 has, so many of the features you allude to are already present (like > DECODE, UNIQUE, Oracle sequence syntax...). also IDS has "readers don't > block writers, ..." which eliminated the perhaps single biggest > stumbling block for porting Oracle apps. > > Cheers > Serge

On Mon, 2007-06-18 at 11:26 -0700, Tool wrote: > Does this mean Informix has both pessimistic _and_ optimistic locking? In Cheetah, yes: http://informix-technology.blogspot.com/2007/02/cheetah-spot-by-spot-last-committed.html -- Carsten Haese http://informixdb.sourceforge.net

Tool wrote: > Serge, > > Got me to want to actually ask you a question. :-) You are aware that this thread may suddenly turn educational now...? ;-) > > It's my understanding that all other DBMS products are > pessimistic-locking, ORACLE > is optimistic-locking, Oracle being only one in this category of > mainstream DBMS products > to have optimistic locking. > Does this mean Informix has both pessimistic _and_ optimistic locking? The language here is very confusing and I'm not sure whether there are true definitions. To me optimistic locking is an entirely different beast and Informix had support for it for years and DB2 Viper 2 is introducing it. Optimistic locking (as I understand it) is commonly used in three-tier architectures where the business transaction is much longer than the database transaction. Note that in that space all products are on an equal footing since no vendor can handle isolation across transactions. In fact I think SQL Server is the most aggressive user of optimistic locking for many years with deep support in .NET. Perhaps this is fueled by the default auto-commit behavior (??) Ticket purchase at an airline is a typical example for optimistic locking. You look for a seat going from A->B and you get a set of possibilities back. Having that information stashed away you check with your significant other if she agrees and eventually pick one of the option and hit "book". Two things can happen (actually make that three): 1. All is well you get the seat "confirmed!" 2. You get the seat but the price has changes "Confirmed?" 3. Oops, that seat is gone, try again. Why? There never was a reservation for any of the seats! IDS "natively" supports this sort of logic by supplying change timestamps. The app keeps these around and when update time comes around the change time stamp is compared to what's in the database. This approach is "optimistic" in the sense that it assumes that the stamps will match and you get that seat. Now, within a transaction a similar scheme can (but not must) be used in a multi version concurrency control situation. This would imply that a cursor for UPDATE does not acquire an intend-update lock and the UPDATE itself may fail if the row has changed between the FETCH and the UPDATE. To do this reliably a change time stamp must be available for each row in each table. To the best of my knowledge Oracle records these on a page level however (i.e. an undosegments have a page granularity, not a row granularity). But I'm sure Daniel or Mark T. would be more qualified to comment than I am. In IDS Cheetah READ COMMITTED is a variation on cursor stability and I do not think that it uses optimistic locking either. I'm sure Madison or Jonathan can confirm or dispel my assumption that an intent-update lock will be used since a very fast transaction could otherwise come in between the FETCH and UPDATE and COMMIT with no way of the cursor being notified. I can't say I'm an expert in locking and isolation level. But hey I tried :-) Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Thanks Serge. My training has been primarily non-Oracle, meaning that I've learned to program outside the database, whereas it's my impression that Oracle folks program applications with a high dependence on the database engine, thusly why I'm convinced that you can't compare most other database products with Oracle simply because it's a lot different than most. What I'm gathering is that it looks like Informix got a big boost in features, but to appreciate these features you really have to understand how a lot of applications work that are being used by the big database companies, i.e., SAP, etc. So for me until I see a benchmark comparison of applications and some specific task that is compared, I think basic I vs O comparisons are not realistic, and only superficial at best. Thanks for your insight... -t- Serge Rielau wrote: > Tool wrote: >> Serge, >> >> Got me to want to actually ask you a question. :-) > You are aware that this thread may suddenly turn educational now...? ;-) > >> >> It's my understanding that all other DBMS products are >> pessimistic-locking, ORACLE >> is optimistic-locking, Oracle being only one in this category of >> mainstream DBMS products >> to have optimistic locking. > > Does this mean Informix has both pessimistic _and_ optimistic locking? > The language here is very confusing and I'm not sure whether there are > true definitions. > To me optimistic locking is an entirely different beast and Informix had > support for it for years and DB2 Viper 2 is introducing it. > Optimistic locking (as I understand it) is commonly used in three-tier > architectures where the business transaction is much longer than the > database transaction. Note that in that space all products are on an > equal footing since no vendor can handle isolation across transactions. > In fact I think SQL Server is the most aggressive user of optimistic > locking for many years with deep support in .NET. Perhaps this is fueled > by the default auto-commit behavior (??) > > Ticket purchase at an airline is a typical example for optimistic locking. > You look for a seat going from A->B and you get a set of possibilities > back. Having that information stashed away you check with your > significant other if she agrees and eventually pick one of the option > and hit "book". Two things can happen (actually make that three): > 1. All is well you get the seat "confirmed!" > 2. You get the seat but the price has changes "Confirmed?" > 3. Oops, that seat is gone, try again. > Why? There never was a reservation for any of the seats! > > IDS "natively" supports this sort of logic by supplying change > timestamps. The app keeps these around and when update time comes around > the change time stamp is compared to what's in the database. > This approach is "optimistic" in the sense that it assumes that the > stamps will match and you get that seat. > > Now, within a transaction a similar scheme can (but not must) be used in > a multi version concurrency control situation. This would imply that a > cursor for UPDATE does not acquire an intend-update lock and the UPDATE > itself may fail if the row has changed between the FETCH and the UPDATE. > To do this reliably a change time stamp must be available for each row > in each table. To the best of my knowledge Oracle records these on a > page level however (i.e. an undosegments have a page granularity, not a > row granularity). But I'm sure Daniel or Mark T. would be more qualified > to comment than I am. > > In IDS Cheetah READ COMMITTED is a variation on cursor stability and I > do not think that it uses optimistic locking either. I'm sure Madison or > Jonathan can confirm or dispel my assumption that an intent-update lock > will be used since a very fast transaction could otherwise come in > between the FETCH and UPDATE and COMMIT with no way of the cursor being > notified. > > I can't say I'm an expert in locking and isolation level. But hey I > tried :-) > > Cheers > Serge >