Re: Oracle vs. Informix

A copy of this was sent to "Vladik" <reason_at_shadow.net> (if that email address didn't require changing) On Wed, 18 Aug 1999 20:26:19 -0400, you wrote:

>Regarding the page level locking -- yes,
>the number is a lot less (and that means
>not only space in RAM but time as well). To us it is very

It really very much depends on how you do locking though doesn't it.

Basically, transaction information (in effect LOCKS) is stored as an attribute of the data. Instead of storing locks externally from the data, typically implemented in RAM in other systems, we store the locks with the data -- on the block. We do NOT store locks in the manner Informix/Sybase and others do.

This allows for unlimited locking (no ram requirements -- locks exist on the block and the block may be in ram, then again it may not be).

this allows also for faster processing. Instead of going to a big list of locks and asking if "row 5 is locked" and then going to row 5, we just goto row 5 and discover its state. Typically, other systems implement a lock manager that mediates access to a 'lock table' and maintains the locking data structures. Lots of points of serialization in that process, extra overhead as well (goto lock table, goto data vs goto data).

this is one of the reasons row level locking in Oracle is not 'expensive' as others would have you believe. Its as fast as table level locks would be in other systems. The concept is -- goto the data and see if its locked, if it is and you need to lock it, you will wait, else you will lock it right there and then and be done with it.

>important because we update/insert at about
>3-4K records a second. And it is not only
>the locks on data but on indices as well,
>so if you lock a page that contains N rows
>and you have 3 indices on the that table,
>you probably saved 3xN locks.
>
>
>Regards,
>Vladislav
>
>
>Vincent GRENET <vincent.grenet_at_free.fr> wrote in message
>news:63ku3.47$on.286799_at_nnrp3.proxad.net...
>> Though I am not a DB guru, here is my opinion as a developper (and a
>little
>> administration).
>>
>> a) Yes, it is easier with Informix to have many databases in the same
>> server; although you can store unrelated tables in the same Oracle
>database,
>> if the needs for tuning (db_block_size...) are not different. You backup
>> tablespaces independently.
>>
>> b) Tuning is as hard with Informix as it is with Oracle. You just have
>less
>> documentation to read. Paralellizing queries is possible in Oracle with
>> Parallel Query Option (PQO).
>>
>> c) Why lock an entire page when you can lock a single row? Ok, the number
>of
>> locks can grow significantly. Just add RAM.
>>
>> d) Oracle is more friendly with the developper. The C precompiler is way
>> smarter (you don't have to declare sql variables in special blocks).
>PL/SQL
>> is more powerfull than Informix procedural (?) SQL.
>>
>> e) regarding performance, I really don't know; some people say Informix is
>> faster, others say Oracle is faster. I didn't have a way to make my
>> personnal opinion (yet; I actually am relatively new to Oracle). The only
>> feeling that is shared among all the people I know how had a chance to use
>> both is that Informix is more friendly to the dba.
>>
>> Vincent.
>>
>> Vladik <reason_at_shadow.net> a écrit dans le message :
>> rra2fmo9krl84_at_corp.supernews.com...
>> > I think that Informix architecture is more elegant, more thought of,
>> > more like well-designed from ground up compared to Oracle.
>> > But this is just my personal view. And I am just starting to understand
>> > more about Oracle -- most of my experience in db tuning/troubleshooting
>> > was with Informix.
>> >
>> > a) I think Informix is a lot easier to maintain especially for
>> organizations
>> > where people use many Database instances (like development shops).
>> > It is a lot lighter on resources. For example if I have 10
>databases --
>> > I do not need 10 separate database instances with their own data files,
>> > index files, temp files -- I rather allocate those spaces for a
>> > database server -- and all the databases on that database server
>> > can use the same db space same index same rollback. The same
>> > goes about memory allocation.
>> > So if out of those 10 databases only 4 are used actively, the
>space/memory
>> > resource is not wasted by the database server -- it is just being used
>as
>> > needed.
>> > In oracle, as I understand, if the other 6 databases are not used
>> actively,
>> > no one can use their data file space or index space, etc. So a DBA has
>> > to constantly be figuring out what DBs are not used and reduce the
>> resource
>> > allocation for them.
>> > We had to purchase more servers when we migrated from Informix to
>Oracle,
>> > just because Oracle asked for a lot more when it comes down to
>maintaining
>> > multiple databases (we have about 30 to 50).
>> >
>> > b) If you just look at the number of configuration switches for
>> Performance
>> > tuning -- Informix has a lot less. But the concepts behind those
>switches
>> > are very powerful, that is why I think that Informix is architecturally
>> more
>> > elegant and well thought off. For example, one of the most important
>> > things for OLTP applications is to be able to distribute the
>> databaseaccess
>> > across as many CPUs as possible and as many disk spindles as possible
>> > to achieve concurrency on the database server side. Well, Informix has
>> > an notion of Virtual CPUs and Virtual IO CPUs . You can attach (ping)
>> > a virtual CPU to a physical CPU of your DB server, and depending
>> > on I/O requirements you can configure number of I/O CPus.
>> > This virtual CPU concept allows (again in my view) to easier monitor
>> > performance of the server, and figure out load balancing issues
>> > (you can see what each virtual CPU is doing).
>> > Oracle on the other hand, has myriad switches per DB instance, per
>server,
>> > etc.
>> >
>> > d) Informix supports page locking, row locking, table locking concepts
>> > on per table level. It also supports the ability for locks to timeout
>in
>> > a given interval -- and this is configurable per each individual
>database
>> > connection! For our OLTP application it was very important as we,
>> > from the application, could identify what transaction take longer then
>> > needed because of lock waiting and do something about it.
>> >
>> > e) As far as stored procedures go -- I do not understand what
>> > you mean by one line at a time. Stored procedures are compiled
>> > by the database engine and cached the first time they are executed.
>> > And never recompiled again (until you explicitly ask for it). They are
>> > fast. In fact, once they are executed the first time -- you will not
>> > see the stored procedure text anymore in your SQL EXPLAIN --
>> > that is because Informix optimizer knows they are compiled
>> > and just calls the stored proc in binary form.
>> >
>> > d) even earlier version of Informix supported table partitioning
>> > and by now all the bugs are hopefully out of the system (I understand
>> > that table partitioning support has been available in Oracle 8).
>> >
>> >
>> > Oracle has more books written about it, I guess, a lot better marketing,
>> > and has more money to spend in investing in interesting technologies
>like
>> > XML. Also, they now have a few former Informix folks working for them.
>> >
>> > Vladislav,
>> > Just my biased opinions.
>> >
>> >
>> > Chris Moehring <chris.moehring_at_txucom.com> wrote in message
>> > news:37B42612.292BB35B_at_txucom.com...
>> > > I am considering purchasing Informix's IDS rather than Oracle's 8.
>> > >
>> > > I have heard that
>> > > 1) Informix was faster than Oracle but much harder to maintain.
>> > > 2) Harder to get support for Informix. People don't want to mess with
>> > > it.
>> > > 3) Stored procedures have to be done one line at a time with
>> > > Informix....very
>> > > time consuming.
>> > >
>> > > I am please with the speed but the last thing that we need right now
>is
>> > > server/software taking up our time to maintain.
>> > >
>> > > Can anyone address the validity of these statements?
>> > >
>> > > Thanks,
>> > > Chris
>> > >
>> >
>> >
>> >
>>
>>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 19 1999 - 08:59:16 CDT