Re: Oracle versus Microsoft Sqlserver 2000

P wrote:

> Oracle versus Microsoft Sqlserver 2000
> SQL SERVER TECHNICAL LIMITATIONS
> ----------------------------------------------------------
> By Faulkner, Kent - USA
> Updated by Havewala, Porus - Australia
>
> 1. Single platform dependancy.
>
> SQL Server is only operable on the Windows platform, and this is a major
> limitation for it to be an enterprise solution. Oracle is available on
> multiple platforms such as Windows, all flavours of Unix from vendors
> such as Ibm, Sun, Digital, HP, Sequent, etc. and VAX-VMS as well as MVS.
> The multi-platform nature of Oracle makes it a true enterprise solution.
>
> 2. Locking / concurrency
>
> SQL Server has no multi-version consistency model which means that "writers
> block readers and readers block writers" to ensure data integrity. In
> contrast, with Oracle the rule is "readers dont block writers and writers
> dont block readers". This is possible without compromising data
> integrity because Oracle will dynamically re-create a read-consistent
> image for a reader of any requested data that has been changed but not
> yet committed. In other words, the reader will see the data as it was
> before
> the writer began changing it (until the writer commits). SQL Server's
> locking scheme is much simpler (less mature) and will result in a lot
> of delays/waits in a heavy OLTP environment.
>
> Also, SQL Server will escalate row locks to page level locks when too many
> rows on a page are locked. This locks rows which are uninvolved in any
> updates for no good reason.
>
> 3. Potential of long uncommited transactions HALTING database activity
>
> In sql server 2K, a long uncommited transaction can stop other
> transactions which queue behind it in the single transaction log, and
> this can stop all activity on the database,
>
> Whereas in Oracle, if there is a long uncommited transaction, only
> the transaction itself will stop when it runs out of rollback
> space, because of the use of different rollback segments for
> transactions.
>
> Oracle allocates transactions randomly to any of its multiple
> rollback segments and areas inside that rollback segment.
> When the transaction is committed, that space is released
> for other transactions, however Sql server allocates transactions
> sequentially to its single transaction log, the space
> occupied by commited transactions is not released to
> new transactions until the recycling of the transaction
> log is complete (in a circular round-robbin manner).
> This means if there is an uncommited transaction in the
> middle, and the transaction log cannot grow by increasing
> the file size, no new transactions will be allowed. This
> is the potential of a single uncommited transaction to
> halt database activity.
>
> 4. PERFORMANCE and TUNING
>
> a. No control of sorting (memory allocation) in Sql Server.
> Oracle can fully control the sort area size and allows it
> to be set by the Dba.
>
> b. No control over SQL Caching (memory allocation) in Sql Serv.
> This is controllable in Oracle.
>
> c. No control over storage/space management to prevent fragmentation in
> Sql Serv. All pages (blocks) are always 8k and all extents are always
> 8 pages (64k). This means you have no way to specify larger extents
> to ensure contiguous space for large objects. In Oracle, this is
> fully configurable.
>
> d. No range partioning of large tables and indexes in Sql Server,
> whereas in Oracle a large table (eg. 100 GB or more) can be seamlessly
> partitioned at the database level into range partitions, for eg. an
> invoice table can be partitioned into monthly partitions.
>
> Such partitioned tables and partitioned indexes give performance
> and maintenance benefits and are transparent to the application.
>
> e. No Log miner facility in Sql Server. Oracle 8i and 9i supply a
> Log Miner which enables inspection of archived redo logs. This comes
> free with the database. But in the case of Sql Server, external products
> from other companies have to be purchased to do this important Dba task.
>
> f. A Sql-Server dba claimed that fully qualifying the name of an object
> in Sql Server code would lead to performance gains of 7% to 10%.
> There are no dictionary performance problems like that in Oracle.
> Oracle would have some gains if it fully qualified all names - say
> 0.01 percent. This actally shows the difference in the internal
> database technology between Oracle and MS and implies that the
> technology of resolving object names via the dictionary is
> more advanced in the case of Oracle, ie. Oracle seems to better
> access its internal dictionary and resolve names, unlike Sql server.
>
> 5. MISSING OBJECT TYPES IN SQL SERVER
> a. No public or private synonyms
> b. no independent sequences
> c. no packages ie. collection of procedures and functions.
>
> 6. PROGRAMMING
>
> a. Significant extensions to the ANSI SQL-92 standard in Sql Server,
> which means converting applications to a different database later
> will be a challenge (code re-write).
>
> b. Sql Server has no inbuilt JAVA database engine as in Oracle.
> In Oracle, Java classes can be loaded and executed in the database
> itself, thus adding the database's security and scalability to
> Java applications.
>
> c. In Sql Server, stored Procedures are not compiled until
> executed (overhead). In Oracle, packages and procs/functions
> are compiled before execution.
>
> In Oracle 9i it is also possible to translate Pl/Sql into C code
> and then compile/link the code, which gives very good performance
> gains for numeric itensive operations. SqlServer has no such
> ability.
>
> d. In Sql server, there is no ability to read/write from external
> files from a stored procedure. Oracle has this ability.
>
> e. Sql Server uses cryptic commands for database adminstration like:
>
> exec sp_addrolemember N'db_datareader', N'davidb'
> GO
>
> This is to add the user davidb to the role db_datareader.
>
> On the other hand, Oracle uses standard English-like Sql
> commands to do the same:
>
> grant db_datareader to davidb;
>
> This one statement does all, in simple English, what the
> cryptic Sql server command does.
>
> f. Oracle Sql and Pl/Sql are more powerful and can do things more
> intuitively
> than Microsoft Transact-Sql. Try to sum up a column by each month, and
> show
> the totals for the month, in Sql Server you do it in T-Sql by grouping
> on strings, in Oracle it is possible to do this grouping by the
> trunc(<datecolumn>,'month') function. This method in Oracle is more
> intuitive, it understands the dates, the method in Sql Server does not.
>
> g. In Sql Server, you cannot issue a "create or replace" for either
> procedures or views, in Oracle you can. This one facility simplifies
> code writing, since in Sql Server the procedure or view must be
> dropped first if present and then recreated ie. 2 commands, in
> Oracle there is no need - a single command "create or replace" is
> enough.
>
> h. In Oracle, a procedure/function/package/view is marked as invalid
> if a dependant object changes. In Ms Sql there is no concept of an
> invalid procedure, it will run but give unexpected results.
> The former is more suitable for change control and preventing
> unexpected errors.
>
> i. A recompile reuses the code that is in the Oracle database,
> the actual command is "alter procedure <procedure name> compile".
> This is applicable to procedures/functions/packages/views.
> This concept of recompiling is not there in MS Sql server
> where you have to resubmit the whole code if you want to
> recompile a procdure.
>
> j. Triggers in Oracle do not allow transactional control ie.
> commit/rollback/savepoint statements. Whereas, triggers
> in Sql Server allow commits/rollbacks, which is potentially
> dangerous and can cause problems with transactions which
> fire the trigger. Triggers in Sql Server also can start
> transactions of their own which is not very good and
> shows lack of maturity of the language.
>
> 7. STANDBY DIFFERENCES
> Sql Server and Oracle have differences regarding standby databases.
> A standby is a database set up on a second server and to which
> logs are applied ie. all database changes, so that the standby
> can be activated and used in the case of a failover.
>
> a) In the case of Sql server, when there is a failover, the
> "master" and "msdb" databases have to be restored from backup
> or copied over from the primary to the standby and then the
> standby is activated after all logs are applied. In Oracle,
> there is no need for a restore to be done, the standby can
> be activated at any time after all logs are applied. This
> difference exists because of the fact that in Sql server,
> new users/roles added to the primary are not carried over
> to the standby (these users/roles go in the master/msdb)
> and backups have to be done continuously of the master
> and msdb, these backups are then restored when the
> time comes for a failover. In the case of Oracle,
> users/roles when created in the primary are automatically
> carried over to the standby. So when the failover time
> arrives, all that is needed is to activate the standby.
>
> b) In the case of Sql Server, if the standby is opened
> as read only, to switch it back to standby again, a
> restore from backup has to be done. In the case of Oracle,
> from 8i onwards, if a standby database is opened as
> read only, it can be reopened as a standby without
> restoring from backup.
>
> c) The time delay to apply logs between the primary and
> the standby can be varied, but it can never be 0 minutes
> in the case of Sql server. In the case of Oracle,
> in 9i it is possible to have logs applied simultaneously
> to the primary as well as standby, using Sql-Net.
> This means Zero data loss in the case of a failover
> whereas Sql Server's log shipping cannot avoid data loss
> during the time gap.
>
> d) Sql Server's log shipping mechanism also happens
> at the OS level, whereas Oracle's mechanism can take
> place directly at the Sql-Net level where logs are
> automatically applied to standbys without any
> scripts or OS batch files, this mechanism in
> Oracle is called managed standby.
>
> e) One deficiency of Oracle in the standby was that
> datafiles, if created on the primary, had to be manually
> created on the standby whereas Sql Server does this
> automatically. However, in 9i, this deficiency is
> fixed and data files are created automatically
> at the standby.
>
> f) Another deficiency of Oracle in the standby is that
> direct loads, if using the unrecoverable facility to
> bypass redo logging, require the data files of the
> primary database to be manually copied across to the
> standby. This is not fixed in 9i. Sql Server's version
> of log shipping and direct loads do not require this
> copying across.
>
> 8. CLUSTER TECHNOLOGY
> In clustering technology, in the case of Sql Server,
> 2 nodes cannot work on the same database, they "share
> nothing". At the best, to utilize the power of both nodes,
> the application must be manually spit up and redistributed
> between the hosts, working on different sets of data, and
> it is not possible to seamlessly scale upwards by adding
> another node to the cluster in the case of Sql Server.
> Most cluster configurations in Sql Server use the power
> of only 1 node, leaving the other node to take over only
> if there is a problem with the first node.
>
> In the case of Oracle Parallel server, it is possible to have
> 2 or more instances of the database on different nodes acting
> on the SAME data in active-active configurations. Lock management
> is handled by the Oracle Parallel server. With the new version of
> Parallel Server in Oracle 9i, renamed as the Oracle real application
> cluster (9i RAC), there is diskless contention handling of
> read-read, read-write, write-read, and write-write
> contention between the instances. This diskless contention
> handling is called Cache Fusion and it means for the first
> time, any application can be placed in a cluster without
> any changes, and it scales upwards by just adding another
> machine to the cluster.
>
> Microsoft has nothing like this clustering technology
> of Oracle, which can best be described as "light years ahead".
>
> 9. REPLICATION DIFFERENCES
> In Microsoft Sql Server's version of simple replication
> ie, publisher-subscriber using transactional replication,
> even if only one table is being replicated, the entire
> transaction log is checked by the log reader agent
> and transactional changes applied to the subscribers.
> In Oracle's simple replication, changes to a single
> table are stored in a snapshot log and copied across,
> there is no need to check all the archive logs.
>
> 10. As of 2002, Oracle has 14 independant security evaluations,
> Microsoft Sql Server has one.
>
> 11. Sql server magazines and internet articles of the magazine
> are only available with paid subscription. Whereas, Oracle
> has given its magazine free for many years, all articles are
> free on the internet, and the Oracle Technical network (OTN)
> is also free on the internet.
>
> 12.Some people say Microsoft Sql Server tools, like Enterprise
> manager, are easy to use. Oracle Enterprise Manager is a huge
> tool and seems daunting to unexperienced people. This is
> true to an extent, however ease of use cannot be compared
> with the many features in Oracle, and its industrial-level
> strength, and its many technical advantages.
>
> 13.TCP Benchmarks:
> March 2002 Benchmarks from Tpc.org show that Oracle 9i
> is seen in the majority of top benchmarks in "non-clustered"
> tpc-c for performance (oltp), whereas Sql Server is seen
> in the majority of entries for "clustered" tpc-c for
> performance (oltp).
>
> This gives the strange impression that Sql server is
> faster in a cluster than in a non-cluster, which is
> misleading. The fact is that this result is due to the
> use of "federated databases" in clusters by Microsoft
> in which pieces of the application are broken up and
> placed on separate active-active servers, each working
> on separate pieces of the application.
>
> While excellent for theoretical benchmarks, this is not a
> practical approach in the real life IT world because it
> requires massive changes to any application, and also
> ongoing changes to the application when new servers are
> added to the cluster (each server has a view that sees
> the data in the other servers, adding a new server would
> mean rewriting the views for all tables on all servers)
> and would be rejected by any practical headed manager.
>
> Using this impractical approach of federated databases
> in clusters, the impression is that Sql-server leads
> in clustered performance, but the practical reality is
> otherwise. This is seen in the way Sql-server is not
> to be seen in the non-clustered benchmarks.
>
> Also, Oracle leads the way for Tpc benchmarks for
> Decision Support systems with 1000GB and 3000GB sizes
> (Tpc-H by performance per scale), whereas Sql server
> is only seen to a small extent in the 300GB range.
>
> 14. XML Support:
>
> To provide a more native support for XML, the first release
> of Oracle9i Database introduced the XMLType datatype and
> associated XML specific behavior. In addition, built in
> XML generation and aggregation operators greatly
> increase the throughput of XML processing.
>
> With the second release of Oracle9i, Oracle significantly
> adds to XML support in the database server. This fully
> absorbs the W3C XML data model into the Oracle
> database, and provides new standard access methods
> for navigating and querying XML - creating a native
> integrated XML database within the Oracle RDBMS.
>
> The key XDB technologies can be grouped into two major classes - XMLType
> that provides a native XML storage and retrieval capability strongly
> integrated with SQL, and an XML Repository that provides
> foldering, access control, versioning etc. for XML resources.
> The integration of a native XML capability within the database
> brings a number of benefits.
>
> In summation, Oracle9i Release 2's XDB functionality
> is a high-performance XML storage and retrieval technology
> available with the Oracle9i Release 2 database. It fully
> absorbs the W3C XML data model into the Oracle Database,
> and provides new standard access methods for navigating
> and querying XML. With XDB, you get all the advantages
> of relational database technology and XML technology
> at the same time.
>
> In contrast to this, Microsoft Sql Server 2000 only has
> limited ways to read and write xml from its tables.
>
> SUMMARY.
> SQL Server is clearly positioned between MS-ACCESS and ORACLE in terms of
> functionality, performance, and scalability. It makes a work group level
> solution (small number of users with small amount of data), perhaps at
> the departmental level.
>
> Oracle is much more advanced and has more to offer for larger applications
> with both OLTP and Data Warehouse applications. Its new clustering features
> are ideal for Application service providers (ASPs) on the internet
> who can now start with a cluster of 2 small servers and grow by just
> adding a server when they need to. Besides, Oracle's multi-platform
> capability makes it the most convincing argument for an enterprise.
>
> Footnote:
> Oracle is the first commercial Sql database and is 25 years old in 2002,
> ie. it has been around since 1977. Larry Ellision the founder of Oracle
> has been championing the Sql language before there was any company around
> like Microsoft.
>
> ----------------------------------------------------------------------------
> --------
>
> Some views from Industry people:
>
> The only thing wrong with MS SQL Server is the "MS" in the name. That
> denotes
> "proprietary". As a system administrator and DBA with 25 years in IT it's
> the industries opinion that "Open Systems Standards" should be the guiding
> light when implementing "enterprise" solutions. When "MS" ports "SQL Server"
> to UNIX I'll consider it for "mission critical" operations. Until then let
> the local "mom and pop" operations have fun with their "toy". If you're
> interested in web enabled databases I suggest you look at IBM's UDB
> or Sybase 12.0, or even Oracle. No site getting millions of hits
> (et al Yahoo) would consider an NT Server running "MS" SQL Server..
>
> stuarta_at_usa.net
> Consultant, Financial
> 16-Aug-2000 06:17:18 am
>
> A non-techie perspective
> I am a Project Manager who has worked on both SQLserver and Oracle projects
> in internet and standard LAN environments. My opinion is based on user
> perceptions
> of the databases and may have no justification technically, but they are
> what
> people are saying. Most users don't care what the database is. Often they
> will
> be a MS or Oracle shop and that's that, you wont change them. So usually
> there
> is no choice or even comparison of technologies, it's just "we have an
> Oracle
> licence, use it" and that's it. There is more to speed than the database.
> The raw grunt of the database is rarely a problem. You are far more likely
> to find speed issues that come from poor database design, crappy data
> models,
> slow networks, poor application architecture or just plain bad software
> design than from the actual speed of the database. When you get to the
> really high end, sure it matters, but the time and effort spent changing
> from RDBMS to the other to get extra speed (if there is any, and
> marginal at best) could be better spent on a gruntier box, you'll
> need one next year anyway. Once an MS shop, always an MS shop.
> MS has the "integration" of its products down pat. If you implement
> an MS solution from end-to-end, likely you will never get out. Their
> products are so dependent upon each other that to replace one
> component is just too hard. You can build similar implementations
> in Oracle by embedding logic in the database, but you don't have to.
> Oracle is far more OPEN to different clients and platforms. If you
> want an SQLserver system to run at optimal performance, it's
> MS end-to-end.
>
> Robert Green, green_r_at_optusnet.com.au
> Software Developer, Consulting/Systems Integrator
>
> SQL Server is a joke
> With all the hype, MSFT still can't keep locks from escalating
> and DB reads clean. Until MSFT, SYBS, and IBM do so they will
> not be taken seriously in the high OLTP world demanded by the
> web. Check the stats, 10 of the top 10 web sites in the world
> use Oracle. As far as the TPC benchmarks are concerned, IBM
> and MSFT are using smoke and mirrors. It took TWELVE separate
> databases to be only twice as fast as ONE Oracle database.
> Oracle has been able to use federations of databases (used
> by MSFT and IBM) for benchmarks for five years. Oracle could
> beat IBM numbers 10 times over using a federation, but won't
> because any real DBA would never consider using this useless
> configuration in a real situation. Shared disk is the cluster
> configuration of every major system on the web, MSFT, IBM and
> everybody else can't yet do it so they use Shared nothing,
> which is good for just that, NOTHING. If you buy into the hype,
> be sure to keep your receipt!
>
> Joe DBA, joe.simpson_at_aol.com
> Software Engineer, Government - Federal

If you want a flame war post it in non-Oracle usenet groups.

But please do not cross post it here where we will have to suffer through another religious war.

Daniel Morgan Received on Thu May 30 2002 - 11:03:44 CDT