Featured Posts

Season's greetings Seasons greetings to all my readers.  The winter solstice is past and we can now look forward to longer days and shorter nights, while we enjoy a really white Christmas in many parts of the world. We...

Read more

The battle of the bulge All of us make fun of fat people.  But, have you ever paused to think about how your derision affects the individuals self esteem? They may seem to laugh off your jokes, but deep down they are mortified;...

Read more

Playing Poker for fun! Poker is fun.  It need not be a gamblers game. It can be a game that you play with your family members on holidays and weekends. While there are many forms of Poker, the simplest form is the five card...

Read more

Are we rational to refuse belief in mantra and tantra? "The power of mantra or tantra?  I don't believe in that!" --that is the protest that is commonly heard among the so called rational intellectuals.  What is the rationality behind the disbelief?  Well,...

Read more

The ifs and buts of allopathy Medicine and practice of medicine today, is standardized around allopathic (Western) medicine. All other systems are called "Alternate therapies".  Allopathy has become some kind of standard (like the...

Read more

twitter

Follow on Tweets

  •  

Aug

20

2010

Oracle vs SQL Server Part II

Posted by Samantha | Comments : Comments Off

Category : Information Technology, software

Tags: , , , ,

Programming differences

SQL server is ANSI SQL 92 compliant and it will be possible to convert the database into other database formats. However, it requires a code rewrite.  SQL server also has no inbuilt JAVA database engine as in Oracle and Java classes cannot be loaded and executed in the database itself.  This limits database security and scalability to Java applications.

In SQL server procedures are not compiled till the point of execution unlike in oracle where the packages and procedures are compiled before execution.  Additionally in Oracle 9i, it is possible to translate PL/SQL code into C code and compile the code to get performance gains for numeric intensive operations.

The read/write of external files from a stored procedure cannot be performed in SQL Server, whereas this can be done in Oracle.

SQL server uses cryptic commands for database administration. Oracle uses more intuitive commands.

Further Oracle uses “create or replace” for procedures and views. This is not possible in SQL.  The result is that in SQL Server one must drop the procedure or view first and recreate it. This makes for two commands in place of one.

When changes are made to a dependent object in oracle the procedure, function, package or view is marked as invalid. This does not happen in SQL and the user may get unexpected results from his program.

Oracle has a recompiling option that is missing in SQL. The “alter procedure compile” command is applicable to procedures, functions, packages and views in Oracle. SQL programming demands that the whole procedure, function, package or view is resubmitted.

Null values will not be indexed in Oracle whereas SQL will index all rows. This may prove to be an advantage in Oracle where large tables and columns are often null and you need to find non null values quickly. It can however, be a disadvantage if you need to find null values.  SQL Server on the other hand allows you to ignore nulls if you wish to and index them otherwise. There are no nasty table scans that can surprise you.

Finally Triggers in Oracle do not provide for transactional control.  SQL server allows commits and rollbacks. This can be potentially dangerous in transactions that fire the trigger. Moreover, SQL server triggers can start transactions of their own, unlike in Oracle.

Performance testing
The statistics is fine grained in Oracle and the user has greater control. Yet obtaining the statistical reports is a challenge.

The SQL query optimizer is more reliable. Query hints are never used. This makes performance testing easier in SQL.

However, both data bases have performance glitches and the programmer will have to fix them.

Concurrency model

The concurrency model between SQL and Oracle are very different. Oracle operates against Point-In-Time snapshots of data, but acquires them on demand. Therefore readers are never blocked.

SQL on the other hand provides for more choices of isolation/concurrency and for more aggressive settings that will block readers and more aggressive readers can block writers.

Standby database differences
SQL Server and Oracle both allow standby databases. However, there are a number of differences in the way these databases are created and handled.  A standby database is usually set up on a second server. Logs are applied to this database for any changes so that the standby can be activated if the primary database fails.

In SQL server the “failover server” gets activated after the “master” and the “msdb” databases are restored from the backup or copied from the primary server and all logs are applied.  This is because the new users/roles added to the primary are not carried over to the standby and backups have to be done of the master and the msdb on a regular basis. Oracle standby database gets automatically activated after the logs are applied and all user and role information is available both in the primary and in the standby.

SQL server’s log shipping mechanism happens at OS level, while Oracles mechanism happens at Net8 level and logs are automatically applied to standbys without any scripts or OS batch files. However Oracle standby does not automatically create data files in the standby even when it is created in the primary database. Oracle 9i has rectified this problem. All versions of SQL server perform this operation automatically.

Direct loads are also not possible in Oracle if the unrecoverable facility is being used to bypass redo logging.  The data files from the primary database will then have to be manually copied to the standby. This has not been sorted out even in 9i.  This is not a problem in SQL Server versions.

Positioning

SQL server is an application that is positioned between MS-Access and Oracle. The functionality, performance and scalability of the application are a testimonial to this assertion.  SQL Server creates work groups and associates members to a work group and access to a specific data set is defined.  However, Oracle is more advanced and makes for larger applications in OLTP and Data warehousing. The clustering features of Oracle are ideal for Application Service Providers on the Internet, who want small clusters of servers that can be added to over a period of time. Oracle therefore makes a more convincing argument for the enterprise.

Cluster Technology

SQL server cluster technology does not permit the sharing of a database by two nodes in a cluster. The application must manually spit up and redistribute between the hosts who work on different data sets. It follows that it is not possible to scale up by adding nodes to a cluster of SQL Servers. Moreover, SQL Server configurations demand that only the power of one node is used while the others are failover clusters that are activated when the active node fails.

Oracle on the other had offers a Parallel server technology and it is possible for two or more instances of a database to remain active on different nodes in an active-active configuration. Lock management is used by the Oracle Parallel server. Oracle 9i has been renamed as Oracle Real Application Cluster and the Cache Fusion is a diskless contention that allows read-read, read- write, write-read, and write-write contention between the instances and any application can be placed in the cluster without changes and upward scaling is wholly possible.

Replication differences:
SQL uses a simple replication of publisher-subscriber for transactional replication. This technology is slow because even when one table is being replicated, the entire transaction log is checked by the log reader agent and the transactional changes are applied to the subscriber.  In Oracle snapshot logs store the changes to a single table and this is copied across the logs. Archive logs are not checked.

Miscellaneous differences:

  • Oracle has 14 independent security evaluation as against SQL Server’s one.
  • SQL Server Enterprise manager is easy to use; Oracle Enterprise manager is very complex.
  • Oracle has introduced XMLType datatype to provide native support to XML and there are XML generation and aggregation operators that increase throughput of XML processing. In SQL Server there is only a limited means of reading and writing to xml from the tables.
  • Oracle information pool is free, whereas SQL Server information pool requires subscription.

Aug

19

2010

Oracle vs SQL Server: part I

Posted by Samantha | Comments : Comments Off

Category : Information Technology

Tags: , , , ,

[This article series was first published in www.sswug.org in November-December 2009. I will publish the next part of this article next week.  Hope you enjoy reading this article as much as my other readers did!]

Both Oracle and SQL are relational database systems (RDBMS). The data is stored in form of tables and columns. Both SQL and Oracle include programming languages, facilities and utilities that support enterprise level applications like ERP and data warehousing. So, both these applications can be described as a set of processes that run on an operating system. The processes define how the data is stored and how it is accessed. ODBC is supported in both.

Having said that, let us now examine the differences:

Multiplatform vs Single platform

Oracle is multiplatform; SQL server works only on Windows. This limits the use of SQL in enterprise solutions. Oracle can be used with Windows, all flavors of UNIX and with vendor machines like IBM, Sun, Digital, HP, Sequent and so on.

The advantage of using SQL server is that SQL requires only a specific operating system that supports multithreading and no special hardware. Oracle requires special hardware configurations. On the other hand Oracle supports unlimited number of user connections. SQL Server has a limitation to the number of user connections that are possible.

The language of data access

The data access in Oracle and SQL is done using a Structured Query language or SQL. SQL Standard was created to provide a means for coders to access and manipulate databases. It is an ANSI or American National Standards Institute standard. SQL allows coders to execute queries against a database; retrieve, insert, update and delete data from a database; create new databases and tables; create stored procedures, views and set permissions on tables, procedures and views.

While the SQL of Oracle and SQL Server implement the ANSI standard requirements, they are really very different versions of the SQL language.

SQL for SQL server uses a number of supportive tools (eg.SQL Plus) to build its applications. Basically SQL for SQL Server is composed of two components–DML and DDL. While DML is a data manipulation language DDL is a data definition language. DML includes commands like SELECT, UPDATE, INSERT, MERGE and DELETE. DDL commands are CREATE TALE, ALTER VIEW, CREATE VIEW or REPLACE PROCEDURE.

PL/SQL for Oracle is a procedural language that is an extension of SQL. SQL can be embedded in PL/SQL. It is used to create applications. It is a fourth generation programming language and has software engineering features such as data encapsulation, overloading, collection types, exceptions and information hiding. Rapid prototyping and development through tight integration of SQL with the Oracle database is a feature of this language.

One advantage of SQL over Oracle that is often touted is that SQL is ANSI-SQL ’92 compliant. This makes it very easy to convert the SQL database into another ANSI compliant database (at least theoretically). Oracle is more proprietary and the conversion is not possible.

Oracle uses multiprocessors; SQL uses multithreading.

Oracle uses multiprocessing to support more than one process simultaneously and this enables a number of programs run concurrently. Different users with different permissions may run different processes simultaneously. This is possible because the application is organized across multiple OS level processes. The processes are insulated from each other by the OS an error in one process will not impact the other process that are running concurrently.

SQL uses multi-threading and creates processes that have multiple threads running simultaneously. Each thread is a stream of instructions in the process with instruction pointers, stack of register and stack memory specific to the thread. The address space is also process specific or common to all threads within a process. This makes the data heap easily accessible. The multithreading process is more light weight than Multi processing. There are fewer contexts per thread than per process. Space sharing by the threads also makes for less work. Therefore costs are also lower.

However on the flip side, multithreading is more complex. The coder has to take care that the code is reentrant and thread safe. Often multi-version consistency is absent and “writers block readers and readers block writers” to make sure data integrity is maintained. This results in delays and waits in a heavy OLTP environment. SQL also will escalate row locks to page level locks if too many rows are locked on a page and this may even involve rows that are not being updated. So, long uncommitted transactions in SQL Server can halt all activity on the database and space allocated to committed transactions is not released till the recycling of the transaction log is complete.

In Oracle, this does not happen. Oracle dynamically re-creates a read consistent image for a reader of any requested data whose changes have not yet been committed. Uncommitted transactions will only halt the transaction itself when it runs out of rollback space.

In other words, the trade off is between speed and efficiency versus code complexity.

A natural corollary is that SQL is highly rated for Cost and performance.Oracle is priced higher.

Differences in Performance and Tuning

SQL server does not offer much control over sorting or memory allocation and Cache allocation unlike Oracle. The sort area size and cache area size can be set by the DBA in Oracle whereas the sort area size and cache area size is predefined in SQL Server.

The DBA also does not have any facility to prevent fragmentation in SQL Server. All pages are set to 8K and all extents are set to 8 pages. Larger extents for contiguous space cannot be specified for large objects. In Oracle this is fully configurable.

Range partitioning of large tables and indexes are not possible in SQL Server. In Oracle a large table can be seamlessly partitioned at the database level in range partitions. The performance and maintenance benefits derived from this are tremendous.

Log miner facility is unknown in SQL Server. Oracle 8i and 9i have a Log Miner inbuilt. This facility enables inspection of archived redo logs. However, in SQL server this facility can be integrated with third party tools.

SQL Server requires fully qualifying the name of an object to achieve 7-10% performance gains. Oracle too has some gains if the names are fully qualified, but the gain would be as small as 0.01% as Oracle uses its internal dictionary to resolve names.

Missing object types in SQL Server

SQL Server has

a. No public or private synonyms; no independent sequences

b. No collection of procedures and functions.

c. There is no such thing as independent sequence objects.

d. There are no packages; i.e., collections of procedures and functions.

e. No “before” event triggers (only “after” event triggers) and no row triggers (only statement)

To summarize:

Oracle is multiplatform and SQL is single platform application

Oracle uses SQL Plus and SQL uses SQL

Oracle is a multiprocessor application and SQL is a multithreading application

Performances tuning in Oracle and SQL server are different

There are several object types that are missing in SQL Server.

Stay tuned in for the next part of the review!

Get Adobe Flash playerPlugin by wpburn.com wordpress themes