Re: SQL SERVER vs ORACLE

From: <alifiemylarder1_at_hotmail.com>
Date: Thu, 16 Jan 2003 14:41:40 GMT
Message-ID: <3E26C37B.E1FBF429@hotmail.com>

check this, f.e.:

Subject:

             Tech Comparison of Oracle versus MS Sqlserver 2000
        Date:
             Thu, 16 Jan 2003 17:49:24 +1100
       From:
             "P" <zarathustri_at_a.com>
 Organization:
             Telstra BigPond Internet Services (http://www.bigpond.com)
 Newsgroups:
             comp.databases.oracle.server

Oracle versus MS Sqlserver 2000

Technical Comparison of the Features in the two databases

By Faulkner, Kent (kent.faulkner_at_trane.com) USA Updated by PorusHH, Australia
Version 3.5, January 2003

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.

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

No public or private synonyms
no independent sequences
no packages ie. collection of procedures and functions.
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.
A previous 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, using Oracle DATAGUARD in 8i which is supplied free with the database, this deficiency is fixed and data files are created automatically at the standby.
A current 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.
A Sql server manager studied Oracle Dataguard and was impressed with the "mainframe-like" capabilities of Dataguard, as opposed to Sql server log shipping.

     Another architect said there is "nothing" like Oracle
     Dataguard in Sql Server, scripts have to be written
     in the latter.

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.

SECURITY EVALUATIONS

As of 2002, Oracle has 14 independant security evaluations, Microsoft Sql Server has one.

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.

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.

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.

64 bit version about 4 years behind Oracle's 64 bit version

     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.

BLOCK LEVEL RECOVERY

     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.

VIRUS INFECTION POSSIBLE ON WINDOWS 2000 SERVERS

     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.

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.

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

Epicentre team A wrote:

> Hi
> Is discussion exist about SQL SERVER vs ORACLE ?

> Thanks
> Oraclion
Received on Thu Jan 16 2003 - 08:41:40 CST