Re: Technical Comparison of Oracle and MS Sql Server 2000

Hi there,

Come on, there must be something you like about SQL Server (just a tiny bit :)

Regards

Richard
"P" <zarathustri_at_a.com> wrote in message news:sP%19.49892$Hj3.149054_at_newsfeeds.bigpond.com...
> Technical Comparison of Oracle and MS Sql Server 2000
> -------------------------------------------------------------
> By Faulkner, Kent, USA
> Updated by PorusHH, Australia
> Version 3.4, August 1 2002
>
> 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.
>
> g. A third party database tuning tool expert (the tool works both with
> Oracle and Sql Server) said: "Performance tables in Sql Server are not
> as *rich* as oracle and incur high overhead for accessing them in the
> case of 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. SECURITY EVALUATIONS
>
> As of 2002, Oracle has 14 independant security evaluations,
> Microsoft Sql Server has one.
>
> 11. 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.
>
> 12. Encryption/Decryption of sensitive data:
>
> Oracle 8i Release2 (8.1.6) provides enhanced security features. Among
> them is
> the ability to encrypt data stored in the database. This means at the
> column
> level such as encrypting chemical formulas, credit card numbers,
> passwords or
> whatever data is sensitive to your business. Until now only
Protegrity's
> 3rd
> party product Secure.data had this capability. Oracle is now the only
> database
> vendor in the world that provides this feature directly in the
database
> and on
> all platforms supporting 8.1.6. Protegrity supports only NT, HP-UX,
Sun
> Solaris
> and AIX.
>
> Oracle allows data to be encrypted and decrypted using the built in
> package
> DBMS_OBFUSCATION_TOOLKIT.
>
> Sql Server has no built in encryption/decryption facility. Developers
> have
> to write home-grown DLLs to encrypt/decrypt data. Not so in Oracle,
> which
> has a built in utility.
>
> 13. Rollback not possible in MS Sql Server service pack upgrades:
>
> It is not possible to rollback any service pack upgrades
> to Sql Server. When you install a service pack, all original
> files are overwritten and MS does not support rollback.
> The only solution is to uninstall and reinstall Sql server,
> which is tedious.
>
> As compared to this, Oracle has full rollback facilities in releases.
> Major Releases are installed in different Oracle Homes and it is easy
> to rollback to an earlier release. Patches can also be rolled back.
>
> 14. 64 bit version about 4 years behind Oracle's 64 bit version
>
> 64 bit version still to be released in Sql server
> (Sept 2002). Whereas, Oracle 64 bit on Sun 64 bit
> has been available since 1998, so MS is at least
> 4 years behind Oracle in this regard.
>
> 15. XML Support:
>
> XML has emerged as the standard for data interchange on the web.
> Oracle8i is XML-enabled to handle the current needs of the market.
> Oracle8i is capable of storing the following:
>
> <> Structured XML data as object-relational data
> <> Unstructured XML document as interMedia Text data
>
> Oracle8i provides the ability to automatically extract
> object-relational data as XML. Efficient querying of XML data
> is facilitated using standard SQL. It also provides the ability
> to access XML documents using the DOM (Document Object Model) API.
>
> 9i enhancements to Xml support:
>
> XMLType datatype was first introduced in Oracle9i to provide a
> more native support for XML. Associated XML specific behavior
> was also introduced. In addition, built in XML generation
> and aggregation operators greatly increase the throughput
> of XML processing.
>
> The XMLType datatype has been significantly enhanced in
> Oracle9i Release 2 (9.2). In this release, 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.
>
> 16. 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.
>
> 17. 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.
>
>
>
> 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.
>
> --------------------------------------------------------------------------

--


> --------

>

>

>

>