Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, A Reader.

Asked: April 23, 2002 - 3:06 pm UTC

Last updated: July 23, 2006 - 9:30 am UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

first of all thanks for this very informative site

Sorry if this is an out of line question but I am curious now…anyway...here it is

I had an interesting argument with my friend he works as a SQL Server DBA
We were talking about clustering and he said that this feature (real time clustering) in 9i is there in SQL Server from the beginning.
Also he pointed me to following page

</code> http://www.microsoft.com/sql/evaluation/compare/mythandreality.asp <code>

I know every company says there product is the best but what is the reality



and Tom said...

SQL Server has shared nothing clustering. we have and have had shared everything. With sql server, you put the data for department 10 on node 1, department 20 on the other node. If you need dept = 10 data, you goto node 1. need dept = 20, you goto node 2. With Oracle -- you goto the node you want, they all access any bit of data, no physical partitioning of data needed nor desired.

We have supported clustering since the late 80's -- a couple of years before windows nt was dreamt of, and many years before clustering was even supported by windows (we are talking windows 386 and windows 286 - how many people remember those "operating systems") and many years before sql server even existed.

My response to all MS tied in people is the same. A couple of years (not many) Unix was "king" and NT stood for not there. Before that VAX was pretty popular (brief run on Wang somewhere in there as well). Before that IBM ruled. Seems that every 5 years or so, the platform changes -- what remains constant is movement to a new platform. Every platform -- has had Oracle. So, when the platform changes (i predicate when, not if) to something else (don't know what -- linux, maybe, something totally different, maybe) where will those sqlserver databases be?

There is something to be said for portability -- Ubiquity is nice!




Rating

  (127 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what do you say about this?

a reader, April 23, 2002 - 3:47 pm UTC

Tom what do you say about this?
Oracle has reworked its Oracle Parallel Server feature and now calls it Real Application Clusters. While Oracle emphasizes the scalability this solution offers for clusters of computers, it does not provide any audited evidence that Real Application Clusters deliver scalability in such a configuration. At the same time, SQL Server 2000 holds the top TPC-C results with Distributed Partitioned Views–based cluster systems.


Tom Kyte
April 23, 2002 - 4:43 pm UTC

We run SAP, Peoplesoft and Oracle apps on clustered environments showing great scalability. Those are *real* applications (hence the name).

Anyone run SAP, peoplesoft on clustered sqlserver? No -- you cannot because they did not design their application to be physically partitioned like that.

Once upon a time ago, there was a benchmark -- tpc-d. they retired it we got so good at it. TPC's are interesting to a degree. Real world -- I don't think anyone will argue that (with a straight face).

Have you read the TPC-C spec? I have, zero percent chance I would design an application with their rules -- zero.



Thank You!

chad, April 23, 2002 - 4:54 pm UTC

Great reply Tom! Unfortunately, I work in a mixed environment where we have Oracle for the back-end and VB 6.0 for the front. Every day I go through the same crap with the "Microsoft Group" on how much better SQL Server 2000 is than anything Oracle has. I am very limited in my experience with SQL Server, so it's hard to debate things with them. Do you have any articles or comparisons that I could use to quite the mob. Thanks!

Tom Kyte
April 23, 2002 - 7:36 pm UTC

Sorry -- I don't even go there. For any piece of literature I would point you at, they would point you at some piece as well.

Databases have always been "religious" with people. I remember the same level of "support" for the predecesor to SQLServer -- Sybase. That didn't get them very far though.

A reader, April 23, 2002 - 5:24 pm UTC

"Anyone run SAP, peoplesoft on clustered sqlserver? No -- you cannot because they did not design their application to be physically partitioned like that."

Not entirely true: I have worked on SAP 4.6 on Clustered SQLServer on MS Cluster. I am not saying it is a big deal -just pointing out a fact. It was a shared nothing architecture strictly for high availability. When one node goes down another takes over. Both access data on a shared EMC array. No dynamic load balancing etc.. The application(SAP) needs to be aware of the clustering (so when one node is down it can redirect you). You have to follow SAP specifications to implement it. I think that is what Tom mentioned when he said Oracle can run real applications. The clustering is transparent there.

I fully agree that Oracle clustering is on another class itself. You cant compare it with SQL server clustering. No way!

RE: Not entirely true...

Mark A. Williams, April 23, 2002 - 9:17 pm UTC

"A Reader":

Your statement "Not entirely true..." is mixing apples and oranges, I believe. You seem to be describing a MSCS configuration, which is not the same as "Distributed Partitioned Views" in SQL Server. Now, feel free to correct me in regard to the "DPV", because I never work with SQL Server, but I do have experience (more than I want, truth be told) with Oracle on MSCS.

With 9iRAC, you can run against a clustered database with no changes to the application. As you pointed out, with MSCS, you have to configure SAP to be "aware". Again, I don't work with SAP, so I'm going by your statements.

- Mark

RE: Comparisons...

Mark A. Williams, April 23, 2002 - 11:46 pm UTC

Chad:

For some comparisons between Oracle and other databases, you may want to check out these links:

DB2:
</code> http://www.oracle.com/ip/deploy/database/oracle9i/collateral/AMR_oracle9i_wp.pdf

MS SQL Server:
http://www.oracle.com/ip/deploy/database/oracle9i/index.html?sqlserver.html <code>

There's not a ton of technical details, but you may find some of the info interesting...

Of course, all other databases are inferior by definition, right? :)

HTH,

Mark

9i and SQL Server 2000

Bob, April 24, 2002 - 1:31 am UTC

Thanks Tom, I too use this site A LOT. You're the best. Like some others, I am in a mixed shop and have worked extensively with Oracle 6, 7, 8i and now 9i, and during those many years I've worked with MSSQL from version 4.9 up thru 2000 (worked with Sybase 10, 11 & 12 as well). I have a lot of hind sight, and both DB's are blindingly fast now, so speed (with a little or lot of tuning) is a non-issue as far as I'm concerned. Both have their architectural strengths and weaknesses. The most important business concern for me is to build a DB app that's fast, scaleable, has a long life cycle and is within budget and time constraints. One other factor I measure by, I call it the "Headache factor", and it has to be as low as possible. My business conclusion after many years is solely based upon juggling time and resources to get the app in production so the business can be profitable. It doesn't matter how much Oracle or MSSQL senior talent I have on staff to do the job, SQL2000, hands down, has been the winner EVERY time. It's really not even close. And yes, databases is like religion here, but when the exec's look at the bottom line, the business tools have to work efficiently, and be cheap. "A man with an argument is never at the mercy of a man with an experience."

Tom Kyte
April 24, 2002 - 7:25 am UTC

I was agreeing with you to the very end -- I've walked into more then one situation where replacing ss2k was the way to go. I've worked with them all myself.

Oracle for Small Apps?

Ken, April 24, 2002 - 8:18 am UTC

Tom,

I'm a devoted member of the Church of Oracle, but I have to admit I have "experimented" with MSSQL. I have an application in mind that I want to develop and sell to small retail businesses, 3 to 5 users on the system at a time. I've been reading you book and am convinced that putting the app in the database is the way to go. I really want to use Oracle for tons of reasons ( %ROWTYPE by itself is enough when it comes to writing procedures) but it is so expensive. MS lets developers distribute the core sql engine in their apps for free provided they use Visual Studio or some other MS tool.

There is no doubt that Oracle is the only choice for big apps. Do they not want the small app business? Have they conceded that to MS?




Tom Kyte
April 24, 2002 - 8:47 am UTC

Oracle SE with small numbers of users is very inexpensive. You are looking at $1,500 USD for 5 named users. Thats less then I paid for my last desktop PC/monitor (last week).

I concede nothing personally.

A reader, April 24, 2002 - 10:05 am UTC

>RE: Not entirely true... April 23, 2002
>Reviewer: Mark A. Williams from Indianapolis, IN USA

>"A Reader":

>Your statement "Not entirely true..." is mixing apples and >oranges, I believe.
>You seem to be describing a MSCS....


I was not talking about the DPVs. Microsoft provides clustered SQL server solutions on MS cluster. And SAP supports their application (SAP R/3) on that platform. The architecture of MS cluster is share-nothing (which is different from that of Oracle parallel server/RAC). Tom said nobody run SAP on clustered SQL server and I was just mentioning that is not "entirely true". You can run SAP on clustered MS SQL indeed, but your SAP application has to be configured specifically for that.
I have worked on SQL server on MS cluster and OPS on Sun cluster. And I know that the OPS architecture is a class apart.

-A Reader.

RE: Not entirely true...

Mark A. Williams, April 24, 2002 - 10:33 am UTC

A Reader:

Thanks for the follow-up... as I said, I don't use SQL Server or SAP, so I was not sure if I was on track or not... but, now I know!

- Mark

Tianhua Wu, April 24, 2002 - 11:57 am UTC

Hi Tom,

I love your comment on NT! But I thougt NT means "not trustworthy"?

Minor Feature Set Differences can make a big difference

Jer Smith, April 24, 2002 - 1:29 pm UTC

Right now, I'm working on a project that's yet to make a final decision between SQL2000 and Oracle 9i. Some minor features can make huge differences. For instance, in our environment, we're finding that Oracle's XMLtype and more configurable partitioning (as opposed to making thousands of tables and using SQL Server's partitioned views) can give us a lot of value. Microsoft either isn't working on these, or won't admit they are. SQL Server does seem to perform faster on the quality of machine and OS that we're forced to use (Win2K, 2 CPU) but it's those two simple things that make Oracle appealing.

I think the world would really be served if someone had a web page explaining the real feature differences between the two products...ideally be an independent party.


OK people...Oracle or SQL Server ..which is easier to learn....

A reader, April 25, 2002 - 4:05 am UTC

Hi Tom and Everybody in this Database fight discussion...

I think sometimes all this fight comes just because people have lack of knowledge in the Features / Functionality provided by these DB Vendors...

I have some Oracle Knowledge , but i doubt if there are really people out there who know Oracle + MS Sql Server databases 100% ....so in fact one can not really argue/and/ or Compare the TWO, if one doesn't really have enough knowledge about BOTH the Databases ..



Why is there so much difference in Prices?

a reader, April 25, 2002 - 2:15 pm UTC

The reason I am asking is this will impact which database to use I personally want to use ORACLE but clients say if there is this much price difference then SQL Server---thats what they want

Number of CPUs Oracle9i
Enterprise Edition SQL Server 2000 Enterprise Edition With SQL Server
1 $40,000 $19,999
2 $80,000 $39,998
4 $160,000 $79,996
8 $320,000 $159,992
16 $640,000 $319,984
32 $1,280,000 $639,968

With OLAP or Data Mining

Number of CPUs Oracle9i
Enterprise Edition SQL Server 2000 Enterprise Edition With SQL Server
1 $60,000 $19,999
2 $120,000 $39,998
4 $240,000 $79,996
8 $480,000 $159,992
16 $960,000 $319,984
32 $1,920,000 $639,968

With OLAP and Data Mining

Number of CPUs Oracle9i
Enterprise Edition SQL Server 2000 Enterprise Edition With SQL Server
1 $80,000 $19,999
2 $160,000 $39,998
4 $320,000 $79,996
8 $640,000 $159,992
16 $1,280,000 $319,984
32 $2,560,000 $639,968

Oracle9i Standard Edition and SQL Server 2000 Standard Edition

Number of CPUs Oracle9i
Standard Edition SQL Server 2000
Standard Edition With SQL Server
1 $15,000 $4,999
2 $30,000 $9,998
4 $60,000 $19,996
8 $120,000 $39,992
16 $240,000 $79,984
32 $480,000 $159,968




Tom Kyte
April 25, 2002 - 6:39 pm UTC

I seriously question the SS prices and options -- where can I read that myself.

take a look at URLs

a reader, April 25, 2002 - 7:04 pm UTC

Tom Kyte
April 25, 2002 - 8:55 pm UTC

And exactly where do I find the equivalent of our "getting to know" type of guides that explains what they mean by OLAP and what they mean by data mining and what they have included in their EE? Not the one paragraph things (and bear in mind, I don't have days to research this -- something akin to the Oracle8i getting to know guide for example)

I know for a fact that the OLAP stuff we have in EE bests their OLAP offering and the OLAP stuff we have more more is stuff they don't even have in SQLServer really (are the cubes stored in SQLServer). Datamining -- I don't think they have exactly the same definition as we do (in fact, it is not even close).

So, before we can compare, we sort of need the "list" as well. I would have thought if they would compare the price, they would have defined some terms and shown perhaps how the products really stack up in functionality?

I really don't want to go tit-for-tat, but quick question for you -- are upgrades included with this? When SQLServer 13 comes out -- do you just "get that"? Is this license from them perpetual or does it expire at some point?

Also - have you ever seen a 32 processer SQLServer box (sort of funny to think about actually)....





32 Processor SQL Server...

Jer Smith, April 26, 2002 - 12:49 am UTC

Hadn't until a few months ago...

</code> http://www.unisys.com/hw/servers/es7000/overview.asp <code>

(It runs Linux and Oracle too, but due to a big marketing arrangement with Microsoft, they don't talk about it)

Oracle SE = SQL EE

Bala, April 26, 2002 - 8:49 am UTC

We should not be comparing the prices of oracle EE with
SQL EE.
The features you with Oracle SE are more than what you
get with SQL EE.





A reader, April 26, 2002 - 10:53 am UTC

Tom,

In your reply to the Original post, you said:

"With sql server, you put the data for department 10 on node 1, department 20 on the other node. If you need dept = 10 data, you goto node 1. need dept = 20, you goto node 2. With Oracle -- you goto the node you want, they all access any bit of data, no physical partitioning of data needed nor desired."

On all the clustered systems I have seen (Oracle and SQL Server), the data resided in an external disk array (and never on the node). All the nodes can access the data directly. I do not know if any cluster system will work without an external disk array (and keep the data in the internal disks of respective nodes -that sounds silly to me). So could you please explain what do you mean by physical partitioning in the above paragraph?

If the cluster uses a shared external array do you consider it as shared-everything cluster or shared-nothing cluster? Also I want to know if shared-everything and shared-nothing is a property of the clustering technology (like MSCS or IBM HACMP) lying beneath the application (such as OPS) or is it a property of the application ie OPS. Could you please clarify what is the difference between them(shared-nothing and everything)?

Thank you verymuch.

Tom Kyte
April 26, 2002 - 11:26 am UTC

SQLServer uses a shared nothing architecture, much like db2 on Unix (but UNLIKE db2 on the mainframe).

While all of the disks may be available to every node, they choose NOT to access the data from every node, they make you goto the node where the data is to get it. This way, they do not have to keep the caches consistent.

It also means that you have to design your application specifically to their architecture and heaven forbid if you need to re-allocate the data. Suppose "department 10" gets really big and you need to split it -- that's a physical rebuild. You are basically building a really large distributed database.

See
</code> http://dbforums.com/t340728.html <code>

for another description of this.

So, while we all use potentially shared storage -- they choose not to share it (like ibm on unix, unlike ibm on the mainframe). We choose to share it.

It is harder for us, easier for you.

Where do I get information about Shared Nothing and Shared Everything?

a reader, April 26, 2002 - 11:50 am UTC

tom could you give some good URLs for
Share nothing and share everything.

Thank You

Tom Kyte
April 26, 2002 - 8:22 pm UTC

Not that I know of personally -- no. Anyone else?

Where do I get information about Shared Nothing and Shared Everything?

a reader, April 26, 2002 - 11:50 am UTC

tom could you give some good URLs for
Share nothing and share everything.

Thank You

Careful with pricing

Jim Kennedy, April 26, 2002 - 12:37 pm UTC

One needs to also be careful with the MS pricing. It can be very complex because you may have to buy CALs (client access licenses) and they usually are not included in the price or are included with a base of 5.

Also there is more to a product in price. Had a reproducable problem that effected a large client that was caused in a 100% MS environment and could not get MS to step up to the plate to fix it. With Oracle I have recieved a special patch for a much smaller client to the DB in under 4 hours. (once we were able to rprodcue the problem)

A reader, April 26, 2002 - 12:47 pm UTC

Thanks Tom. The forum you referred to answered it very clearly. I knew that OPS provides failover and load balancing, while SQL server provides only failover. I have it confirmed now.

A question to the gentleman who mentioned about 32 nod SQL server? Do they have one node to access data and 31 redundant nodes waiting to takeover on a failure? Or they have 32 physical partitions with no failover. Either way this sounds funny. Not worth the effort.

Thanks again Tom, for clearing it up as day!

ashraf jamal

A reader, April 27, 2002 - 8:51 am UTC

All above is forget that:

- MS.sql server is mean taht windows server only
- MS.slq mean windows application only
- MS.sql mean lot of security miss
- MS.sql is microsoft...

SQL Server - Oracle comparison

Paul, May 03, 2002 - 8:54 am UTC

I've used Oracle8i and little of 9i, as well as SQL Server 7.0 and 2000. I'll try to give a reasonably unbiased comparison between the offerings. Note that this is mostly a high-level look at the systems. Since I have yet to use Oracle9i in great depth, some points may be neglected.

Firstly, it is important to consider the target audience of the two systems. I consider Microsoft's target audience to be primarily those who like an easy to set-up system that requires little initial tuning/configuration. Notice the limited install options and the way Microsoft designs most of it's products. SQL Server Enterprise Manager is also very basic and simple to use.
Oracle's target audience appears to be at a slightly higher level. Although it is possible to set up a basic database system relatively quickly, the wizards allow for a much more configurable system. The tools provided with Oracle8i also assume a higher level of technical know-how.

Consider a couple of points about SQL Server 2000:
No bitmap indexes!
Very basic Enterprise Manager(compared to 9i OEM)
Transact-SQL language less powerful than PL/SQL
Faster database than Oracle on similar hardware/OS
Less scalable
Limited platform support
Lower "HEADACHE factor" (I agree with Bob)

Notes on self-tuning:
Microsoft claims that SQL Server 2000 has self-tuning memory, file sizes, and I/O. Oracle9i apparently now supports these (I have yet to look into this more deeply). Great! But, as any experienced IT person will tell you computers are DUMB, they cannot adapt! For certain cases the self-tuning will work very well, but once you present the program with a scenario it doesn't know how to handle, things fall apart. Oracle, and some degree Microsoft, recognises this and thus allows the DBA to tune the database manually.

So in my final opinion it comes down to features and personal preference. If you like SQL Server 2000 and it supports what the business needs, then by all means use it. The same goes for Oracle9i.

About TPC-C

A reader, May 24, 2002 - 3:50 am UTC

Dear Tom,
Why should an application not be designed with TPC-C rules?
Thanks for a great site. I am waiting for your performace book.
Regards,


Tom Kyte
May 24, 2002 - 9:23 am UTC

what are "tpc-c" rules?

It's actually Windows vs. UNIX(es)

Randy, May 24, 2002 - 10:42 am UTC

First I'll say I was using SQLServer before I got into Oracle, so I have a certain respect for what SQLServer can do. But Microsoft products (all of them) don't force you to become as knowledgeable as you should to run them--they assume too much (often you'd rather HAVE to configure something before it'll work).

The problem most SQLServer people have with Oracle is that it *makes* you know the database. Just think of the SQLServer databases sitting out there that just got installed and never tuned after that. Oracle's just more configurable. I've administered both, and there are just *way* too many times I've wanted to do something that Oracle can do that SQLServer won't. Even though I sometimes think "geez, why do I have to do through all this?" with Oracle, it's worth it because I know why it's doing what it's doing because I told it to.

TPC rules

A reader, May 31, 2002 - 2:43 am UTC

Dear Tom,
Please refer your earlier comment on a followup to this question.
"Have you read the TPC-C spec? I have, zero percent chance I would design an application with their rules -- zero."
I wish to understand and appreciate this, please help me.
Regards,


Tom Kyte
May 31, 2002 - 7:12 am UTC

For example, they make it impossible to use a sequence to generate order id's -- they impose serialization around the order id primary key by using a table to generate them.

Own test

Harri, May 31, 2002 - 7:48 pm UTC

Some people thinks that MSSqlserver is faster than Oracle...

Why don't you test it by yourself:

Open two sqlserver's query analyzer sessions(or some another tool) and do this:

create table test (a number)
insert into test values (123)

begin transaction
update test set a=234

--hop to another session and do:
select * from test

...and put the timer on.

Do the same in Oracle and compare results - which one is faster?






Tom Kyte
May 31, 2002 - 9:08 pm UTC

excellent example. great way to show why they "autocommit" (transactions are not them processing)....



I remember this one cool issue too (it was sybase but basically the same thing)

isql | sed | isql

where the first isql was dumping some data -- sed was creating an update of that data -- updating the same row. The second isql was doing the update.

Since an update is/was generally a DELETE + INSERT -- this actually created an infinite loop. The update created more data at the END of the table, the first isql session -- when it got there -- read it and re-read that row. Read consistency is a great thing indeed.....

when I had processed over 50,000 updates against a 2,000 row table -- I killed it and spent the rest of the day trying to figure out "now why the heck would that happen like that"....


comparison of Oracle 8i and SQL Server2000

R.V.Reddy, June 12, 2002 - 2:14 pm UTC

Hi Tom

This article is very very interesting. since I am working on both databases I red MS online books it says:
SQL Server2000 is 200 times better than Oracle 8i in Performance basis ( cpu cost, elaspsed time ) even I tested with index and without index with SQL Server2000 and Oracle 8i(8.1.5) with around 2 lakhs of records. SQL Server2000 is taking less time.

But When I compare same thing with Orcle 9i and SQL Server2000, Oracle 9i is the best.

Can you compare what are all the best in Oracle 8i and SQL Server200 ( In fact I got interviewed for this question).

Thanks
Reddy

Tom Kyte
June 13, 2002 - 7:33 am UTC

The author of that book was smoking something funny.

You have my book -- you see when I say something, I show all factors involved, I show the proof, I show the hard numbers and I make it so you can reproduce it.

Did that author do the same or do they just say things.

200 times -- think about it -- we would need a 200 gHz (giga) cpu just to keep up with them? I think *not*.


I would take on sqlserver2k with Oracle7. In single user mode, I have seen them "best us" -- but -- how many single user systems do you run in the real world. Give me 10 or more users -- then see what happens. Things like non-blocking reads, unlimited row level locking, highly concurrent operations kill them.

Simple question -- if a user in one session updates a row in a transactional fashion (meaning you are actually using transactions -- something sqlserver abhores -- given their DEFAULT and primary mode is "no transactions for you") and a user in another session tries to select * from that table -- how many rows per second will that second user get? Answer: 0. Oh -- and another question to ask -- after that second user waits for some indeterminate period of time -- will they get the right answer (an answer that ACTUALLY existed in the database at some point in time?) -- Answer: most likely NOT.



Take a look how confident M$ is!

A reader, June 14, 2002 - 1:19 am UTC

Me personaly do not like M$ here is URI.

</code> http://www.theregister.co.uk/content/53/21003.html <code>

More Corporate Bull from MS

A reader, June 26, 2002 - 9:31 am UTC

IBM=Mainframe
MS=Windows

Oracle=Sum( Mainframe+Windows+Unix...etc..etc )

Doesn't look like a one product company to me.


I just had to jump in

Dean Reynolds, October 30, 2002 - 6:18 pm UTC

I search this site quite ofetn and when I saw this thread I had to jump in.

I have worked with Oracle from 7.3 and MSSQL from 6 (and every major version of the two to the latest versions).

I love Oracle and I want that bias noted.

On a MS server, MSSQL almost always (saw the note on 9i, haven't tested it) outperforms Oracle for a single user on a simple query (including joins, but not parallel query, etc.).

The reason is because of the architectures. I can't draw a graph in this response, but the basic idea is that MSSQL is user focused. I mean that in a very literal sense. First-come, first-served. Oracle certainly provides the same interest in users, but anticipates(better) that other users may be interested in this as well.

If you do some load testing (I have) just on a Windows box (very unfair to compare Windows performance to a good Linux build), you will see an interesting trend.

Even if it's not notiicable to end users, Oracle has a bigger resource footprint (overall) for a single user than does MSSQL. As you add users to your load testing something else interesting happens. The Oracle resource footprint (overall/per user)rises very gradually, whereas the MSSQL footprint sprints to a larger and larger footprint (overall/per user). Run a load test with 1, then 300, then 1000 users. Look at the resource costs(overall/per user). Oracle really shines when you look under the hood. It was built from the ground up as a multi-user database (also look at simple things like locking architecture over the last 3 versions of MSSQL to see how mutli-user they expected to be).

ALL MS products are designed for ease of use, period. Oracle has gotten easier and easier to use (it will never be easier for a part-time DBA/developer, DBA/ network admin, DBA/ receptionist), but does not sacrifice what it is just to make some tasks easier.

Talk to experienced DBA's about how easy it is to do real DBA tasks (managing the database proactively and dynamically) and you'll find it's much easier in Oracle than in MSSQL. For one thing, there's very little you CAN'T do in Oracle thru SQL/PL-SQL if you know what you're doing. Though MSSQL has opened more access points for DBA's in recent releases, you are still constrained to some degree by the GUI interfaces to the database. Oracle allows you to manage your database, not just configure it.

I've architected databases and data warehoues in Oracle and MSSQL. I can tell you it's a lot faster in MSSQL thanks to the many tools and GUI's provided, but is not nearly as flexible as Oracle in design options. Any VLDB on MSSQL is ugly, a large DW in MSSQL is very ugly.

Security is much better implemented in Oracle than in MSSQL.

You can take your Oracle database with you from platform to platform ( a big advantage in the Java shops I've worked with), with MSSQL you will only ever be able to use a MS OS. I would hate to be the one to convince some CIO that MS will seriously support Linux in the future.

Cost is a complicated issue. Cost in $$$ is always negotiable (even with Oracle) when you're talking about licenses (keep in mind, when you buy Oracle, you're done with Oracle, put it on Linux for free, when you buy MSSQL look for the additional costs (other licenses, etc). But cost is not only an issue at the time of purchase. If your business is based on delivering reliable data to clients (internal and external), what is the cost if that data is unavailable or, worse, inaccurate?

It is mythically cheaper to maintain a MSSQL staff than an Oracle staff. Not really true many times. It is a cheaper skill set, though.

The bottom line is, what do you need a database for (today and tomorrow)?

As a database consultant I am asked weekly "What's the best database?"

Depending on my mood, I say Access or MySQL. That's a stupid answer for a stupid question. Figure out what you want to do first.

quick and easy = MSSQL
quick and reliable = Oracle

Almost all of the developers who say MSSQL beats Oracle hands down are .NET certified and are trying to build a system that rewrites the database in VB or C# anyway. They also have a one-sided perspective of what a database is (usually a simple repository of data). Unfortunately, programmers (usually front-end ones)get put in charge of more projects than data architects.

:)

I agree

catherine, November 04, 2002 - 9:37 am UTC

I have to say "Dean Reynolds from Nashville, TN" talks a lot of sense. This is my view too!!


mssql newsgroup

Catherine., November 04, 2002 - 10:03 am UTC

If you want the equivelant mssql newsgroup argument - interesting read - search for "sql server and oracle" in the subject line in: </code> http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.sqlserver.server&SLCID=US&sd=GN&id=fh;en-gb;newsgroups <code>

9i versus CACHE 5

mo, January 03, 2003 - 10:00 pm UTC

Tom:

I just got the latest version of Software development magazine and one article claims that this database CACHE 5 is faster than oracle with large tables. have you ever benchmarked against it or aware of it?

<According to InterSystems, Cache 5 performs 300 times faster than Oracle 9i on a 10-million-row table.>

</code> http://www.intersystems.com/analysts/reviews/klas.html http://searchdatabase.techtarget.com/originalContent/0,289142,sid13_gci854569,00.html http://www.infoworld.com/articles/hn/xml/02/09/16/020916hncache.xml <code>





Tom Kyte
January 04, 2003 - 9:27 am UTC

so, umm, where are their industry standard, audited benchmarks -- vs gut feel cold calls?

I can never stop laughing at Microsoft !

Terry, January 04, 2003 - 10:33 am UTC

When I see this type of URL ( </code> http://www.microsoft.com/sql/evaluation/compare/mythandreality.asp <code> ) and I see the .asp at the end I just know I am moving to the "granny lane" of information processing. I have done so much Performance and Scalability Testing over the past several years, especially during the dot com crazy days on Oracle, DB2, SQL Server, Sysbase, ,Teradata, MySQL and others and in every single test scenario Microsoft everything gave up early. The Webserver, the database, the O/S it is all small scale period. I would choose Oracle Stored Procedures and Mod Plsql any day over any MS Com crap accessing SQL Server, and guess what Oracle web enablement works out of the box and has worked for many years. Nothing Web enabled works out of the box for Microsoft when it comes to the database.

When Microsoft says they have "Data Center" software that just means the price not the quality, not the scalability, not reliability. My most humorous moment with a "SQL Server Crew" was in Silicon Valley where a famous infrastructure provider firm was using a SQL Server development team to work on Oracle and they were trying to do a Distributed Query on Oracle. The humor was they had never heard of a database link and were re-inventing something in com/vb and all other forms of other MS Crap as these guys always do. I walked in and solved the problem in 1 minute or less. My point is not only do most of these guys not know the answer they don't even know the question. When life is viewed through a toilet paper roll you don't see much and that's pretty much how it is at Microsoft.

I Just can't stop laughing at Microsoft Further

Terry, January 04, 2003 - 11:07 am UTC

Further to the Microsoft Techie "dumbo show" in Silicon Valley referenced earlier. My second to most ridiculous escapade was catching a group of Microsoft SQL Server "Gurus" attempting to do relatively simple date math in Oracle but decided to write a C Program to accomplish their task. The task was simple, the date was represented in number as an offset of The Epoch in days plus the seconds of the day. Pretty simple task for an Oracle guy in Oracle, I did it in a single SQL statement (as any Oracle person would) but for Mr. Sql Server an emergency MS Tar and a conference call to Redmond, WA resulted in a brain fart C Program. Date math is strong in Oracle, not so hot in SQL Server apparently. That toilet paper roll is gettin' smaller and smaller.

I Just can't stop laughing at Microsoft Yet Again

A reader, January 04, 2003 - 11:55 am UTC

Tom,
You may even remember this one! I was contracting at a famous printer company out of Silicon Valley as an OAS Performance Tuner. This famous client (who recently merged with a Houston based Computer Manufacturing company) decided to go an a wild Microsoft "boon doggle". The internet site involved was handling between 600-1000 concurrent requests for 16 hours per day (pretty busy) average response time less than 1 second. The MS implementation (com object you know using Active Server Pages .asp) designed to replace it would not scale beyond 25 users and in fact reverse scaled when more mid-tier application servers were added. The site was retested many times by myself and by Mercury Interactive at huge costs to the client. Nothing changed except the development costs just kept going up with no visible return. These people tweaked the living daylights out of everything in sight. The solution actually performed worse when more mid-tiers and Webservers were added. The same content was tested using Apache, MODPLSQL out of the box on the same hardware and it scaled to well over 300 concurrent users with no loss of performance.

This was not about com or IIS vs Apache or Oracle vs MSSQL this was about building performance architectures and knowing what that performance means from network to database to schema. In Microsoft you waste your time "screwing around" and tweaking this and playing around all day in the registry looking for the holy grail as to why your application will not scale to the task at hand. On Oracle you build and deploy. That is not to say there are not issues on Oracle but these issues in my 15 years experience are solvable and that's mostly what this site and Tom's book is certainly all about.

cache 5

mo, January 04, 2003 - 1:42 pm UTC

Tom:

I assume you never heard or worked with it before?

Where do you find industry standard audit benchmark tests at? is there some organizations that do that?

Tom Kyte
January 04, 2003 - 2:09 pm UTC

Cache' the database formally known as MUMPS -- used widely in the healthcare industry -- and given that I work in Oracle GEH (government, education and healthcare) I've heard all about it. Many LEGACY systems are locked up tighter then a drum in it and they are looking to get out of it. Yes, I've heard of it.


www.tpc.org -- the Transaction Processing Council.

SQL SERVER cheaper NOT

Jim, January 04, 2003 - 2:20 pm UTC

Small SQL Server DB with maybe 50 concurrent users and it has to have a 4 CPU machine with 2 gigs of ram and a whole additional server just to run reports on the data. (same schema) The additional server was needed because we couldn't run users and reports on the same machine without contention. Of course, when it replicates (read only) the data daily it locks the tables so users can't even read the data while that table is being copied.

On another DB with Oracle we never have these problems and there are 300 concurrent users, people running reports and it is a SUN box with 2 CPUs and not a performance problem. Of course, readers don't lock writers or other readers. :-)

common....whats the bottom line

canucus, January 04, 2003 - 2:35 pm UTC

hey i am not a M$ fan but still ... most of you use M$ windows....dont you.....lot of companies have implimented M$ solutions very efficiently....
we should give them credit for that....and the bottom line is .... one should choose there tools as per there requirements and not otherway round.....
well thats what I think....
enjoy.....
tom's fan

Tom Kyte
January 04, 2003 - 2:50 pm UTC

but -- given that Oracle runs on windows -- what does that mean to the "what is common"...

what is common is that Oracle runs on all OS's and SS does not. And if you look at the history of OS's -- well, the OS du-jour changes about every 5 years. Check back in about 3 years and we'll see what's happening then. Me, I cannot wait (i personally feel I'm already on the next OS but I could be wrong -- might be something totally different, but as I sit here missing *nothing* from the windows environment and the guys I work with are running something different (mac attack -- they are coming back and you know what? they are really cool. I just cannot bring myself to buy a mac though for some reason ;)



mac attack

Stu Charlton, January 05, 2003 - 12:25 am UTC

I use a powerbook g4 running Oracle 9iR2 developer's edition... it's a good unix + I get the desktop apps if I ever need em. as good as or better than linux, imho. no regrets.


ANSI

Alexandre, January 16, 2003 - 11:50 am UTC

Tom,

Does ANSI tells anything about consistent read being or not ANSI compliance ???

Cause a friend of mine (DB2 DBA) insist that Oracle does not follow ANSI rules because consistent read is not ANSI compliance...

Is that true ???

Thanks,

Alexandre

Tom Kyte
January 16, 2003 - 12:09 pm UTC

Sure it does and yes it is.

First, ANSI has 4 definitions.
Second, we were the first database ever to

a) get sql89 nist certified
b) get sql92 nist certified

and seeing as how nist doesn't do it any more -- that'll be that. we passed the tests -- which must be run in isolation=serializable (i know, I was part of the team that did it for sql92 way back in 1993)

Anyway -- here is a short blurb from my book "Expert one on one Oracle" -- I spend quite a number of pages on this very topic:

<quote>
The ANSI/ISO SQL standard adds to the concurrency control mix as well. In this standard, they define four levels of transaction isolation with different possible outcomes for the same transaction mixes. That is, the same work performed in the same fashion with the same inputs, may result in different answers given your isolation level. These isolation levels are defined in terms of three 'phenomena' that are either permitted or not at a given level. These phenomena are:

o Dirty read - The meaning of this is as bad as it sounds. You are permitted to read uncommitted 'dirty' data. This is the effect you would achieve by just opening an OS file someone else is writing, and reading whatever data happened to be there. Data integrity is compromised, foreign keys violated, unique constraints ignored.

o Non-repeatable read - This simply means that if you read a row at time T1, and attempt to re-read that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.

o Phantom read - This means that if you executed a query at time T1, and re-execute it at time T2, additional rows may have been added to the database, which affects your results. This differs from the non-repeatable read in that in this case, data you already read has not been changed but rather that more data satisfies your query criteria than before.

SQL takes these three phenomena and creates four isolation levels based on the existence, or lack thereof, of the above phenomena. They are:

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted Permitted Permitted Permitted
Read Committed Permitted Permitted
Repeatable Read Permitted
Serializable

Oracle supports explicitly two of the above isolation levels as they are defined - read committed and serializable. This doesn't tell the whole story however. The SQL standard was attempting to set up isolation levels that would permit various degrees of consistency for queries performed in each level. Repeatable read is the isolation level they claim you must be at to get a read consistent result from a query. Read committed does not give you consistent results. Read uncommitted is the level to use to get non-blocking reads.

In Oracle, read committed has all of the attributes needed for read consistent queries. In other databases, read committed queries can and will return answers that never existed in the database at any point in time (db2 for examlple does that -- returns answers that never existed ever -- eg, it makes up answers). Moreover, Oracle supports the spirit of read uncommitted as well. The goal of providing a dirty read is to supply a non-blocking read; queries that are not blocked by and do not block, updates of the same data. Dirty reads are an implementation other databases must use in order to provide non-blocking reads. Dirty reads are not necessary in Oracle, it neither supports them, nor does it need them.

In addition to the four defined SQL isolation levels, Oracle provides another level - READ_ONLY. A read-only transaction is equivalent to a read-only repeatable read or serializable in SQL92. Updates by a read-only transaction are not permitted in this mode (other sessions may update data, but not the read-only transaction), but you get a repeatable read and serializable read, without phantoms.

.....
(lots more about these isolation levels and how to use Oracle with them chopped out)



ANSI

Alexandre, January 16, 2003 - 12:31 pm UTC

Thanks very much Tom,

Just great,

But, I could not even imagine how DB2 does this, as you said:

" db2 for examlple does
that -- returns answers that never existed ever -- eg, it makes up answers"...

Could you explain a bit more this...

Thanks again,

Alexandre

Tom Kyte
January 16, 2003 - 7:40 pm UTC

ok, say you have a 3 "page" or 3 block table. pretend there is 1 row / block. assume the values are:

ACCT BALANCE ACCT_TYPE
---- ------- ---------
1 500 savings
2 100 savings
1 1000 checking


Obviously -- there is 1600 in the bank there right? Assume the following operations and we are using the default DB2 isolation "read committed". What we need to do is report back "how much money is in the bank". At the same time -- we need to process transactions

time operation by query operation by update
------ ------------------------- -----------------------
t1 start query, read block 1
running total = 500, while
reading -- we have a shared
read lock on the block

t2 account 1 pulls up to ATM
and tries to take 400 from
checking (block 1) but has to
wait -- query has block 1 shared
locked and we need it exclusive

t3 query finished with block 1, released
shared lock, moved onto block 2, running
total = 600

t4 we get exclusive lock on block 1
and change the value to 100.

t5 we are actually doing a transfer,
so now we get block 3 and add
400 to it. block 3 has an
exclusive lock on it.

t6 give up block 2, try to move onto
block 3. we cannot - so we wait...

t7 we commit -- releasing block 3

t8 move onto block 3, read 1400 -- add
to 600 and report back the answer
"there be 2,000 in the bank!", hmmm



There are other interesting scenarios -- say you want a consistent read -- a correct answer. In db2 -- that is "repeatable read", they achieve that by LEAVING the shared locks on as they read it. So, it the above timeline, the query would have blocked the update until the query finished and committed. Given that block 2 really could represent a TON of blocks in the example, that could be a while (no atm transactions for you)... Or even better if the data were organized:

ACCT BALANCE ACCT_TYPE
---- ------- ---------
1 1000 checking
2 100 savings
1 500 savings


now what happens is:

query starts -- gets and keeps shared lock on block 1 (1000,checking). Update starts -- gets x-lock on block 3 (500,savings -- updated to 100,savings) and keeps it.

query keeps running....

update tries to get block 1 but cannot, query has it locked. update blocks.

query runs and gets to block 3, cannot get a shared lock.

We now have a deadlock. In order to get the right answer, we had to lock, but by locking -- we deadlock. catch 22 -- what do we do?


This is why db2 supports dirty read (forget about locks, what a waste of time right? who cares if you get the right answer, just blow through them).

Excerpt from a IBM redbook on dirty reads:


1.3.2.1 Case 2 - Without Locking

1. User A updates value 100 to 0 into the DB2 table column.
2. User B reads the updated value 0 and makes program decisions based on that value.
3. User A cancels the process and changes value 0 back to value 100 for the
DB2 table column.

This case shows that, without locking, User B made a wrong program decision, and because of that the business data in the database is potentially corrupted’


or repeatable reads:

3.3.1.1 Strategy 1 - Conversation Integrity (Not Recommended)
A commit point is created at the end of the conversation. The lock duration may include user think time. Therefore, this strategy should not be used in applications where other users need concurrent access to the tables.
…

Read-only application might get away with this strategy in some cases. S-locks are normally released before commit point, but an S-locked page or row cannot be updated by another process and an S-lock may have a very long duration. It may include user think time.

We do not recommend this strategy even for read-only applications, although it may be the most convenient one from a programming point of view.’



Oracle does not have the concept of a shared read lock.
Oracle does not need shared read locks to return a consistent result.
Oracle does not have a write block a read (queries are never blocked by updates)
An update is blocked only by an update of the same data (no lock escalation -- as db2 "provides" as a "feature")


It is -- in my opinion -- one of the largest differentiators between the databases -- their locking and concurrency models...





ANSI

Alexandre, January 17, 2003 - 7:25 am UTC

Just grate...

Tom,

I´ve just searched and did not find at google and ANSI site for a paper or offical document that describe isolation level just to prove to this friend of mine (DBA DB2)... cause even reading your answer he is telling that Oracle is not fully ANSI compliance, and you, as an Oracle member, probably has a biased opinion...

Could you point me a site that I could find this document to "rub in his face"...

Thanks a lot...

Tom Kyte
January 17, 2003 - 9:30 am UTC

you can buy it from ansi.

there are books on it -- mine is a pure cut and paste here.



Oooops

Alexandre, January 17, 2003 - 7:40 am UTC

Oooops...

Sorry Tom,

I´ve meant great, not grate...

By the way... when your next book is coming???



IBM not stupid

A reader, January 17, 2003 - 11:41 am UTC

Tom,

The engineers at IBM can't be that stupid (although the ones at M$ possibly are). Why did they choose to have a lock manager, rather than making the lock an attribute of the data?

Is there a downside to the way Oracle implemements row-level locks?

Tom Kyte
January 17, 2003 - 12:03 pm UTC

Well, don't really know what to think about this comment. All I've done, all I ever do here, is present facts. You make your own decision. It should be obvious from the url where my opinions when stated come from.

So, to the question at hand...

There is a hard way to do things, which is the path Oracle took.

Then there is the easy way to do things, which is the path IBM took.

It is not stupidity here "reader", it is "what is easier". This locking thing is the differentiator between databases. SQLServer -- used to be page based only -- why? because they were stupid? No, Sybase wasn't stupid -- they just wanted to be quick to market and you know what is easier -- it isn't row level locking, not when the database manages things at the page level. It took them a loooonnnnggggg time to get row level locking - and I would say they still didn't get it right.

Look at clustering technology -- there is an easy way for the databases to go -- it is called "shared nothing". Here you have to physically partition your database and affiliate a node in a cluster with some slice of data. IBM does this on unix with db2/udb for unix. It is impossible to use this software solution unless you specificially design your application 100% to its constraints. Eg: easy for IBM, hard for you

Then, there is the hard way. It is called shared everything. Here -- you need not physically partition the data -- all nodes in the cluster are peers of eachother. Your applications just run. IBM does this on Zeos (mainframe -- os/390). so tell me, why -- why two implementations of the same technology? are they "stupid" or did they have a time to market issue. One of the implementations is wrong isn't it? Or is it? You tell me.


With our locking and concurrency model -- do you trade off consistency (correct answers) for concurrency? no.

do you trade off performance (how many rows per second does db2 give you when it hits a locked row?) for concurrency? no.

do we force you to resort to dirty reads (which are as nasty as they sound) in order to get lots of queries going with lots of modifications? no.

do we escalate locks because they are a scarce resource? no -- never. In fact -- straight from the db2 guides:

...
If maxlocks is set too low, lock escalation happens when there is still enough lock space for other concurrent applications. If maxlocks is set too high, a few applications can consume most of the lock space, and other applications will have to perform lock escalation. The need for lock escalation in this case results in poor concurrency.
....

darned if you do, darned if you don't... (it would be interesting to read the Adminstration Guide: Performance document for db2 and check out the pages of information on all of the isolation levels, concurrency considerations, maxlocks, lock escalation and so forth. You won't find such documentation for Oracle since -- well, since you don't really think about these low level basic things that a database should take care of for you)


Take the facts.
Take the implementations.
Understand them.
Understand their implications.

Then and only then, draw your own personal conclusions.

Excellent answer

Srinivasan Sankar, January 17, 2003 - 2:52 pm UTC

Very crisp answer to a very complicated question. I'm in the process of a proposal to convert the DB environment from SQL to Oracle. This will be very helpful in my presentation to speak for Oracle

Here are some comparisons

Sikandar Hayat Awan, January 17, 2003 - 11:17 pm UTC

Nice discussion in this thread. Here are some white papers addressing comparison between Oracle and SQL-2000.

</code> http://technet.oracle.com/deploy/performance/content.html <code>

another link

Kevin, February 08, 2003 - 4:11 am UTC

Here is another discussion on Oracle vs MS SQL.
Stan Sorensen, director of SQL Server at Microsoft provides his views on the much debated topic.

</code> http://searchwin2000.techtarget.com/originalContent/0,289142,sid1_gci835654,00.html <code>

For me, its a never ending battle. Microsoft's strengths don't lie in building better products but in aggressive marketing, which perhaps Oracle doesn't do that well. Its my personal opinion and is not substantiated by any studies or data. But I feel Microsoft uses its desktop presence to the max. Most of the Servers would be in Unix but the decision makers (CEO,CIO,CFO) etc all use windows desktops. They tie up with each and every computer training institute. They tie up with different companies to use and market their products. e.g. very recently Bill Gates met CEO of Sheinsei Bank (Tokyo) and assured him of 24 hr onsite support, if they move to SQL Server (haven't heard Larry Ellison do so). Needless to say Bank did so and even force its subsidaries to do the same.(Remember what they did to Netscape, but since Oracle is no Netscape, I don't see SQL Server surpassing Oracle. But its presence definitely adds value to the competition and ultimately the end users/developers/dba etc are getting better and better products in shorter span of time.

A reader, May 27, 2003 - 3:01 pm UTC

---x---
Moreover, Oracle supports the spirit of read uncommitted as well. The goal of providing a dirty read is to supply a non-blocking read; queries that are not blocked by and do not block, updates of the same data. Dirty reads are an
implementation other databases must use in order to provide non-blocking reads. Dirty reads are not necessary in Oracle, it neither supports them, nor does it need them.
---x---

One of the goals of "dirty reads" could also be to avoid ORA-1555 errors!

What if I want a query, however long-running to always succeed without getting the dreaded ORA-1555?

i.e. I dont my query to ever go to rollback, just read the data (dirty or not) as of when the query gets to it.

In other words, isolation level = "read uncommited" in ANSI SQL. SQL Server supports this, any reason why Oracle doesnt?

Currently, I can never guarantee that a long-running query will NOT get ORA-1555. How can I give my users/developers this guarantee?



Tom Kyte
May 27, 2003 - 5:44 pm UTC

because a dirty read is exactly as bad as it sounds.

I remember using sqlserver.... (sybase but same concept, it was born of the same "really good stuff")

had one process that read data out, piped to another filter that turned it into an update, piped to another process that updated the row.

run against a 5,000 row table.
after it updates 25,000 rows in this 5,000 row table I killed it. anyone guess what happened?


sqlserver tends to process many updates as DELETE+INSERT. The insert went to the "bottom" of the table. Hence when I updated row 1 -- it added row 5001. It was a never ending cycle.

No, we will not ever not provide a consistent, correct read. Period.

You as a DBA can solve the ora-1555 "problem" by sizing your system as per the workload it performs. I can count the unexpected 1555's I've gotten (I've gotten many as I set up tests to show where/when/how they occur) on one hand.

Dirty read -- yeah, thats the ticket. You want dirty reads? Just use this query:

select dbms_random.random from all-objects;


it will apparently satisfy your needs entirely. there are apis to generate random strings as well.

I dont understand

A reader, May 27, 2003 - 11:21 pm UTC

I dont understand your attitude. Are you saying that ANSI SQL has the "read uncommited" concept for the heck of it? Just de-facto added to the standard because of something that SQL Server's(or Sybase or whatever) ability (or lack rather) to support consistent reads the way Oracle did?

Keeping your anecdote about how Sybase implements update=delete+insert and the resulting endless cycle aside, Oracle implements update the right way, correct? You still dont see the advantage, in some situations, that a dirty read would provide?

How can I size my system so that no SELECT, never, ever gets a ORA-1555? That would, in all likelihood make me size my RBS very high when in reality it would not be necessary 99.9% of the time.

That said, why doesnt Oracle implement the read-uncommited isolation level?

Many process, ad-hoc queries, etc, really dont care about read consistency, just get me the data as of the time it is read. If the block is commited, return it, otherwise read the block from rollback. Dont "freeze" the database as of the point in time my SELECT started. That is too drastic.

Tom Kyte
May 28, 2003 - 6:57 am UTC

they loosely define many levels of concurrency in terms of phenomena.

they are characterizations.

they are not even part of the standard really -- when they test, the only one they test for is serializable -- none of the other have been part of the test -- ever.

I see, will not be able to see, have not seen "dirty read" being good.

You know when I see it -- in informix/sybase/db2 implementations when they are trying to mimick Oracles non-block read. Why? cause without it -- their application doesn't even begin to run -- they cannot have concurrent reads and writes.

Tell me -- why would you need dirty read in sybase or MS to do a long running report? since they don't have 1555's (since they don't do that), what is the reasoning behind using a dirty read (beyond getting garbage, wrong, not useful answers?) does it save resources in their database (no). the only reason you are using it is because your system won't function without it. Your queries would either take years to run as they continously get blocked or they would deadlock with other updates (if you needed a consistent and correct answer and used shared read locks).




Not quite...

A reader, May 28, 2003 - 9:53 am UTC

---x--
Many process, ad-hoc queries, etc, really dont care about read consistency, just get me the data as of the time it is read. If the block is commited, return it, otherwise read the block from rollback. Dont "freeze" the database as of the point in time my SELECT started. That is too drastic.
---x---

you have still not addressed the above comment. Yes, I understand all that you are saying about "writers blocking readers" being bad. Reads should never be blocked, thats terrible design, I agree.

But at the same time, forcing me to size my rollbacks so as to accomodate even the longest running query is also not good.

My system already has enough RBS to support the largest transaction it has. But occasional queries still get 1555, because, I guess, they just happen to execute during periods of high db activity. But high db activity shouldnt be a reason for SELECTs to get 1555, right?

Bottom line: what do you recommend I do to eliminate 1555? Like I said, my rbs is already sized to handle the largest tranasction. Beyond this, what can I do?

Tom Kyte
May 28, 2003 - 6:53 pm UTC

I don't agree with you even a teeny bit.

I've not met the process that really wants to read the same row 15 times in a single query execution(more then possible in "read committed" and "dirty read" in sqlserver/db2/informix)

I've met more then one process that when confronted with this reality goes "oh, you mean that can really happen"


I don't have to address everything. I don't agree with your premise even a little.

We don't "force you" to do anything. We do things in a certain way. You either size the system properly -- or -- you don't. That is your choice.


what do I recommend? I recommend the only solution -- learn that RBS is not just about rolling back. Learn that RBS is an integral, vital, crucial, not to be treated as something to "minimize" part of Oracle. Learn that reading the same row 15 times in a select when it should be read once is something to be embarassed about, not something to demand in a product.




uh

Stu Charlton, May 28, 2003 - 3:24 pm UTC

"My system already has enough RBS to support the largest transaction it has"

and

" But occasional queries still get 1555, because, I guess, they just happen to execute during periods of high db activity"

are conflicting statements.

Size your RBS so that you don't get 1555's at peak loads. Why is this such a bad thing? Is disk space really that expensive? Perhaps your priorities are different, but data accuracy usual plays a more important role than the relatively minor amount of disk storage required for RBS. I wouldn't trade them in for a dirty read any day.

How large systems are running on SQLServer database...

Kamal Kishore, May 28, 2003 - 7:18 pm UTC

The mere thought that other databases FORCE you to do a dirty read in order to continue processing in reasonable amount of time, is Freighting.
As said by earlier poster, I would not sacrifice this for saving few extra MB of disk space.
But, It makes me wonder as to how these databases are running some of the high volume production systems without any performance issues?
Why is their user community not complaining about the slowness of (or, non-responsive) database?
How are they able to pull it off with such dreaded monsters (like the dirty read) embedded deep into their systems?
It makes me wonder???


Another Approach to the Question

A reader, May 28, 2003 - 10:08 pm UTC

The discussion so far as been around features and price for the two products (ORACLE and MS's Equivalent).

Coming from a finance background with an intermediate SQL understanding, my concern is obtaining data as quickly and as easily as possible.

How can I do this?

First, where do I find employees that know whatever DB you are deciding to go to? How much do they cost between the two systems? What's the availability of good people?

If the purpose of the database is provide timely information, then you need people who know how to use it.

Second, what is the expected development time between the two systems? Again, how is this going to get done?

Third, how much do I have to spend today, so I don't have to spend more tomorrow?

Finally, what if you are not able to upgrade as often as you would like, is this still a viable solution?

If an IT person were to come to me with a request to spend cash on a system, those are the questions I would want answered in as straightforward a manner as possible with as much supporting info that I could understand or be explained to me.





A reader, May 28, 2003 - 10:55 pm UTC

1. Read committed: committed here refers to data commited _before_ my SELECT statement started. Instead, if this definition is changed to include data that is commited as and when the SELECT statement processes it, whats the down-side? In other words, before the SELECT statement returns a data block/row, check if this data is commited or dirty (uncommited). If committed, fine. If not, get the corresponding data from rollback. Why wont this work?

2. ORA-1555: No transaction in my system ever gets 'unable to extend segment %s by %d in tablespace RBS' so I know my RBS is sized properly. I took the largest transaction, added 50% to it and created the RBS tablespace. Created as many segments as needed to accomodate my concurrent active transactions and everything is fine.

But how exactly do I size in a methodical fashion to avoid 1555? The problem, if I understand it right, is not one large transaction that is blowing my rbs, it is many small transactions! So, how can I size for this? Thanks.

Tom Kyte
May 29, 2003 - 7:40 am UTC

1) read committed in Oracle does what you describe.

read committed in sqlserver, db2, informix, et. al. means the "changed definition". read committed does not MEAN what you say it MEANS. ANSI describes isolation in terms of phenomena and transaction boundaries. NOT in terms of statements.

2) no, you are wrong. You have a fundemental misunderstanding of the database you are managing. You believe falsely that RBS is about WRITE transactions.

I'll say it simply "it is about reading and writing". If you get 1555 -- then BY DEFINITION YOUR RBS IS IMPROPERLY SIZED.

You are using the wrong metrics.



You size your rbs such that it takes longer to wrap around and reuse an extent then it does for your longest running query to execute. In 9i, this becomes somewhat simple with the undo retention period -- you don't size rbs, we do. you tell us how long to retain undo.

Forget about these comparisons

Get out while you can, June 24, 2003 - 11:22 am UTC

Let's just talk about how to get your data out of Microshaft, and into Oracle.

I have a program that generates and executes:

1) Oracle Create table syntax from SQL Server tables
2) MS's bcp commands to export all desired tables to flat files
3) Oracle SQL*Loader syntax to suck all that flat file data into the Oracle tables it just created.

: to get yourself out of MSSS2k, and into Oracle

There are a few prerequisits, and assumptions, but if you know what you're doing, you'll figure it out.

Tom - Do you mind if I post such a script, or would you rather I not?





Tom Kyte
June 25, 2003 - 10:50 am UTC

2 things --

o generic connectivity, you can just create table as select from ms.

o the oracle migration toolkit (on otn.oracle.com) does this

if you have a link -- to your scripts, thats great (but they would be too large for here)

Moving from Oracle to DB2

Ralesh Yougard, July 08, 2003 - 1:47 pm UTC

Dear Tom,

We are in the brink of migrating from Oracle to DB2 UDB. Oracle Corp just does not seems to serve our need as IBM does, in terms of service. We have taken a look at Migration Workbench, but there is not an option to go to BD2. You would you suggest ?

Your feedback will be very much appreciated.

Tom Kyte
July 08, 2003 - 1:57 pm UTC

you are kidding right?

If IBM is such a better servicer of the customer, as you seem to be suggesting they are, they would be the ones to provide a tool to migrate. I'm sure they'll be more then willing to send in an army of global services people to help you out.

So, where have we fallen down on service that you think IBM will better serve you?

This must be a joke

Wendy, July 08, 2003 - 4:24 pm UTC

I can't believe there is anyone in their right mind who would move FROM Oracle TO DB2.
If it is not a joke, your headaches are about to increase tenfold. The amount of work to manage a DB2 database alone is monumental.
Forget about OLTP and reporting in one database. The indexing strategy and optimization paths cause your database to 'lean' one way or the other (queries vs update/insert). You will lose the flexibility and forgiveness of the Oracle environment.
If you indeed make the switch, it will be interesting to hear how you feel about your decision 6 months from now.

ask the DB2 experts about dynamic sql

Jim, July 08, 2003 - 7:06 pm UTC

Maybe they have fixed this, but I doubt it. I worked with DB2 years ago on a mainframe and our application did dynamic sql (with bind variables). Dynamic sql to DB2 is sql that has not been bound to a plan before hand. This means that the optimizer in DB2 has to generate an explain plan each time (regardless of using bind variables or not). Since DB2 uses page level locks everyone using DB2 on the mainframe would serialize behind that user. They could not insert another explain plan - which is an entry in a table because that page was locked (due to the insert)They would be blocked from generating an explain plan until your session issued a commit statement. (thus forcing things to be auto commit) The poor Cobol programmers had fits because they could not compile their programs until someone issued a commit after a select.(any dml actually) It was a mess and did not scale well.

So ask the DB2 expert to describe in detail how dynamic SQL works in DB2 and make sure he/she understands that you do not want to run in autocommit mode.

Should Oracle provide the tools?

Kamal Kishore, July 08, 2003 - 8:14 pm UTC

I wonder if people expect Oracle to provide tools and software to be able to migrate from Oracle to other vendor databases?

I think if Oracle does provde such tools (not saying that it should), such customers will clearly see that how easy it was to be able to migrate (thanks to Oracle supplied tools) but then how hard and diffcult it became to manage the migrated database (thanks to other vendors database and support).

I think that would win such customers back.

I do not know if this qualifies as good humor, but...
:-)


To DB2

Ralesh Yougard, July 09, 2003 - 9:03 am UTC

Dear Tom, IBM teams have always been commited to our work in other projects, as well as we outsource them. Whereas Oracle is ok in general, IBM is always around helping us. I think the Oracle Database is very good, but IBM's support is much better. The question about OMW is only a personal curiosity. They can help us alright. We don't like Oracle support, in general so distant. You're right, IBM sends an amry if we need. Many thanks.


Tom Kyte
July 09, 2003 - 11:27 am UTC

well, IBM global services do Oracle development even more then DB2 development (meaning most of their projects are on systems that run Oracle).

If you would like oursourcing, we do that as well.

You are confusing things here. IBM global services are not *db2 support* anymore then Oracle consultants are Oracle support. You'll have consultants going through the same support chain for Db2 support.

Anyone will send you an Army if you send them money.

IBM will send you an army ( you bet )!!!

Alex, July 09, 2003 - 10:15 am UTC

Ralesh,

If we need IBM to be around you all the time what your IT(MIS or whatever you call it ) is doing ? Of course that IBM will send you people that's their biggest business. But if you guys think that you will need baby sitting all day, then just close the IT and outsource the whole thing to IBM . Oracle provides the best database technology( my opinion ) not just bunch of consultants, but somebody have to read the documentation coming with the database !

Regards,
Alex

DB2

Ralesh Yougard, July 09, 2003 - 1:28 pm UTC

Dear Mr. Tom,

If anyone can send in an army for money, then Oracle does not like money. Because we have tried to have people here and they just were not up to IBM's service level. So we made them not come back any more.

A reader, July 09, 2003 - 11:05 pm UTC

Mr Yogurt,

You seem to be from a "take-my-money-and-tell-me-a-story" type company. Oracle is probably not good for you, it for people who can understand and appreciate the differece. You are looking for a bunch of consultants who will tell you stories and put you to sleep. You certainly have made the right choice with IBM.

Goodluck!

9i and SQL Server 2000

Venky, July 15, 2003 - 8:57 pm UTC

In Oracle i know how to delete duplicate records.

Can anybody from SQL-Server show me how to delete duplicate records from a table using SQL statement. I tried so hard but with no success.


9i versus CACHE 5

Alex, July 25, 2003 - 5:51 pm UTC

<According to InterSystems, Cache 5 performs 300 times faster than Oracle 9i on a 10-million-row table.>

Are you sure it is 300x faster, not 299x, not 301x but exactly 300 ?

... sometimes I really wonder how people arrive at such figures.



SQL VERS 9I

ashraf, July 29, 2003 - 3:47 am UTC

WE should say SQL server hanging hanging slow
we should sqy oracle fast not hanging more opreating systems

Migrate to Oracle

xw, July 31, 2003 - 9:34 am UTC

Hi all,

For those interested in migrating to Oracle, the following url may help :

</code> http://otn.oracle.com/tech/migration/workbench/content.html <code>

You may also get helps from PTS (Partener Technical Services) which has a lot of migration experience.

best regards,





analytics in sql server

Asim Naveed, August 26, 2003 - 9:36 am UTC

I have read that analytic functions are standard SQL /99.

But i couldnt find analytic functions in SQL of SQL SERVER.
Am I right or analytics are not standard SQL.

Thanks

Tom Kyte
August 26, 2003 - 10:52 am UTC

there is no vendor, anywhere on this world, that is even nearly compliant with the monolithic thing that is called "sql" anymore.

The standard was feasible in days gone way by...

it is so large now that being compliant, well, I just don't see anyone doing it.

they (NIST) do not even run tests anymore like they used it.


analytics are part of the spec somewhere, haven't bothered to read them for years.

Paper Oracle Standard Edition vs SQL Server

A reader, March 30, 2004 - 8:53 am UTC

Hi Tom, I don't know if you could please helpme pointing to a paper, who compares sql server vs. oracle Standard Edition, specially showing sql server don't have hot backup, if you can please :)

A reader, March 30, 2004 - 10:32 am UTC

Mean while I found this really good one, thanks to Dennis Williams

</code> http://www.tcoug.org/Archive/Winter2004/SQL-9i.ppt <code>
almost 100 pages

Switch back from EE to SE?

Jelena, April 05, 2004 - 9:55 am UTC

Hi Tom,
can you switch between Enterprise and Standard edition? Is there any script/paramter/stg... or you need to reinstall oracle?
Thanks,
Jelena

Tom Kyte
April 05, 2004 - 10:01 am UTC

you'll want to contact support/metalink for the details but basically - you'll have to ensure you are not using anything EE oriented in your DB, then (after backing up everything of course) you'll reinstall the SE software.

My suggestion would be to get a play/test machine and restore the EE database there with the SE software to test - making sure you turned off anything that could cause a problem and then remove the software from the "real box", reinstall and restore.

but -- contact support, there are support notes outlining howto do this.

Decision Value

kamran, April 06, 2004 - 12:43 am UTC

I read that article (Microsoft comparing Oracle with SQL Server), it seems much emphasis on price. First of all do you think your decision value is less than the price of oralce-application, f so than no need to use(surely it is expensive then). A good oracle-users can bring you information from stored data in enormous directions which provides great help in decision making(businnes offcourse)

I had have used SQL Server and using Oracle now a days. Does SQL Server offers the following features:
analytical functions (e.g; lead, lag etc with extended options)[remarks: fast, do what sql can't or hard to do],
minus/intersect operators [remarks: clear, easy to code and easy to unserstand],
different index-types (function-based/hash,bitmap etc) [remarks: speedup your query-processing with your given hints (control)],
encrypted procedures [security/secracy],
inline views [ease in dealing with required result-sets, and a way to have "order by" views],
easy and clear syntax to join more than 2 tables [not an ANSI standard (oracle supports both its native syntax and in 9i, the ANSI syntax],
partioned tables, object-relational database support,support for xml, java, c, html etc.
and finally fast, efficient to use resources, excellent backup/recovery and fine tuning features.
you may disagree on some issues, and there are many more features I didn't discuss.

Tom Kyte
April 06, 2004 - 9:02 am UTC

(not only that but the entire price argument is *false* -- list price, we are basically "the same")....




9i and 10g vs SQL 2000

Martin, April 06, 2004 - 3:47 pm UTC

Hi Tom,

It's easy to have a tit-for-tat discussion about the benefits of Oracle over SQL*Server (and vice-versa, of course), believe me, I work in primarily a Microsoft shop, and trying to explain to even fellow developers why doing something in a single SQL statement using OLAP functions beats their "I need to use 287652 T-SQL temp tables, oh and don't SELECT too much from this table, other processes are trying to UPDATE" is a hard slog. Don't get me wrong, SQL*Server does have some great features, DTS and it's greater integration with Windows, does have appeal when dealing with Windows apps (and a lot of organisations do).

It is unfair to completely dismiss SQL*Server, it DOES have an element of XML support (no pun intended), although it is VERY basic, you can define "ORDER BY" views (although, there's no support for inline views), supports ANSI joins / CASE etc, and it does it's job for the smaller applications. I think Microsoft would be the first to admit, however unofficially, that SQL*Server just can't compete with Oracle on the larger scale stuff.

Ultimately, of course, it is down to the skills you have in your organisation. It'll take longer to develop an app sitting on Oracle than it will be to sit it on SQL*Server, if you have no Oracle people, and a lot of organisations simply are "Microsoft" (or IBM, or...) shops.

We all know that the features of Oracle which really "make the difference" to an apps scalability and performance, are down to the architectural design features which are used by applications which haven't been coded correctly, such as multi-versioning, allowing the app to function "correctly" even if the basic concurrency controls have not been followed (or imagined), and the absence of any "lock manager", which is still the bugbear of SQL*Server and DB2's design, one which Yukon (the next version) will HAVE to address if it's going to even attempt to compete with 9i and 10g.

I find the hardest thing, though, is going from Oracle to SQL*Server projects. Having to write T-SQL when just written PL/SQL code to do the same thing in about 1000th of the size is VERY frustrating!

Just my 0.02 pence (British, of course) ;-)

Clustering and 10g

Oleksandr Alesinskyy, April 06, 2004 - 5:26 pm UTC

does 10g still use mostly "share-all" clustering or "g" (grid)means moving toward to damned "share-nothing" clustering?

No offence, just curiosity.

Tom Kyte
April 07, 2004 - 8:52 am UTC

10g is and will be shared everything.

My $.02

Arun Mathur, April 08, 2004 - 1:33 pm UTC

Tom has already done a nice job at presenting his case as to why he (and plenty of others) prefer Oracle over other RDBMSs. For those arguing that MS SQLServer is the way to go because of "licensing costs" or whatever reason, here's an experience that I wanted to pass on:

I worked in a small company which used SQLServer. Correct me if I'm wrong, but when building stored procedures, SQLServer does NOT check for semantic errors, only syntax errors. Needless to say, this drove a lot of people nuts when testing an application. If this is no longer the case or I'm simply mistaken, I'd like to know about it. If this is the case and I'm correct, I would think twice about buying an engine that can't compile.

Regards,
Arun

Yup, MS SQL Server still just checks for syntax

Jim, May 13, 2004 - 2:57 pm UTC

Yes, in MS SQL Server 2000 stored procedures still just checks for syntax and does not check for errors. It doesn't make any sense to me.

Is Stan Kidding, or What?

RAF, October 27, 2004 - 7:18 am UTC

I read the article, mentioned in by Kevin ("another link" February 08, 2003, above).

In it, Stan Sorensen actually says:

"Our strategy is to provide highly manageable, scalable and performance databases that integrate with the operating system [customers] are using"

"highly scalable"? Not with escalating locks, my friend. "integrate with the operating system"? Yeah, but only if you've got Windoze.

In another part of the article, he says that M$ "wiped the slate clean" when they brought out SQL Server - madness!

I have to ask myself, does Stan actually use databases, or merely present sales pitch (in this case, that's pitch, as in tar - gloopy, concealing and rather dark).

Merits of SQL Server 2000 versus Oracle

Interested, November 07, 2004 - 8:18 am UTC

For some of us who still work with "older" web-based technologies such as idc/htx (admittedly a Microsoft-centric approach) but who need to connect to heterogeneous data environments including Oracle, MS SQL Server, Teradata, one of the benefits of MS SQL Server over Oracle has been the ability to execute multiple queries within a single internet data connector. For example:

+ delete from table where grass in ('green');
+ insert into table (select * from table2 where grass in
+ ('greener');
+ select * from table;

In MS SQL server, these query parts are executed in order and the query results are returned in the browser.

Oracle, on the other hand, has always seemed to have more difficulty supporting this sort of multi-part query approach (at least when attempting to execute this multi-part query from IIS within an idc). This seems to be the case even when adding commit statements between the delete and update query parts.

The first part of the query in the idc when run against Oracle seems to cause some sort of lock on the table and/or which does not seem to get released so that the next SQL statement within the idc can be executed.

I know that Oracle is now releasing a "new" product (that appears to largely resemble a Microsoft internet data connector approach) but the new technology also appears to require a separate Oracle webserver.

Has Oracle ever resolved these issues so that a multi-part query can be submitted from IIS via an idc?

If integrated heterogeneous environments are to be successful, my perspective would be that the two of you need to learn to play better together.



Tom Kyte
November 07, 2004 - 2:47 pm UTC

You are kidding right?

Or you haven't really ever logged into Oracle.

Oracle doesn't lock tables.
Oracle locks rows.

I'm not following you *at all* here, but everything you say above is definitely *wrong* transactionally speaking about Oracle -- all of it.

(but not really sure what an "idc" is or how it works but I can say that software written by MS for MS tends to work good on MS and that is about it).



In Oracle, you would


delete from table where grass in ('green');
insert into table select * from table2@some_other_database_even_sqlserver where grass in ('greener');
commit;


you don't need any fancy "idc" or anything like that -- it is called a distributed transaction, we transparently provide a 2 phase commit and heterogenous access.

We have for well over a dozen years (2pc, distributed transactions)
We have had the distributed query thing since version 5.


So not really sure what you did wrong, but you got something fundementally wrong here.


(we've been able to read and write a sqlserver database since before MS bought the technology from Sybase in 93' or 94')

troll alert !

Robert, November 07, 2004 - 7:17 pm UTC

>>Merits of SQL Server 2000 versus Oracle
>>Reviewer: Interested from Danville, CA
>>benefits of MS SQL Server over Oracle has been the ability to execute
>>multiple queries within a single internet data connector. For example:

Wow even this fine site is getting trolled.
musta missed the last bus on Holloween night....


Syntax vs. semantic?

A reader, November 07, 2004 - 8:21 pm UTC

"Yes, in MS SQL Server 2000 stored procedures still just checks for syntax and does not check for errors. It doesn't make any sense to me"

Um, can someone explain what this means? What does it mean to check for syntax errors but not semantic errors? Can someone explain in Oracle terms?

Tom Kyte
November 08, 2004 - 9:55 am UTC

believe they meant (believe they meant) "the sql parsed and was valid sql but if you try to run it -- it'll fail for some other reason. so syntactically, it was valid SQL, but we didn't have a privilege or something like that so it cannot run"

I would love to be corrected if I'm misinterpreting that.

whatever...

A reader, November 08, 2004 - 1:32 pm UTC

As far as I'm concerned, your response completely proves my point. Microsoft internet data connectors (idc's) have also been around for several years and it baffles me that you have no idea of what one is. You characterize MS as the "evil empire" in suggesting that "MS works well on MS...that's about it..."

Folks around these parts who know you're about to take-over PeopleSoft and eliminate many jobs in the east bay might characterize Oracle similarly...

To my earlier point, if either of you were interested in "getting along", then you would have at least half a notion of the fact that an idc is nothing more than a SQL script...not exactly rocket science (which is requested as though it is a webpage) that can be submitted by IIS to any ODBC datasource including Oracle. The idc uses an HTML presentation template (called an htx document) to present the query results in HTML.

To your point about never logging into Oracle, I actually create dozens of multi-million row Oracle tables on a daily basis and have created hundred million row tables in Teradata. I also regularly write heterogeneous queries between Oracle, MS SQL, and Teradata environments to construct multi-million row tables in all 3 environments.

I apologize for mis-wording the comment regarding the table lock...you're right of course, it is a record lock, but the result is the same, that is, a multi-part query works on MS SQL Server when executed by an idc, but not on Oracle.

I don't see any difference in the query logic you provided either versus what I provided to you.

I have also utilized pass-through queries (such as OPENQUERY and OPENROW).

I was really just looking for anyone who might have done something similar.

Also, if you really want to talk performance, why don't you compare Oracle 9i performance stats with Teradata against really large datasets? But then again, we both know that would be a pretty short discussion...

Thanks anyway...

Tom Kyte
November 08, 2004 - 5:35 pm UTC

Look -- I don't work on a proprietary system, thats all -- I work on lots of systems.

Ok -- Oracles connectivity has been around longer than MS has had a database -- why aren't you using that?

and if IDC were simply a "sql script" what you say is SO FAR AWAY from what Oracle is, what Oracle does, how Oracle would behave locking wise -- that the ONLY conclusion anyone can come to is "it ain't a simple sql script". MS SQL server is "infamous" for

a) not using multi-statement transactions (eg: each statment is a transaction -- you don't commit in sqlserver unless you say "begin tran"

b) using a connection PER statement

which in an Oracle environment( db2, informix, you name it...) is death, deadly, horrible

So, I'm guessing -- cause what you say about how oracle locks is patently and provably FALSE -- just go into sqlplus, you can prove that in 5 seconds -- is that this isn't some simple script, it is the MS way of approaching the problem, which is quite different from, well, anyone else (except Sybase of course)


As for Oracle vs Teradata -- you are correct, given a person who knows Oracle and a person who knows teradata -- well, you are right - there isn't a question in MY mind what would happen. Been there, done that.


But if you understand Oracles concurrency model, you would understand what you have said about the locking is fundementally *flawed*, it quite simply doesn't work that way *at all*.


Pricing - truth in MS advertising

Jim, November 08, 2004 - 1:32 pm UTC

Tom

Let me start off by saying that I actively support both RDBMS's. I started out on Oracle 7.3.4 (on OpenVMS), so I do have an Oracle preference. But, I can speak with some experience on both.

Let me give you a real world example on why Oracle is actually cheaper from a pure license perspective.

I am currently supporting the creation of a custom ERP system (why custom? - it's a very small industry that is not well supported by the ERP vendors). We are creating a 500GB database for about 500 concurrent users.

Here is the rough outline of the architecture that is required to get this up and running for performance and reliablity on SQL 2K with licensing requirements.

8 CPU SQL EE (Active/Passive Cluster) for OLTP
2 CPU SQL EE (Active/Passive Cluster) to manage Transaction Replication
4 CPU SQL EE (Single node) for management reporting

Reporting MUST be separated from the OLTP because of SQL's locking strategy. All agregation type queries lock and block the OLTP transactions so much that it brings the server OLTP to its knees.

If we had built the same solution on Oracle, we could EASILY have performed the same workload on a 8 CPU RAC (2 node) cluster. So, we need to purchase 14 SQL licenses vs. 8 Oracle licenses.

Remember, Microsoft has built their business model based on creating a scaled out architecture. So, when you weigh the costs of the additional hardware, and the redundant storage of the reporting server, the costs are very close to being equal. Plus, the 8 procs is the hard that is allowed for Win2K advanced Server. So, in order to scale this up further, we would need to completely switch the OS to datacenter and 64 bit.

Question to author of discussion reply entitled "Whatever..."

Arun Mathur, November 08, 2004 - 4:29 pm UTC

You wrote:

"I apologize for mis-wording the comment regarding the table lock...you're right
of course, it is a record lock, but the result is the same, that is, a
multi-part query works on MS SQL Server when executed by an idc, but not on
Oracle."

Can you elaborate on this and provide some reports to support your argument?

Thanks,
Arun




Tom Kyte
November 08, 2004 - 5:46 pm UTC

If you execute this:

+ delete from table where grass in ('green');
+ insert into table (select * from table2 where grass in
+ ('greener');
+ select * from table;

in a single session in Oracle, what they claim to have observed - well, it doesn't work that way.

5 seconds in sqlplus would show that.


IDC is most likely using the sqlserver way of things - separate sessions for separate statements, there is a unique index on "table", the insert it trying to insert rows the first (uncommitted) delete deleted and gets blocked by that.

again, it would be IDC, not the database doing this. I see it time and time and time again -- because

a) sqlserver is pretty much "afraid of locks" and commits after each statement by default

b) they love to use as many connection as you can think about creating


both of which are not "a good idea(Tm)" in pretty much every other database (with the exception of Sybase)

interesting thread...

Menon, November 08, 2004 - 6:31 pm UTC

"As for Oracle vs Teradata -- you are correct, given a person who knows Oracle
and a person who knows teradata -- well, you are right - there isn't a question
in MY mind what would happen. Been there, done that."

Tom
do you mean to agree with the reader who asked this
question about teradata being better than oracle here?


Tom Kyte
November 08, 2004 - 9:29 pm UTC

umm, no.

there isn't a question in MY mind what would happen.

they left it open:

<quote>
Also, if you really want to talk performance, why don't you compare Oracle 9i
performance stats with Teradata against really large datasets? But then again,
we both know that would be a pretty short discussion...
</quote>

as did I in my response.

IF person using software knows software
THEN
results are good
ELSE
results are not as good
END IF

there is NO QUESTION in my mind what would happen...

If you take someone that has a cursory knowledge of both -- who *knows* what would happen.

I do benchmarking with customers. If you have someone who actually knows the product using it, you'd be amazed what you can actually accomplish with said product (that is true for Oracle as well as everything else)....



agreed

Menon, November 08, 2004 - 10:59 pm UTC


Interesting....

Arun Mathur, November 09, 2004 - 10:24 am UTC

(Going back to the "whatever..." discussion) I ran a simulation of the delete/insert scenario you presented, and I get the same behavior you predicted. Out of curiosity, could this mean that IDC is trying to follow SQL*Server's database architecture while simultaneously trying to be "database independent"?

Thanks,
Arun


Tom Kyte
November 09, 2004 - 11:11 am UTC

Do this, get it stuck again and issue:

select (select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/

and see if you don't have more than one session.....



SQL*Plus output

Arun Mathur, November 09, 2004 - 11:51 am UTC

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 X                                         NOT NULL NUMBER

SQL> select count(*) from t;

  COUNT(*)
----------
      1000

SQL> delete from t where x=1;

1 row deleted.

** another session

SQL> insert into t(x) values (1);

***I'm blocked here.



SQL> @track_blockers.sql

BLOCKER                               SID 'ISBLOCKING'
------------------------------ ---------- -------------
BLOCKEE                               SID
------------------------------ ----------
AMATHUR                                 9  is blocking
AMATHUR                                11

SQL> l
  1  select
  2  (select username from v$session where sid=a.sid) blocker,
  3             a.sid,
  4            ' is blocking ',
  5             (select username from v$session where sid=b.sid) blockee,
  6                 b.sid
  7        from v$lock a, v$lock b
  8       where a.block = 1
  9         and b.request > 0
 10         and a.id1 = b.id1
 11*       and a.id2 = b.id2

Makes sense. Is this what you're looking for?

Thanks,
Arun
 

Tom Kyte
November 09, 2004 - 12:17 pm UTC

no, i want to see the IDC stuff using two sessions and see you blocking yourself.

FYI

Arun Mathur, November 09, 2004 - 12:10 pm UTC

After re-reading my post on simulating the insert/delete operation, I can see how someone could read it and think that I used IDC to simulate it. I used SQL*Plus to simulate the scenario. But, I do want to apologize to Tom and the other readers if I misled anyone.

Thanks,
Arun


Tom Kyte
November 09, 2004 - 12:18 pm UTC

ahh , i see -- you are not using IDC....



sounds aweful

A reader, November 11, 2004 - 10:57 am UTC

hi tom,

i am working on oracle only but as you told it before databases are somewhat like a religon and there are always discussions in progress about system opportunities. that's why i think it might be also very helpful for people working on oracle to know how the others do.

one year before, our manager invited ibm to present db2's possibilities for datawarehousing. ibm people arrived telling us something about db2's _great_ features. i: what's about partitioning? ibm: hash partitioning i: how will you implement a rolling window? ibm: our hash partitioning algorithm is very effecive and there are algorithms for automatic redistribution ... i couldn't stop laughing because they really told me that they will bring propably millions of rows out of this hash partitioned table by a DML transaction - but a very, very effective one *LOL* (of course, what other should they tell if there is just hash partitioning in db2?)

tom, a few questions:
1) does db2 also use autocommit on every statement until you tell them "begin transaction"?

2) what happens during a consistent read in db2 when someone other updated a row meanwhile that is required by the rolling consistend read in following?

3) what happens if there is an large update running in default isolation mode (which is "read commited" i read) and rolled back in following. what does this mean to concurrent selects and updates?

4) is there an equivalent to "select for update" in db2?

5) i heared something about "database partitioning" in db2? that is somewhat like defining a special tablespace and every table within this tablespace will be distributed between two nodes based on a hashing algorithm? what's that?

regards,
max






Tom Kyte
November 11, 2004 - 11:04 am UTC

1) db2 starts a transaction like Oracle does....

2) well, in DB2 to get a consistent read -- you have to use shared (or above) read locks -- meaning once you read the data, you leave it locked (prevent updates) until you commit.

So, in DB2 -- if you read the data -- no one can update it until you say "commit" or "rollback".

In DB2 if someone has updated the data -- you have to wait for them to commit or rollback to read it.

3) there will be no concurrent selects and updates? no one is reading or writing the data you are writing.

4) yes.

5) that is to implement their "share no disk" approach to clustering. You have to figure out the best (and only) way to physically partition your data over N-Nodes in a cluster (to avoid shipping of data from node to node -- if you run a query on "node-1" and it needs data that only node-2 can access, you'll be shipping data over the interconnect like mad -- many DB2 clustered implementations also implement REPLICATION of the data over the nodes to avoid this. And heaven help you if you add or remove a node from the cluster, can you spell "rebuild database")

funny db2

A reader, November 12, 2004 - 6:06 am UTC

seems to be very funny to be on db2 :)

ad 2)
you gave an example of default isolation "read commited" to "alexandre of brazil" explaining your statement "db2 for example does that -- returns answers that never existed ever -- eg, it makes up answers". you used a select first but isn't this also working for an update taking first place?

ad 5)
is data distributed automatically in such a "partitoned database" when loaded in any case?

thanks,
max




Tom Kyte
November 12, 2004 - 7:14 am UTC

ad 2) there are an infinite combinations in db2's default mode of operation (read committed) to get answers that never existed.


ad 5) hope not -- the "system" doesn't know what questions you will be asking. if it partitioned for you, it would be less than desirable.

Microsoft have "seen the light"

Jon, November 12, 2004 - 6:48 pm UTC

I've been running a few courses introducing our many SQL Server developers to the joys and efficiencies of Oracle's concurrency and locking model.

During my research, I came across this:

</code> http://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx <code>

SQL Server 2005 is introducting multi-versioning:

"Is it possible to return statement-level or transaction-level read consistent data while a system is actively processing? Can you write a long-running query in a production environment, ask for consistency and not block writers? In SQL Server 2005, the ability to offer this to your users can be done through an optional database-level setting which automatically changes the behavior of READ COMMMITTED; this offers non-blocking, non-locking, statement-level read-consistency."

Read the article - it's a goodie. It amazes me how Microsoft marketing can still manage to portray it's shared/read lock architecture as a virtue, slag of Oracle, and then say, "by the way, we are now going to do the same thing as Oracle have been doing for years".

It has some classic quotes:

"With SQL Server 2005 and the introduction of optimistic concurrency control with Snapshot Isolation it is now much easier for a type 1 application vendor to make a direct port to SQL Server and extend their market beyond the confines of the Oracle/Unix platform"

and MSs description of Oracle's rollback segments and related configuration seems disingenious to say the least.

What does seem to have been glossed over, though, is the concept of undo (or version store) retention:

"Version records need to be kept in the version store only as long as there are snapshot queries that might be interested in them; this length of time depends primarily on whether or not the snapshot is statement-based or transaction-based."

Unless I've read it wrong, they don't seem to explain how they plan to retain versions for long running queries that started before a separate DML transaction that updates rows the query may not have yet visited.

Nevertheless, it will be very interesting to see how well Micorsoft implement this. If it works, it may well give them the ability to push into the larger end markets.

Could be a very interesting database market in 3-4 years time... I hope Oracle are watching their back :)

Tom Kyte
November 12, 2004 - 8:58 pm UTC

we are always advancing.

It is funny how for sooooooo many years they have been saying "you don't need that feature" -- I might even have to dig up old articles where they exposed the evilness of that feature of Oracle -- saying it was a design flaw in our database and a "bad thing (tm)".


but remember -- they will always be the fact that they run on a single OS. One.

Oracle vs SQL Server

Tarry Singh, November 13, 2004 - 7:47 am UTC

I've been away from oracle for almost 1.5 yrs(God how have i survived that?), wheni got a job as a SQL Server DBA.

We run several databases on sql server, including a core application which is almost a 24x7 db. And it's beginning to grow like mad...

So talk about...
--multiversioning(the whole market is still on sql 2000, and adopting migrating is still going to take 2 more yeears at the least, so what are you going to do, wait??)
--db load balancing(sql clustering is nothing but a active/passive failover scenario, never heard anyone being happy, forget about advocating doing an active/active cluster)
--file system? man I've seen databases(irrespective of size being parked in one huge *.mdf file)
--partitioning--I have one table which grows massively, a logging table, I'd have loved to partition it, but honestly I'm skepticle.
--and there are many more issues but bottom line, you want a database which you can real tweak, monitor, slice,dice etc etc you're choice will have to be oracle, lest you have a very good friend in M$

I'm doing a research and will be proposing an migration to oracle/linux or solaris,because enterprice aplications can't afford the locks, latches, etc etc.



Is Oracle a Legacy Technology?

A reader, November 17, 2004 - 9:55 pm UTC

Would love to hear your feedback on

</code> http://www.rittman.net/archives/001130.html <code>

Thanks

Tom Kyte
November 18, 2004 - 10:23 am UTC

well, before even reading it, every shipping piece of software out there is by definition a legacy piece of software.

1 second after cutting the ribbon on a new system, it is a legacy system.


and what exactly is linux doing to the OS market? have you priced out what it takes to implement and deploy say RH Linux? it is another OS, with a price tag (non trivial price tag one might add). Supporting software in a production "we cannot be down" environment costs $$$. Ebay isn't going to rely on the good will of George in Australia when their system has a "burp"


I claim Java a legacy, J2ee as well, and .nyet of course.

How many programming paradigms have we seen in the last decade alone?




A reader, November 18, 2004 - 12:20 pm UTC

I dont mean the "legacy" aspect of it as much as the comment that

"we've gone past the peak of Oracle's ascendancy"

"whilst Microsoft haven't exposed SQL Server's internals in the same way that Oracle have, in fact they've got a historical chance to instrument the entire application platform, as they own the technology behind Windows Server, IIS, .NET and so on"

"like disk storage and operating systems before it, the database itself is now becoming a commodity, with no-one these days getting fired for buying Microsoft SQL Server, and many organisations looking to open source databases such as mySQL to handle their day-to-day database needs. Whilst this is moving databases as a whole into the legacy category, it particularly hurts Oracle badly as firstly..."

Finally

"whenever a system or technology reaches a level of perfection (in other words, science is used as a rule) it will be replaced by something more chaotic that looks (and perhaps even is) cheaper", something that happened to mainframes before and, just at the point where it reaches the level of "technical perfection", could possibly be the fate of the Oracle RDBMS itself"

Soooo...is 10g as good as it gets? What is 11g going to have?

Bottom line...Oracle needs to do a better job of putting out vaporware out there! Heck, the whole world already knows what to expect in MS Version 2010 since they have been forward-selling it for years!

Tom Kyte
November 18, 2004 - 2:13 pm UTC

10gr2 is going to be announced at OOW in two weeks -- you judge. you can judge for yourself if we are "terminal".

Return of the Mainframes

reader, November 18, 2004 - 3:31 pm UTC

another interesting viewpoint on Oracle 15 years down the
road and the evolution of an old technology.


</code> http://www.dba-oracle.com/art_dbazine_2020_p2.htm <code>

Here the latest comparison between Yukon and Oracle 10g

Jack, November 20, 2004 - 6:38 pm UTC

Another and probably the most complete comparison between Oracle 10g and SQL Server Yukon: </code> http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf

This is a research article from Wisdomforce  
http://www.wisdomforce.com <code>and it has the clustering comparison for latest releases of both databases

Question regarding syntax and semantic check

Arun Mathur, January 20, 2005 - 1:00 am UTC

I was the one who originally brought up my experiences with MS SQL Server checking for syntax errors, but not semantic errors. Jim confirmed for me that this is still the case, and to be honest, I was hoping I was wrong.  Anyways, to answer the question about how Oracle does check for syntax and semantic errors, see my example below:

SQL> drop table t;

Table dropped.

SQL> create table t(t_pk number(11) not null primary key,name varchar2(40));

Table created.


SQL> create or replace procedure invalid_proc
  2  is
  3  begin
  4  insert into t(wrong_column,wrong_column_again) values (1,'Arun');
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE INVALID_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/28     PL/SQL: ORA-00904: "WRONG_COLUMN_AGAIN": invalid identifier

SQL Server would have compiled this procedure without any errors, and you wouldn't know this procedure is incorrect until you run it.  Thank God for Oracle......

Regards,
Arun


 

RE: Latest comparison of Yukon and Oracle

Scot, February 09, 2005 - 4:14 pm UTC

Thanks but no thanks.

So I open up the wisdomforce article referenced a couple of responses ago that compares sql server 2005 with oracle. I skip all the intro stuff about differences between the "old" sql server 2000 and oracle because, as we know, there are many.

By the way, "old" being in quotes because of course it is the only thing currently available :)

So I scroll down to where it starts to talk about new 2005 features, and get to LOCKING on page 10. I read this:

--- Begin of quote ---

Locking
The most notable feature added in MSSQL 2005 is probably a new isolation level called Snapshot Isolation (SI). The idea has been to add row versioning option to MSSQL so that
<...>
• If working in so called "Snapshot Isolation (SI)" isolation level, then all fetched rows will be returned in the same state as they were on transaction begin

The only reason to use (SI) mode is probably to provide an option to create a report that represents a consistent snapshot of complete system in point-in-time. (SI) mode has no direct equivalent mode in Oracle. It can be simulated although by, for instance, Oracle Flashback Query mode that is a switch to flashback mode in some point-in-time and create report using a consistent snapshot of the system.

--- End of quote ---

After reading this, the only thing I've read in the article, how can I take anything else the article has to say seriously?


Non Block Reads

A reader, March 10, 2005 - 3:57 am UTC

Hi,
In sql server we have an option of reading a previous value of data. In this case it is equalent to
oracle read consistentsy. Isn't it ?


Thanks,


Tom Kyte
March 10, 2005 - 7:40 am UTC

no, not even close.

read consistency is the fine art of providing a correct consistent answer that actually existed at some point in time in the database.

a dirty read (ala sqlserver) is the fine art of returning random data, to make up an answer that never actually existed.

Look on this example.

a reader, March 10, 2005 - 8:35 am UTC


ok ...

In Oracle

----
1
2
3
4
5

time t1 you start to read this data.
time t2 you are reading second row (2) from this table.
time t3 another session update fifth value of this table.
oracle will read older value from buffer cache or undo .
And it will get the point in time consistent result.


And I write a query in sqlserver for reading the old value it will ge me the same result ..




Tom Kyte
March 10, 2005 - 8:46 am UTC

sqlserver won't do that. sqlserver cannot read the old value, sqlserver has no clue what the old value is - sqlserver reads the uncommitted value using a dirty read perhaps -- but sqlserver is not reading the "old value"

Tom is right (of course)

djb, March 10, 2005 - 8:54 am UTC

You *can* get a read-consistent view of the data if you specify SERIAL (I think) in your query.

Of course then you get blocked reads until the prior update commits.


Tom Kyte
March 10, 2005 - 7:00 pm UTC

i assume you mean "in sqlserver"

reads won't block in oracle of course.

Scot's review

jan van mourik, March 10, 2005 - 10:13 am UTC

What about the "Snapshot Isolation (SI)" mentioned by Scot above? That sounds like an MS attempt to get read consistency?

regards, jan

Tom Kyte
March 10, 2005 - 7:10 pm UTC

it will be somewhat similar to the workspace management stuff we've had since 8i yes - more of a multi-row situation.

to jon

Menon, March 10, 2005 - 1:24 pm UTC

"What about the "Snapshot Isolation (SI)" mentioned by Scot above? That sounds
like an MS attempt to get read consistency?"

Sounds a whole lot like Oracle's special case when we set the transaction isolation level to serializable.
IS not the same as statement level read consistency
provided by Oracle by default.



The Horror that is SQL Server

Richard, March 16, 2005 - 6:48 am UTC

I recently left my job as an Analyst Programmer/SQL Server (MS-SQL) & Oracle DBA, in order to get into *proper* Oracle DBA work. I was aware that my Oracle DBA skills required spiffing-up, so I have embarked on a 60 day (9 hours per day) self-learning programme, which is going rather well, and ends in about 40 days. Sadly, a large percentage of employers use MS-SQL, as well as Oracle, so I have had to school myself in MS-SQL, too (just in case).

I decided to do the MS-SQL study up-front. I am now nearing the end of my 20-day MS-SQL study, and let me tell you, if you really, really test MS-SQL, then the sheer horror and frustration at this toy's inadequacies are staggering. Just about all of the gizmos, do-dads and other trinkets that MS-SQL employs in its Enterprise Manager (unfit for an enterprise and not much of a manager) fail to work as advertised, and in a few cases, wreck the database they are supposed to be helping protect. It's not my incompetence or lack of experience, either: all of the nasties I've encountered are recognised bugs.

Why would anyone willingly buy MS-SQL over Oracle? They must never have compared the two products first.

I stand corrected

Arun Mathur, April 06, 2005 - 10:24 am UTC

Sorry to keep bringing up semantic checking, but my previous post regarding SQL Server not checking for semantics is incorrect. A SQL Server user provided a test case for me, which I was able to reproduce using using SQL Server's interface called Query Analyzer.  It has a button which checks only for syntax errors without trying to actually store the procedure.  But, it also has a "Play" button which executes the statement, checking for both syntax and semantic errors, and then attempting to create the procedure.

I still did notice a difference between the two. SQL Server will not store a procedure that is invalid, whereas Oracle will.  In other words, the scenario below will run in Oracle but not SQL Server:

SQL> create or replace procedure a is
  2  begin
  3  b;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE A:

LINE/COL ERROR
-------- ------------------------------------------------
3/1      PLS-00201: identifier 'B' must be declared
3/1      PL/SQL: Statement ignored
SQL> create or replace procedure b is
  2  begin
  3  dbms_output.put_line('Procedure b');
  4  end;
  5  /

Procedure created.

SQL> set serveroutput on size 1000000
SQL> exec a;
Procedure b

PL/SQL procedure successfully completed.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Thanks,
Arun 

SQL Server Outperforms Oracle

Vaibhav, June 30, 2005 - 4:32 am UTC

Tom,

I have learnt a lot from your site.
Thank you for your great support.

I came across comparison of SQL Server and Oracle 9i
</code> http://www.microsoft.com/sql/evaluation/compare/sapad.mspx <code>
Please share your comments.

Regards,
Vaibhav

Tom Kyte
June 30, 2005 - 9:36 am UTC

well, lets see, it is on a microsoft website. without reading it, one would have to think "might it come from a certain bias"

wow, they compared old software on a machine from over a year ago to a brand new machine. with half the users on Oracle, doing half as much work. compared a 4 way dual core with a 4 way single core.



9i forms and SQL Server

Aamer Javaid, July 22, 2005 - 5:11 am UTC

Sorry For using an Indirect way to contact you

Actually I have a very serious problem with a mix application setup.

I am currently working on a Project with Oracle Forms 9i at front end and Microsoft SQL server at back end. Could Any one please recommand me the steps to connect Oracle 9i Forms application with SQL Server database.


Thanks in Advance...

M. Aamer Javaid
Oracle Certified DBA
aamerjavaid@yahoo.com

Tom Kyte
July 22, 2005 - 8:55 am UTC

The only supported way now is to use an Oracle database with either the gateway to MS sqlserver or the generic connectivity over ODBC to sqlserver.

I can say, Forms would probably not be in my list of 10 tools to develop an application against sqlserver with.

9i forms and SQL Server

Aamer Javaid, July 25, 2005 - 9:16 am UTC

Tom,

Thanks for your kind reply. would you please recommand me how to make a generic connectivity over ODBC to sqlserver without having the need to use an Oracle Database.

Actually we have an application developed in forms 9i and one of our clients wants to use that application only with MS sql server.

Thanks again for your reply.

Aamer Javaid




Tom Kyte
July 25, 2005 - 11:06 am UTC

You cannot.

9i forms and SQL Server

Aamer Javaid, July 26, 2005 - 3:35 am UTC

Tom,

Thanks again for your kind reply.
would you please recommand me one of the followings for the same.

1). Oracle Application Server 9i as a middle tier between the two.
2). Microsoft Internet Information Server as a middle tier between the two.
3). Oracle 10g's Integretion Adaptors(Oracle Fusion Middle Tier) between the two.
4). Any Third party product as a middle tier between the two.
5). In any case, It is never possible.

Thanks again for your reply.

Aamer Javaid




Tom Kyte
July 26, 2005 - 8:04 am UTC

hmm.

6) Oracle the database between the two. To act as the ODBC bridge.

But once again, I'll reiterate, "this is not going to work so well"

9i forms and SQL Server

Aamer Javaid, July 27, 2005 - 4:23 am UTC

Tom,

Thanks

This is really a great help.

Now we can convince our client towards an Oracle Database Installation.

Thanks again for your reply.

Aamer Javaid



Is is right?

Totu, August 03, 2005 - 12:29 am UTC


Dear Tom.

Please, open below link. Is it righr that SQL 2005 benchmark is greater than Oracle?

</code> http://www.microsoft.com/presspass/misc/06-07flessner-teched05.mspx <code>

Thanks.

Tom Kyte
August 03, 2005 - 10:16 am UTC

all results are publically available on www.tpc.org, you can read them yourself?

currently in the c's they are 4th. check back next week, the ranks will have changed (it is a hardware benchmark, as the hardware improves....)

check out the h's too



Benchmarks

Bob B, August 03, 2005 - 11:58 am UTC

I would say that benchmarks of microsoft products on other microsoft products should be the best. What would be very curious is how well does sql server 2000 run on every other operating system that oracle runs on?

ORACLE vs SQLSERVER

Thirumaran, November 05, 2005 - 9:07 am UTC

Hi Tom,

I had an interesting argument with my friend he works as a SQL Server DBA ,whether to perform data migration through SQL Scripts (sqlldr/external tables) or whether to use some higher-end, "richer"(ease to use,GUI,less coding,etc..) tool like SQL SERVER INTEGRATION SERVICES(SSIS). we need to migrate data from oracle to oracle.

1) I knew that data extraction & data loading is superfast with sqlldr when compared to SSIS but i donot have any support articles(Benchmark results) to provide, I believe the advantage SSIS has is GUI,ease to use,Debugging is easy,visually see the migration process.

Also he pointed me to following page
</code> http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx

can i use "Oracle Database Migration Verifier 1.0.0" tool to migrate data from oracle 9i  to oracle 10g ,if yes ,
is it faster than (sqlldr/external tables). 
http://www.oracle.com/technology/tech/migration/index.html <code>


Thanks in advance
Thirumaran



Tom Kyte
November 05, 2005 - 5:58 pm UTC

and we have tools like OWB as well - full gui - but they take advantage of bulk processing.... the gui tools *don't have to be* slow by slow processing beasts, they can do things in bulk.


not sure what the question was here tho...

Ask WHO for SQL Server

Gabriel, February 13, 2006 - 9:04 am UTC

Hello Tom,

Do you know of a website for SQL Server that is just as good as yours is for Oracle? Been forced by circumstances to temporarily convert to SQL Server and I'm looking for a good, reliable resource. If you don't know about any, would you consider making one, you know, in your spare time...

Thank you,

Tom Kyte
February 13, 2006 - 9:52 am UTC

hahaha.

sorry - I do not know of any personally.

OS

Anil Uniyal, February 15, 2006 - 5:56 am UTC

Hi Tom,
good article...
Tell me one thing...
I have heard,felt and understood that Oracle works dirrerently on different OS .
Why don't you people develop an OS that best performs oracle db.
I think this would be a great help for Oracle DB performance as such because you people know much internaly about oracle...

Tom Kyte
February 15, 2006 - 9:33 am UTC

... I have heard,felt and understood that Oracle works dirrerently on different OS ...

you have for the most part

heard wrong
felt incorrectly
have misunderstood


99.99999% of everything comes back to "how did I design, build and implement my applications"

OS

Anil Uniyal, February 17, 2006 - 2:47 am UTC

Hi Tom,
Thanks for getting me on right track.I was confuesed about application performance on various database. Now I will focus at design side.

alt, February 27, 2006 - 8:14 am UTC

How to find the number of databases(both up and down) in Unix server.

Tom Kyte
February 27, 2006 - 9:05 am UTC

ps -aef | grep pmon

works well to see what instances are running.


IF you maintain the /etc/oratab
then
/etc/oratab can tell you what instances you normally run
else
you'll have to come up with your own standard way of recording this data
end if

Books to learn DBA on UNIX

reader, April 02, 2006 - 12:24 pm UTC

Hello Tom,

Thanks for supporting Oracle DBAs through this site.


I was wondering if you could point me out some good books/links through which I can improve my Oracle DBA skills for databases living on UNIX.


Most of the time I have worked on WINDOWS environment and just before a years or so I have started working on Solrais.

Have seen few books on Amazon but still wanted your suggestion!

Any suggestions??

Cheers,


Tom Kyte
April 02, 2006 - 1:31 pm UTC

I don't do windows, but I would be able to DBA it as well as my unix/linux machines.

You might be better off just with a plain old "getting to know unix" book - rather than trying to find a book specifically on "being a DBA in unix". You would benefit from mastering the scripting language (sh, ksh, whatever), and understanding how unix works.

DBA'ing won't be horribly different day to day.

Your opinion

Kamal Kishore, July 20, 2006 - 1:02 pm UTC

Hi Tom,
Would love to see your opinion on the following:

</code> http://www.samspublishing.com/articles/article.asp?p=375922&rl=1

specifically, this:

http://www.samspublishing.com/articles/article.asp?p=375922&seqNum=2&rl=1 <code>

where the author claims - "SQL Server is Superior, and Here's Why"

Thanks,


Tom Kyte
July 22, 2006 - 4:59 pm UTC

It is pretty funny isn't it?

Some people have written about this proposition before:

</code> http://www.niall.litchfield.dial.pipex.com/2005/12/logical-errors.html http://radiofreetooting.blogspot.com/2005/12/manageability-oracle-fan-gets.html <code>


If you write a paper that says "we shall use science, logic, deductive reasoning" - you should by all means include some!

I hope the article author does not think this as "the rants found on most discussion groups"

Kamal Kishore, July 23, 2006 - 12:17 am UTC

While reading that article, I moved from one page to another, in anticipation that some "proof" for the claims would be presented, but all I saw were unsubstantiated claims like these:

cheaper?
easier managed?
full (complete) feature set for businesses?
...

It all turned out to be a opinion of an uninformed author who seems to have no clue what he is talking about.

The links you posted were a good read but I think the author would claim all these discussions again as "Oracle enthusiasts' logical arguments, or lack thereof." or that these are "the rants found on most discussion groups."

Tom Kyte
July 23, 2006 - 9:30 am UTC

I am sure the author would - but only because they cannot see the forest for the trees.

for you see - to say "we will use logical deduction" and then not have a single logical deduction - well, that is a problem.

Hot air?!

cd, July 24, 2006 - 10:12 am UTC

The mentioned article seems to be a perfect example if I remember my composition writing classes at my college almost 2 decades ago correctly.

C.

concerning the "sams publishing" article

A reader, August 07, 2006 - 11:45 am UTC

sorry, have to do this one! can't stop laughing!

SQL Server Costs Less than Oracle
-> but Mysql costs even less than SQL Server
SQL Server Is Easier to Manage than SQL Server
-> but Mysql is equal or even easier to manage than SQL Server
SQL Server Has a Smaller Footprint than Oracle
-> but Mysql has a smaller footprint than SQL Server
SQL Server Has Most Every Feature Required for Modern Business Applications
-> No question the others have more features. But according to the authors: "do you need them"? so, mysql has also most every feature...

BUT
an ascii file costs even less than mysql
an ascii file is easier to manage than mysql
an ascii file has a smaller footprint than mysql
an ascii file has also most every feature (in a single user environment...)...

CONCLUSION
use ascii files! cheap, easy to manage, small footprint, every feature! back to the roots, guys! ;)

Microsoft SQL Server is more secure than Oracle?!

Rumburak, November 29, 2006 - 5:27 am UTC

from David Litchfield, 21st November 2006:

</code> http://www.nextgenss.com/research/papers/comparison.pdf <code>

And now Tom: ;-)



Oracle Forms with MS SQL server

vijay, October 04, 2007 - 5:28 am UTC

We are using Oracle 10G Application Server (10.1.2.0.2) on Windows 2003 platform to deploy Oracle FORM
based applications. This robust application is using Oracle 10g Database (10.2.0.3) on Windows 2003 platform as backend.

Customer has asked for a solution to connect the same application with the Microsoft SQL server. Will it be possible to connect the existing 10g forms to the SQL server with a minimal changes. If possible please provide necessary inputs

SQL Server Still Doesn't Get Concurrency

Warren, October 16, 2007 - 2:04 pm UTC

Here is a posting from a very large software company (CA) about one of it's enterprise products:

"May 14, 2007

Announcing drop support for SQL Server 2000 starting with Clarity 8.1

There have been many deadlock (and extended locking) issues with the use of Microsoft SQL Server 2000 and Clarity, which often result in very poor performance and failed user actions. In many cases these problems have proven to be essentially unsolvable because of fundamental underlying problems in the SQL Server 2000 locking architecture. As a result, Microsoft has re-architected this for SQL Server 2005. "

Microsoft responds with SQL Server 2005 and "Snapshots" that require a separate database to manage (read fudge) concurrency:

http://msdn2.microsoft.com/en-us/library/tcbchxcb.aspx

SQL Server is still not an enterprise db solution.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.