| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Oracle versus MS Sqlserver 2000: Technical Comparison of the Features in the two databases
Oracle versus MS Sqlserver 2000
By Faulkner Kent, USA
Updated by PorusHH, Australia
Version 3.9, March 2003
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, Linux 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
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
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 MS 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. Date Math is strong in Oracle, whereas in Sql Server it is not.
For example to represent the date in number as an offset of the Epoch in days plus seconds of the day, in Sql Server a c program has to be coded, whereas in Oracle a simple Sql statement will do the job.
h. 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.
i. 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.
j. 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.
k. 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.
l. Error trapping in Microsoft's T/Sql language mostly use
the Go to, whereas error trapping in Oracle's Pl/Sql is more sophisticated (ada-like) and is cascaded automatically to the enclosing blocks if not caught in the first block.
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 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.
MS has nothing like this clustering technology of Oracle, which can best be described as "light years ahead".
9. REPLICATION DIFFERENCES In MS 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.
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 MS 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.
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.
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.
A 64 bit version was released in Sql server in late 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.
In Oracle 9.2 it is possible to recover to the block level
from Oracle Recovery manager (rman) backups. This means if
a few blocks have been corrupted in a datafile, you are not
forced to recover the whole file, you can just recover the
corrupt blocks from a backup and keep the rest of the data.
This is mainframe-like technology.
Such recovery to the block level is not possible in Sql Server.
In Oracle using RMAN you can specify two types of incremental backups.
Differential is the default , to implement a cumulative incremental backup
you use the CUMULATIVE keyword in the backup command.
The cumulative incremental backup behaves more like the SQL Server
incremental backup. Sql Server does not have the first type of
differential incremental backup. This prompted a Sql Server
database manager to say: "I like the Oracle Rman scheme. Provides
more options than SQL Server."
Servers running Sql Server 2000 are Windows 2000 servers
and are susceptible to virus attacks. In a Sql Server
site, a large database server was totally lost due to
a virus infection that stopped it from rebooting. Such
viruses are not seen on Unix computers on which run the
majority of Oracle databases.
Windows 2000 computers running Sql Server would therefore
require an anti-virus software (more added cost) which would
also impact the performance of the server.
Password management in Oracle, from 8i onwards, has a number of
advanced facilities such as the ability to lock accounts, password
expiry, which are not present in Sql Server.
In the case of Sql Server, after the install of the software and
creation of the first default instance, to create multiple named
instances on the same server, the Sql server cd has to be used
each time and the licence key input each time. In the case of
Oracle, multiple named instances can be created at any time
after the initial install, without the cd or any "licence key".
20. 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, MS Sql Server 2000 only has limited ways to read and write xml from its tables.
21. 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. This says a lot for the attitude of the two companies.
22. Some people say MS 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.
23. MS Premier support is not unlimited support, it is only
for a fixed number of "incidents", then the user pays extra per incident. Oracle standard suppport has unlimited support ie. any number of TARS (Technical assistance requests).
SUMMARY
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 Ellison the founder of Oracle
has been championing the Sql language before there was any company around
like MS.
Received on Mon Mar 10 2003 - 01:58:05 CST
 |
 |