Oracle versus Microsoft Sqlserver 2000

From: P <zarathustri_at_a.com>
Date: Thu, 30 May 2002 18:19:08 +1000
Message-ID: <6alJ8.945$Hj3.3615@newsfeeds.bigpond.com>

Oracle versus Microsoft Sqlserver 2000
SQL SERVER TECHNICAL LIMITATIONS

By Faulkner, Kent - USA
Updated by Havewala, Porus - Australia

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

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

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

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

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

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 Received on Thu May 30 2002 - 03:19:08 CDT