Is Oracle slower than sql server
faisal....@gmail.com
I am having problem with oracle performance in comparsion with SQL
Server. My application supports both Oracle (9.2) and SQL server (2000)
database. I have identical schemas installed on both Oracle (installed
on windows box) and SQL server, all the tables and stored procs are
identical but I am constantly getting performace problems with oracle.
Oracle takes twice or more time to perform the same data retrival and
insertion tasks that SQL server does in half the time. Is it a general
problem which every one faces? if not then please let me know how can I
improve performance of on oracle database-- I am not comparing two
oracle's snapsots, but I want to improve oracle's performance to match
with SQL server --. Our data base is of medium size and we are running
both oracle and sql with alomost all default configurations.
Any one having such experience or solution to problem please answer.
Faisal
sybrandb
The reason Oracle is slower is you subscribe to the myth Oracle is
Sqlserver sold by a different vendor. It is not. The architecture is
completely different.
If you *ported* the sqlserver application *directly* without change to
sqlserver, yes, you will have a non-performant applicationi.
Sadly, there are complete armies of Oracle DBAs who do nothing but to
resolve the errors made by people like you.
I would buy any book on Oracle architecture or development written by
Tom Kyte and he will explain you why you are wrong. He will also
provide you with the tools to find out where you are wrong.
Furthermore the Oracle Concepts Manual, online at
http://tahiti.oracle.com, is a mandatory read. Likely, being
brainwashed by the Evil Empire, aka Microsoft, you didn't read
anything.
There is no such thing as a database independent app, and there will
never be.
--
Sybrand Bakker
Senior Oracle DBA
Charles Hooper
I can't disagree with anything said.
However, I would add that an "alomost all default configurations" setup
on a server with a single RAID 5 array will not deliver desirable
performance. The Oracle database must be properly tuned and installed
on a sufficient disk subsystem. In my case, under Oracle 8i, I saw a
roughly 5 fold application performance improvement over an almost
default Oracle installation. Some message threads on asktom.oracle.com
suggest that SQLServer can be faster that Oracle when there are few
users on the system, but as the number of users increases, Oracle
quickly overtakes SQLServer's performance.
Tom Kyte's books focus more on the application development side of
Oracle administration than they do on the system configuration side of
Oracle. Chapter 1 of both of his books is a must read for anyone who
believes that all databases are the same - just digital dumping grounds
for data. If you still are not convinced that a database is not just a
digital dumping ground, pick up a copy of "Cost-Based Oracle
Fundamentals" by Jonathan Lewis. If you are interested in determining
WHY your programs runs more slowly on Oracle, pick up a copy of
"Optimizing Oracle Performance" by Cary Millsap - the 10046 trace at
level 8 or level 12 will tell you why your program is running slowly.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Gints Plivna
Most probably you created your procedures at first on SQLServer and
directly ported them on Oracle. If so let me guess that you are using
some of following constructions:
1) creating and dropping tables on the fly
2) using plenty of user defined functions in DML statements
3) creating wide outer joins with little restrictive base record set
and discarding at least half of returned result on client
4) creating multi union selects of the same data source instead of
simple grouping
5) commiting as mad
6) haven't analyzed your schema and haven't looked at any of your sql
statement explain plans
7) using default Oracle configuration with for example choose or
all_rows optimizer goal for your _OLTP_ application.
At least these were the most common mistakes I found for our MS SQL
programmers trying to create database independent application both for
Oracle and MS SQLServer.
I can only agree that you should read previously mentioned books and
get the feeling how the very architecture of Oracle is different from
MS SQLServer.
Gints Plivna
http://www.gplivna.eu
Mark D Powell
Sybrand probably identified the main problem even if he may have been a
little harsh. Though in some cases his wording would be too kind.
Vendors who port their product from SQL Server to Oracle are quick to
blame Oracle for performance issues when often their applications do
not run well on SQL Server once the user load or data quantity
increases.
Faisal, Oracle provides a free SQL Server to Oracle conversion tool.
You might want to run the code through it and compare the results to
what you are running. The process might expose some tuning
opportunities.
Charles point about looking at you disk setup is also valid though disk
is often a 'black box' to the DBA these days. Your are told to "Put
all your database files there" and when you ask how many physical
dirves are behind the mount point you are told not to worry about it.
HTH -- Mark D Powell --
Carl Kayser
"Mark D Powell" <Mark....@eds.com> wrote in message
news:1153922359.6...@s13g2000cwa.googlegroups.com...
Where did the the OP say that he ported from SQL Server to Oracle? I think
that it is highly probable but ... perhaps that question should have been
the initial response from sybrandb.
> Faisal, Oracle provides a free SQL Server to Oracle conversion tool.
> You might want to run the code through it and compare the results to
> what you are running. The process might expose some tuning
> opportunities.
>
Oracle Migration Workbench? It does a reasonable job with moving data from
Sybase ASE to Oracle. But we still had to clean up some anomalies. As for
stored procedures and triggers - ugh. We decided that you might just as
well rewrite them - partly because of the architectural issues. (Sybase ASE
and MS SQL server syntaxes are very similar.)
> Charles point about looking at you disk setup is also valid though disk
> is often a 'black box' to the DBA these days. Your are told to "Put
> all your database files there" and when you ask how many physical
> dirves are behind the mount point you are told not to worry about it.
>
What? Surely you jest, sir! As any Harvard MBA graduate can tell you, he
doesn't have to know anything about the specifics of the company that he
runs. All one needs to know is general business principles. After all, if
you can run Pepsi then you can run Apple. Especially since both items are
food!! The same must be true for disk storage. Why, all you have to do is
mirror and use RAID4 and everything will be fool-proof ..... Not only that,
but the hardware vendors know what they are doing. It must be better to
have fewer drives, each of which has much more capaacity.
<turn sarcasm off>
joel garry
I second what everyone else has said, in particular that there is
probably particular code and coding practices that are the cause of
most of the problems.
9.2 is pretty good about defaults, but there are still some things that
my voodoo doll is complaining about.
First of all, you are using Oracle's Cost Based Optimizer (and
definitely read about it in the Concepts Guide!) So, if you have an
OLTP type system, you may want to blindly change the init.ora
parameters optimizer_index_caching to 95 and optimizer_index_cost_adj
to 50. This is the _wrong_ way to go about tuning this, but it may
give the CBO a nudge in the right direction, telling it to bias more
towards index usage. See the admin guide for details.
Second, you can sometimes find gross mistuning with the Oracle OEM
product. You can download it if you don't have it, for development
purposes only. In particular, there are tuning advisors and some tools
for showing if you are seeing the effects of too many full table scans,
what plans you are using and so forth. pga_aggregate_target,
db_cache_size, shared_pool_size are commonly changed.
Third, you must be sure you are on a recent patch level. Always state
the minimum information people need to help you on this group, see
http://www.dbaoracle.net/readme-cdos.htm People do want to help, and
we've seen the same mistakes over and over viz. SS and Oracle noobs.
Fourth, are you making a proper comparison between SS and Oracle? The
big gain comes when you have a bunch of people updating the stuff that
a bunch of other people are looking at. If you just have one person
using a table, you might as well use Access.
A common mistake is to have undo, redo log or archiving in places where
contention takes place, either controller or disk layout bottlenecks.
So you might want to use your OS tools to watch that. Also check your
alert log to see if it is complaining about anything, see how often
your logs are switching.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/state/20060726-1413-ca-missingwomen.html#
faisal....@gmail.com
Charles Hooper I am basically a developer and will not have much
control over how the client will configure its oracle server as
mentioned by "Carl Kayser" and how many users will be using the
database simulatneously. I am testing my application in development
environment right now with dedicated sql server and oracle on identical
windows servers.
As Carl Kayser mentioned we have not ported our application from sql
server to oracle. Basically our main application does not have much
database stuff it just calls stored procedures for performing db
related tasks. grunt of db realted tasks relate to fetching and loading
data. Although we first coded sql server procs but the oracle procs are
not blind copy of that procs
>>1) creating and dropping tables on the fly
We are using oracle temporary tables for this. They are created as part
of out schema.
>> 2) using plenty of user defined functions in DML statements
nope.
>>3) creating wide outer joins with little restrictive base record set and discarding at least half of returned result on client
Nope not manipulating the result set on client. Even if we did that
would be done for both the databases.
>>5) commiting as mad
nope. For the data retrieval task not commiting at all.
The following quries might help me identify the probelm.
Does grouping a large no of procs in a package can effect performance
(I dont think so it should)?
Our applicantion dumps files in to database. Issuing insert quries
gives horrible performance so we are using bulk loading in sql server
but for oracle we are using sqlldr with conventional load option.
(Can't use direct path load because multiple clients might be loading
data in the same table simultaneously with our application and direct
path load does not support that). Loading data through sqlldr takes
double the time then sql server bcp interface (And thats expected as we
are launcing a seperate sqlldr process for every load operation it is
much faster than insert quries but much solower than sql server bulk
loading). Is there any programming interface available that does the
same thing that sqlldr does?
Thanks again all for the great responses. The referenes and books
mentioned by all of you are really good.
Faisal
Papa Piquillo
1) If the procs are not related there is no reason to put them together
in a package and it can affect performance because when you call a proc
into a package the hole package will be loaded in RAM.
2) You can consider using external tables instead of sqlldr.
See:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6611962171229
for details.
Best regards.
Frank van Bortel
> Does grouping a large no of procs in a package can effect performance
> (I dont think so it should)?
Yes - in a positive way. It is logical to group procedures, that
"belong together" in a package. If one packaged procedure is called,
the package is fetched into memory - and thus all procedures, of
which it is more than likely to execute one - that is why you packaged
them, didn't you?
> Our applicantion dumps files in to database. Issuing insert quries
> gives horrible performance so we are using bulk loading in sql server
> but for oracle we are using sqlldr with conventional load option.
> (Can't use direct path load because multiple clients might be loading
> data in the same table simultaneously with our application and direct
Eh? what design is that?
> path load does not support that). Loading data through sqlldr takes
> double the time then sql server bcp interface (And thats expected as we
sqlldr is a server side utility for bulk loads. I have only seen it
outperform bcp by a factor of 3, same hardware (Ok, it wasn't MSSS,
but Sybase)
> are launcing a seperate sqlldr process for every load operation it is
> much faster than insert quries but much solower than sql server bulk
> loading). Is there any programming interface available that does the
> same thing that sqlldr does?
>
Sure, what interface, client or server side?
Web interface: WPG.DOCLOAD
Forms - W2UTIL, I believe the client side windows interface API was
called.
PL/SQL, server side: no, nothing beats sqlldr, although you could
take a look into external tables.
And loose the temp tables - you can merge, using a select as inline
view (aka table).
Perhaps stating the business case would help, in stead of finding
solutions for a perhaps less-than-optimal implementation.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...