Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Technical Comparison of Oracle and MS Sql Server 2000

Technical Comparison of Oracle and MS Sql Server 2000

From: P <zarathustri_at_a.com>
Date: Thu, 1 Aug 2002 11:12:43 +1000
Message-ID: <sP%19.49892$Hj3.149054@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

  1. 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.
  2. No control over SQL Caching (memory allocation) in Sql Serv. This is controllable in Oracle.
  3. 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.
  4. 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

  1. 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).
  2. 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.
  3. 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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.

  1. SECURITY EVALUATIONS
    As of 2002, Oracle has 14 independant security evaluations,     Microsoft Sql Server has one.
  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.
  2. 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.



Received on Wed Jul 31 2002 - 20:12:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US