SQL Server VS. Oracle
-
I don't have a huge experience in both Oracle and SQLSERVER, but I think that there is some relevant differences that should let you make your mind:
First of all, I would'nt simply compare SS and Oracle, but SS+ NT vs Oracle+NT or Oracle+UNIX.
SS runs only on NT. NT is easy to install, manage and (partially)tune. Keep in mind that you don't control everything on the machine your Database stands. What you gain in ease of manage, you lose in accuracy and transparency.
Oracle's preferred OS is UNIX (or Linux). This kind of OS gives you by far more handle on the resources: RAM,disks and CPU.
In most case, your datafiles will stand on what is called Raw Device, which is a contiguous zone on a disk. This will ensure you that your data zone is not fragmentated (ever heard of the "Read Ahead" algorithm ?),that you will not suffer "double buffering"...
The processes of Oracle on UNIX (Lazy Writter, coherence checkers, memory manager, ...) are more "manageable" by the user (with NT, you cannot easily handle a thread).
Oracle on NT seems to be implemented with a "UNIX approach" in order to allow the DBA go handle his server more accurately, but I don't have enough experience to confirm or deny this.
Real efforts have been made by Microsoft to improve the manageability on SQLSERVER 7 and 2000, but it still runs on NT only.
SO my last word is, once again:
What you gain in ease of manage , you lose in accuracy.
SQLSERVER is by far easier to install and manage than Oracle is.
Oracle is more tunable, scalable, and is "closer to the machine".
Benjamin
Edited by - bvesan on 12/18/2001 09:43:18 AM
-
Good points and thanks for the update.
BTW, wouldn't Oracle's RAW file be the same as the file used by SQL Server? all tables in SQL Server are contained within a single file in SQL. Granted, the growth of the file may result in a non-contiguous file.
However, Inserts, updates, and deletes should still move the table around within its file, which would be internal fragmentation for that table. In SQL you have to rebuild the clustered index to resolve this fragmentation. I assume there is a similar thing in Oracle.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
SQL supports raw partitions - but who wants to hassle with them!
Andy
-
There is also a fragmentation effect with Oracle, because the allocation unit, called Extent is variable size. a process is running while a base is "up" that online defragments the "files".
There is no File system like fragmentation with Raw device, but you cann't increase or decrease the size of your data zone !
I didn't know SS could handle raw partition, but I presume using them will increase performance, but the cost is no increase possibility...
-
I thought I read that SS2K would not support RAW partitions. 7 does.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
We find that by using the database platform that the application was written for has fewer headaches. We are a DB2, Oracle, and SQL Server shop. Each database application has it's own pros and cons.
Rob DeMotsis
Sr. SQL Server DBA
Pier 1 Imports, Inc.
Rob DeMotsis
Sr. SQL Server DBA -
Steve - raw is still supported, look for "Using Raw Partitions" in BOL. Tons of caveats though.
Rob - that makes sense of course, few apps are really platform agnostic - or even should be! Do you manage all 3 db types yourself, or have separate DBA's for each?
Andy
-
Hi all
Having come from the world of Oracle, SQL*Server 7 and 2k has been very impressive indeed. Under NT, I firmly believe SQL*Server is the only choice as Ive had nothing but problems with Oracle (no - I wont explain myself!
).The only BIG problem with SQLServer (and probably why it seems to break so many records) is its locking and redo/rollback consistency model. Writers blocking readers and readers blocking writers is pathetic. In Oracle the concept of rollback segs to maintain read-consistent views and ensure independent working environments that allows oracle to re-construct a sessions view of data is a great solition that works very well (and in 9i seg management is self administering but ive yet to try it). Its a pity SQLServer hasnt taken a similar model as thats is only really big downfall as far as im concerned.
Another problem is T-SQL. Its a poor mans PL-SQL - and I mean poor! no exception handling for one thing is a fundamental problem with t-sql.
I have yet to be firmly convinced (minus DW solutions) is the dynamic block sizes that oracle supports over SQL*Servers fixed 8k page and 64k seg sizes. Ive found more trouble than its worth in some cases buggering around with block sizes only to find that what you selected isnt that optimal and is actually causeing unforseen performance issues (minor has they are though).
We could rambel on for hours about DBMS costs/benefits and what feature is better than another for performance and suitablility reasons. In the end, do you sums, dont forget support, look at other costs (system admin), look at integration with other DBMS and associated systems, look hard at replication and what you plan to achieve. In the end, I believe you will find it hard to fault SQLServer.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Thanks for the info. BTW, in line with this, has anyone anything to say about MySQL/PostgreSQL?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
MySQL is still missing a few things isn't it...like sub queries? Have not looked at Postgres. I like the idea of a freebie, just not willing to give up too much. If you don't have a lot of data it's hard to argue with MSDE - if only they would remove the connection limits!
Andy
-
Andy - We have cross-trained all DBAs in each Database platform. We find that we can take vacations that way.
Rob DeMotsis
Sr. SQL Server DBA
Pier 1 Imports, Inc.
Rob DeMotsis
Sr. SQL Server DBA -
We currently use SQL Server 7.0 and our company is considering upgrading to SQL Server 2000 or to Oracle. I have heard a few things that I like about Oracle and wouldn't mind if we switched to Oracle even though I have no Oracle experience at this time.
One thing I've heard is that the execution plan for a query or stored procedure can be manipulated more in Oracle than in SQL Server. If true (can anyone confirm this), I like the thought. I've often wanted to force a query to execute a certain way to see if it ran better than how SQL Server thought to execute it, however the hints to tell (force) SQL Server to execute a query a certain way are limited.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Hi
You can use hints, like you can in SQLServer to alter the plan, thats about it though. I would highly recommend against hints unless you have a good reason to use them, why??
a) maintaintence is a right pain
b) does the hint work fine with lost of data or very little?
c) does the validity of using the hint "grow" with the tables growth?
Plan manipulation is not a big plus for going to oracle. I would say
a) superior replication (sqlserver is a damn site easier though and is catching up nicely)
b) partitioned tables
c) bitmap indexes
e) superior clustering (oracle RAC)
f) ability to alter block sizes for each tablespace
even still, without DTS, its tough getting data in and out.... ive heard of a few sites that purchased sqlserver just for DTS between their DB2 and oracle apps.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
You mean there is nothing similar to DTS packages in Oracle? Then how do you get data in and out of Oracle? Can the process be automated like DTS packages can be scheduled?
I think one of my strong points is DTS packages. I certainly enjoy designing and modifying them.
Are you saying replication in Oracle is better than SQL Server? How does replication in Oracle work?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
AFAIK, the only DTS competitors are expensive tools from 3rd party vendors. That was one of the big hits with DTS.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
).Viewing 15 posts - 16 through 30 (of 96 total)
You must be logged in to reply to this topic. Login to reply