Re: Oracle versus MS Sql Server

In article <9reug4$3vt$05$1_at_news.t-online.com>, "Michael says...
>
>"Jim Kennedy" <kennedy-family_at_home.com> schrieb im Newsbeitrag
>news:xYBC7.1580$ib.998751_at_news1.sttln1.wa.home.com...
>
>> Databases differ from vendor to vendor. No they really do, they are not
>> commodities. How they handle concurrency (some handle it well some
>poorly)
>> , cursors (client side vs server side), and many many more. These are
>> application architecture issues. I agree with Galen, you are designing an
>> application to use the lowest common denominator and ignoring the dollars
>> the customer spent on their database.
>
>Interesting point. I do understand that.
>
>It is about the same as a software, which is written for many plattforms at
>the same time, without using the specific operating system's potentials - as
>for example ... Oracle Enterprise Manager?
>

Not quite the same as not using an OS's potential - very different indeed. It is the fact that queries run in a multi-user system (with concurrent updates and reads) will return totally different results in one system versus the other. Its not about using the systems to their potential (although you are missing on that, that is definitely for sure) -- its about getting totally different results from the systems running the same exact code.

Consider that MS does not have multi-versioning or read consistency. The default mode will read any committed data they hit and block when you hit locked data. Oracle does not do this. If you have a table with account balances for example and issue a simple "select sum(bal) from accounts" -- and you run the same exact inserts/updates/deletes against this table in both Oracle and MS - you will get totally different answers (and one of them will return an answer that never existed in the database at any point in time).

small example -- table has lots of rows. "row 1" has my savings balance in it of $100. "row 100,000" has my checking balance of $200. All intervening rows have balances of $0 (to make the math easy).

We will start a query that selects sum(bal). We will at about the same time move $100 from checking to savings. It is clear that the sum(bal) should always be $300 -- we didn't take money out or put any in.

Ok, here is the course of events then in MS using read committed:

time         action
--------     -----------------------
t1           our select sum(bal) reads row 1 ($100) and continues on
t2           another session updates row 100,000 and locks it.  row
             100,000 now has $100 in it.
t3           out select sum(bal) is still reading -- somewhere in the 
             middle
t4           that other session updates row 1 to $200 and locks it.
t5           we get to row 100,000 and get blocked..... we are waiting....
t6           that other session commits
t7           we read row 100,000 and report the answer:  $200

wait -- what happened to that other $100?? we lost it somewhere. Now, consider that there are hundreds of little transactions going on in this table -- your query is really getting blocks hundreds of times -- it is getting hundreds of incremental "errors" in the answer.

So, say you wanted the correct answer in MS. Then you would use a shared read lock on the data -- doing that we can ensure that no one can change data we've read -- it'll avoid the wrong answer but, using the sequence of events above, we would find that at step t4 - the other session gets blocked (they cannot update the row we read) and then at step t5 our query deadlocks with our update. One of them will fail -- tradeoff concurrency for correctness here. We can either query or update -- but not both.

In Oracle -- the sequence of events would look like this:

time         action
--------     -----------------------
t1           our select sum(bal) reads row 1 ($100) and continues on
t2           another session updates row 100,000 and locks it.  row
             100,000 now has $100 in it.
t3           out select sum(bal) is still reading -- somewhere in the 
             middle
t4           that other session updates row 1 to $200 and locks it.
t5           we get to row 100,000 and see that it was modified since our query
             began.  We do a read aside to the rollback segment, see the value
             was $200 when we began.  We report the answer "$300"

No blocking, no waiting, consistent answer with respect to the point in time we began.

I'm not going to belabor which approach is superior or better -- just that they are TOTALLY different, the applications function differently using the same code under the same conditions. It is that they are different that is the crux of the issue here.

Unless you write single user or very "non concurrent" applications, you will run into this (every conversion from sqlserver to oracle i've ever worked on has in many different ways -- the converse it true as well).

The concurrency models are so different that applications that behave correctly on one, fail on the other (if you are so inclined, I have a couple of case studies of this in my book -- for example, there is an interesting one where a trigger that worked great in SQLserver failed miserably in Oracle due to the fact that Oracle will not be blocked during a read and the trigger counted on reads getting blocked. without the blocking read -- the trigger made incorrect assumptions about the state of the data in the database and allowed a resource to be reserved by two people at the same time. With blocking reads -- it worked great. Without them -- it failed. Same code, same logic, same sql -- different results)....

And the list goes on and on and on. Use any database you want but make sure you

1. understand the heck out of it.
2. exploit it to death.

you're missing alot if you don't. I don't care what platform you use (but you might consider that 5 years ago, NT stood for "not there", Unix was dominant. 5 years before that, unix was an up and comer -- VAX was peaking in its popularity in the midsized computers. 5 years before that Mainframes were the serious computing environment. 5 years from now -- whats the OS platform going to be? I don't know but I'm pretty sure it'll be as different then as it is now from 5 years ago...)

>
>> My guess is you avoid using host variables. (which is going to
>> make scalability go down the tubes and no DBA can fix.)
>
>I do not quite understand, what made you guess this. Maybe it's my
>not-so-perfect English. Sorry.
>
>My programms are only targeted at the MS Windows plattform. So my way of
>talking to the database is through OLE/DB. I do not know a reason, why this
>might hinder me in accessing the database in a performing way. I do not know
>much about Oracle database administration, but you can be sure that I do
>know OLE/DB's capabilities and that I use all of them in the correct way.
>
>Michael G. Schneider
>
>

--
Thomas Kyte (tkyte@us.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