Oracle vs SQL Server
Zeus
newsgroup and I'd like to hear the other side of the story. TIA.
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.
----------------------------------------------------------------------------
--------