Oracle and Dotnet
Larry
I am looking for an "opinion" by Oracle Professionals as to whether
Oracle server 10G can be expected to perform properly using the ODP.net
as provided by the Oracle Corporation.
Our company has contracted out Oracle DBA's. They have set up a 10G
development server for me to use. I am using ODP.net from Oracle and
Csharp. I have a single table, with a single field (varchar(20)), no
indexes at all.
Running my app from the same machine that the server is located on, I am
inserting a whopping 35 records per second.
The exact same program (except for connection string and changing
'Oracle' to 'MySQL') gets me an insert rate of 7250 in the MySQL database.
Obviously, something is wrong. Oracle didn't become the industry leader
with that type of performance. Our Oracle DBA's are claiming the problem
is DOTNET. I don't buy that, especially since I am using a provider
directly from the Oracle Corporation.
I need an opinion on the following....
SHOULD I EXPECT HIGH PERFORMANCE FROM ODP.NET?????????????
Sybrand Bakker
Actually your statement
>Csharp. I have a single table, with a single field (varchar(20)), no
>indexes at all.
Tells it all.
Likely much more is wrong. Your problem is you don't know Oracle and
the Oracle DBAs you hired also didn't knew Oracle.
--
Sybrand Bakker, Senior Oracle DBA
MBPP
a statement? I don't know much about .NET, check for connection pooling
parameters in the connection string (or maybe you need to implement
this by yourself in the code).
Larry
>>Csharp. I have a single table, with a single field (varchar(20)), no
>>indexes at all.
>
>
> Tells it all.
> Likely much more is wrong. Your problem is you don't know Oracle and
> the Oracle DBAs you hired also didn't knew Oracle.
Well, actually, you're missing the whole point here.
I created a simple table with a single field and no index to make the
insert test as simple as possible. This is just an insert test.
This is not the final 'product'.
No, I am not a DBA, and yes, I know very little about Oracle.
OTOH, I know very little about MySQL but I was able to get 7250 inserts
per second out of it as opposed to the 35 per second in Oracle.
This is not Oracle VS MySQL because I know that Oracle is a more
complete product. All I really wanted was opinions from Oracle DBA's as
to what they have to say since our contracted DBA's seem to want to
blame dotnet.
In essence, I am looking for ammunition from other Oracle DBA's to
counter what our DBA's are saying.
Thanks for your comment...but you weren't very helpful!
Moritz Klein
[... bad performance on inserts with ODP.Net and C# ...]
Look into the manuals and search for prepared statement and bulk binds.
Maybe you have many round-trips or your query has to be parsed for every execution.
hth,
Moritz
Jesse
inserts are being generated and sent to Oracle. Normally, I would
expect better performance than what you are seeing, and I typically use
the MS Oracle client. Let's see the code and maybe we can come up with
something.
Later...
Jesse
Larry
===============================================================================
using System;
using Oracle.DataAccess.Client;
class insert
{
static void Main()
{
String cs = "User Id=nopusr;Password=nopusr;Data Source=nop";
OracleConnection con = new OracleConnection(cs);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
for (int x = 1; x<1000; x++)
{
string SQL = "insert into test values ('" + x.ToString() + "')";
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
}
Sybrand Bakker
>No, I am not a DBA, and yes, I know very little about Oracle.
>OTOH, I know very little about MySQL but I was able to get 7250 inserts
>per second out of it as opposed to the 35 per second in Oracle.
>
>This is not Oracle VS MySQL because I know that Oracle is a more
>complete product. All I really wanted was opinions from Oracle DBA's as
>to what they have to say since our contracted DBA's seem to want to
>blame dotnet.
>
>In essence, I am looking for ammunition from other Oracle DBA's to
>counter what our DBA's are saying.
>
>Thanks for your comment...but you weren't very helpful!
Ahh but then of course you are expecting people here are clairvoyant
aren't you? You provide very little or actually no non-trivial
information, apart from 'it doesn't work', and you expect us to shoot
in the dark, or the provide a complete free of charge advice as to
what to do.
You might very well being handicapped by improperly sized online
redolog files, your program might commit every individual insert, you
might parse every individual statement every time ... Which is all
killing performance! Who are members of this group to decide what is
going on? If you post a generic request, be prepared to receive
generic responses, and don't try to blame anyone for doing so!
pobo...@bebub.com
that string it is a unique statement that needs validating and
compiling.
You should have something like
string SQL = "insert into test values (?)";
and bind the placeholder. Sorry I am not a C# programmer so I don't
have the syntax, but there should be ODP.NET code examples on
http://otn.oracle.com
--
MJB
Steve
sql monitor. It works by trapping all the communication made between a
specific client app and the oracle client, it outputs all the sql
statements, error messages, connects, disconnects and so on. I strongly
suggest using that to see if the .net portion is constatnly connecting,
disconnecting. or if there is a huge amount of time between insert
requests.
go here: http://www.toadsoft.com/downld.html and download SQL Monitor.
Larry
> Ahh but then of course you are expecting people here are clairvoyant
> aren't you?
No.
> You provide very little or actually no non-trivial
> information, apart from 'it doesn't work',
I never said 'it doesn't work'.
I simply gave you the insert rate per second.
> and you expect us to shoot
> in the dark, or the provide a complete free of charge advice as to
> what to do.
No, I fully expect that people who frequent these boards are here to
make extra cash on the side.
> You might very well being handicapped by improperly sized online
> redolog files, your program might commit every individual insert, you
> might parse every individual statement every time ... Which is all
> killing performance! Who are members of this group to decide what is
> going on? If you post a generic request, be prepared to receive
> generic responses, and don't try to blame anyone for doing so!
I might be doing all that, or I might not.
I didn't ask for a code evaluation.
I didn't ask to have my problem solved.
I said that the DBA's that installed Oracle blamed ODP.NET
for the poor performance. But, if you go back to the original question,
it was simply...
================================================================
I need an opinion on the following....
SHOULD I EXPECT HIGH PERFORMANCE FROM ODP.NET?????????????
================================================================
I was hoping to hear experienced Oracle DBA's say either
1. "Yes, you can get great performce out of ODP.NET" or
2. "No, ODP.NET has proven to be a dog. Don't bother with it."
That's all I wanted. Then I could go back to our DBA's and tell them
what other DBA's are saying. It's just too convenient for them to blame
ODP.NET. I'm looking for ammunition.
Now please, post your bank account number on your next posting so I can
deposit 2 cents into your account. You obviously feel offended at the
thought of giving "free" advice, so I wanted to pay you what you are worth!
Larry
Larry
Thanks for that reply. I changed my string to
=============================================
string SQL = "insert into test values ('1')";
=============================================
By doing that I removed the variable and simply deposited a constant.
My performance did improve from 35 to 40 inserts per second but that is
still way short of any usable rate for my application.
Thanks again!
pobo...@bebub.com
limiting you to such a low insert rate.
What happens if you connect using sqlplus and do this
On a laptop I am getting about 15,000 inserts per second. I would look
if the driver is set up correctly or if the C# code can be otherwise
optimized.
SQL> create table t (n number);
Table created.
Elapsed: 00:00:00.28
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into t values (i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.68
SQL> select 100000 / 6.68 inserts_per_sec from dual;
INSERTS_PER_SEC
---------------
14970.0599
This is also using a loop construct which is about the slowest method
that can be used. If set based processing can be used then the rate
will go up further
SQL> insert into t
2 select level n from dual
3 connect by level <= 100000;
100000 rows created.
Elapsed: 00:00:04.85
SQL> select 100000 / 4.85 inserts_per_sec from dual;
INSERTS_PER_SEC
---------------
20618.5567
This is on a pretty low powered, 4 year old windows laptop with 512 MB
of RAM and a vanilla install of 10g. So any half decent production box
should be able to kick sand in the face of these numbers.
--
MJB
Mladen Gogala
> Obviously, something is wrong. Oracle didn't become the industry leader
> with that type of performance. Our Oracle DBA's are claiming the problem
> is DOTNET. I don't buy that, especially since I am using a provider
> directly from the Oracle Corporation.
How is your DBA supporting his or her claim? Did he or she turn on tracing
(DBMS_MONITOR) and produce a trace file? What events are shown in the
trace file? There is a nice company that has at least a week or two of
experience with analyzing trace files and determining what the problem is.
You can visit them at http://www.hotsos.com. They can send you a
consultant or analyze your trace. Unfortunately, I don't work for them nor
I ever have. I am not that good. If your DBA is good enough to do it
himself, you'll save some money. They can even analyze trace file
submitted online. The proprietor of the company is Mr. Cary Millsap,
former VP of Oracle Corp. and co-author of "Optimizing Oracle for
Performance", ground breaking book introducing method to the madness of
tuning Oracle RDBMS. The other co-author is Jeff Holt, also of the Hotsos
fame.
> I need an opinion on the following....
>
> SHOULD I EXPECT HIGH PERFORMANCE FROM ODP.NET?????????????
What do you think, why is it nicknamed "dot not"?
--
Demagogue: One who preaches a doctrine he knows to be untrue to
men he knows to be idiots.
H.L. Mencken
Jim Kennedy
"Larry" <la...@larry.com> wrote in message
news:vmIne.3966$x96.1744@attbi_s72...
Too many variables to know what the problem is. Are you using bind
variables? (probably not) Are you using the array interface? (probably not)
Are you keeping the connection open or closing it with each insert? Are you
keeping the cursor open and rebinding the bind variables and executing?
(probably not) You are also probably committing after each insert. (don't
unless the transaction is that)
My guess is that you are constructing a string to do the insert and opening
and closing the connection with each insert. (which would cause a lot of CPU
and really slow things down.)
So I think you can get high performance from odd.net and I think you can get
excellent scalability from odd.net. However, that assumes the application
is written efficiently and takes advantage of Oracle.
So what is wrong with your example? Don't know, not enough information.
Try the same thing by using slider. I bet you will be able to import a lot
more than 35 records a second into that table via sqlloader. That would
give you an idea of probably how fast odp.net would be if written
effeciently. (estimate of top throughput in your environment.)
Jim
Jim Kennedy
Larry,
You are not using bind variables. This is very very inefficient. You are
forcing a hard parse. Use bind variables and just reexecute. Also I
suspect the odp.net isn't set up correctly. I don't use odp.net so I can't
tell you what to change. I have used Oracle's ole objects in VB and C++ and
using bind variables and array interface in VB I can get around 20,000
records inserted per sec into an IOT table. (over the network), with 8.1.7.4
version of Oracle. (10g should be more efficient)
Jim
Larry
> On a laptop I am getting about 15,000 inserts per second.
I ran your first example with SQLplus. It took me
about 10 seconds so that was a rate of about 10,000 per second.
Thats good...I'm good with that.
But I need that rate with ODP.NET.
And that's my dilemma.
Larry
> So what is wrong with your example? Don't know, not enough information.
> Try the same thing by using slider. I bet you will be able to import a lot
> more than 35 records a second into that table via sqlloader. That would
> give you an idea of probably how fast odp.net would be if written
> effeciently. (estimate of top throughput in your environment.)
Is it possible there are some inefficiencies in the code. Sure.
Do I think they are so bad to drop the rate from the 10,000 per second I
get with PL/SQL on SQLplus down to 40 per second with ODP.NET.
I doubt it.
I only open the connection once.
I'm not committing after each insert, I wait til the loop is finished.
I'm not using binding because I insert the same fixed SQL statement each
time.
Are there some inefficiencies...maybe...but geez, 40 inserts per second?
Here is the code again, it cannot be THAT inefficient!
================================================================================
using System;
using Oracle.DataAccess.Client;
class insert
{
static void Main()
{
//connection string
String cs = "User Id=nopusr;Password=nopusr;Data Source=nop";
//create the connection and open
OracleConnection con = new OracleConnection(cs);
con.Open();
//create the command and assign the connection to it
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
//create the SQL, no variables, no bindings to worry about
string SQL = "insert into test values ('1')";
cmd.CommandText = SQL;
//do my loop
for (int x = 1; x<1000; x++)
{
cmd.ExecuteNonQuery();
}
//done with the loop so I commit only once
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
//clean up
cmd.Dispose();
con.Close();
con.Dispose();
}
}
Larry
> Larry,
> You are not using bind variables.
I changed my code so I don't need variables. I'm simply using the same
SQL insert over and over. That's not inefficient, is it?
> Also I suspect the odp.net isn't set up correctly.
It was my understanding that the Oracle.DataAccess.Client DLL is all I
need to make it work. I would have thought that if something was
"missing" then the program just flat out wouldn't work.
I'm not saying thats not the case...and thats where I may have to turn
my attention for more investigation.
Thanks Jim!
Holger Baer
> Jim Kennedy wrote:
>
>
> Is it possible there are some inefficiencies in the code. Sure.
> Do I think they are so bad to drop the rate from the 10,000 per second I
> get with PL/SQL on SQLplus down to 40 per second with ODP.NET.
>
> I doubt it.
>
> I only open the connection once.
> I'm not committing after each insert, I wait til the loop is finished.
So you think. But .NET is autocommiting as every junk was that excreated by MS.
(And the reason behind this is that MS SQL Server is not very good in long
running transactions). Even JDBC suffers the same problem (because most
Java guys are not much better when it comes to databases).
So search the .Net documentation how to turn off autocommit, and you'll be
fine.
> I'm not using binding because I insert the same fixed SQL statement each
> time.
>
> Are there some inefficiencies...maybe...but geez, 40 inserts per second?
You suffer the same problem that many developers suffer. You start out with
a small benchmark, and instead of trying to learn something out of it (namely
how to use Oracle correctly in the first place) you're prepared to blame anybody
else. A database is not a bit bucket and code that runs on one RDBMS will not
necessarily run well on another. ANSI SQL is a formal description of the SQLanguage,
however, it's implementation varies from RDBMS to RDBMS.
To simulate what the .NET code actually does, you can run the following test:
declare
2 l_txt varchar2(20);
3 begin
4 for i in 1..10000 loop
5 l_txt := lpad (to_char(i), 20, '*');
6 execute immediate 'insert into demo values (''' || l_txt ||''')';
7 commit;
8 end loop;
9 end;
10 /
The timing will not be the same, but it will get you in the right direction.
Now how to fix this?
Try this:
> //create the command and assign the connection to it
> OracleCommand cmd = new OracleCommand();
> cmd.Connection = con;
>
> //create the SQL, no variables, no bindings to worry about
> string SQL = "insert into test values ('1')";
> cmd.CommandText = SQL;
>
OracleTransation TransX = con.BeginTransaction();
cmd.Transaction = TransX;
> //do my loop
> for (int x = 1; x<1000; x++)
> {
> cmd.ExecuteNonQuery();
> }
TransX.Commit();
I'm not a .NET developer. But http://www.datadirect.com/developer/net/dot_net_optimizing/index.ssp
seem to know what they are talking about.
HTH
Holger
Larry
> You suffer the same problem that many developers suffer. You start out with
> a small benchmark, and instead of trying to learn something out of it
>(namely how to use Oracle correctly in the first place) you're prepared to blame
> anybody else.
I don't necessarily agree that I was "blaming" anybody.
Was I puzzled that the per second insert rate was at 40. Yes.
Was I disappointed that our companys contractors simply said that
"dotnet" was the problem? Yes.
Anyway, my not knowing that Dotnet was autocommitting was the core
problem. By changing the code to use a transaction, my insert rate went
from 40 per second to 1250 per second. That's still not quite the 7000
per second I get in MySQL using Dotnet, or the 10,000 per second I get
in Oracle when using SQLplus.
But it's a start. And a big one at that.
I thank you...and everyone in this forum that replied for your gracious
help!
Larry
DA Morgan
Well yes .NET is part of the problem. As you discovered not the entire
problem ... but .NET is not the tool of choice with Oracle for the
simple reason that Microsoft sees Oracle as a competitor.
I have personally seen Oracle inserts at 20,000 per second using Java
... so the rate is not being limited by Oracle assuming decent hardware.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)
xho...@gmail.com
> Holger Baer wrote:
>
> > You suffer the same problem that many developers suffer. You start out
> > with a small benchmark, and instead of trying to learn something out of
> >it (namely how to use Oracle correctly in the first place) you're
> > prepared to blame anybody else.
>
> I don't necessarily agree that I was "blaming" anybody.
>
> Was I puzzled that the per second insert rate was at 40. Yes.
>
> Was I disappointed that our companys contractors simply said that
> "dotnet" was the problem? Yes.
>
> Anyway, my not knowing that Dotnet was autocommitting was the core
> problem. By changing the code to use a transaction, my insert rate went
> from 40 per second to 1250 per second.
I would guess that the back and forth between the client and the server is
the limiting factor at this point.
> That's still not quite the 7000
> per second I get in MySQL using Dotnet,
If you can use MySQL, then use it. If you can't use MySQL, then it
doesn't matter how fast it is.
> or the 10,000 per second I get
> in Oracle when using SQLplus.
SQLplus is not doing the looping. It is merely submitting an anonymous
pl/sql block to the database, and the looping is occuring on the server
and not the client. That is why it is fast, there is no net traffic for
each individual row. You could have submitted that pl/sql block from .net
rather than from SQLplus (or at least I think you could have, I've never
actually done it) and get the same results.
Does .net support array binds?
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
Moritz Klein
> SQLplus is not doing the looping. It is merely submitting an anonymous
> pl/sql block to the database, and the looping is occuring on the server
> and not the client. That is why it is fast, there is no net traffic for
> each individual row. You could have submitted that pl/sql block from .net
> rather than from SQLplus (or at least I think you could have, I've never
> actually done it) and get the same results.
> Does .net support array binds?
Yes it does. That was one of the things I tried to point out by asking about
round-trips, because reducing them gives you better performance. Take a look at
"Oracle Data Provider for .NET Developer's Guide" or look at this How To:
http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html
hth,
Moritz
Larry
> Well yes .NET is part of the problem. As you discovered not the entire
> problem ... but .NET is not the tool of choice with Oracle for the
> simple reason that Microsoft sees Oracle as a competitor.
Sure they're competitors. But Oracle knows they are the market leader
in DB and they also know that there are a LOT of dotnet programmers that
need to interface with their server. They most certainly want to put
out the best product they can so that dotnet programmers can get the
most out of their product.
And thats why it didn't sound right when our DBA's blamed dotnet.
Oracle corp will do whatever it can to make its product work well with
dotnet. And they have.
Here is an entire Oracle site dedidcated to dotnet programmers.
http://www.oracle.com/technology/tech/dotnet/index.html
Anyway, one big hurdle out of the way. Now it's time to move on to the
next one.
Larry
DA Morgan
Oracle knows there are a lot of .NET programmers. Oracle does a good job
of creating compatibility with ODBC, .NET, etc.
The issue is more what Microsoft chooses to do. Look at their latest SQL
Server 2005 promo pieces for a taste of their attitude. They are
comparing a product that does not yet exist with a previous version of
Oracle on completely different hardware and then claiming superior
performance.
ROFLOL.
Holger Baer
> Holger Baer wrote:
>
>> You suffer the same problem that many developers suffer. You start out
>> with
>> a small benchmark, and instead of trying to learn something out of it
>> (namely how to use Oracle correctly in the first place) you're
>> prepared to blame anybody else.
>
>
> I don't necessarily agree that I was "blaming" anybody.
I was possibly exaggerating to make my point. Sorry.
>
> Was I puzzled that the per second insert rate was at 40. Yes.
>
> Was I disappointed that our companys contractors simply said that
> "dotnet" was the problem? Yes.
I overlooked that they were contractors. My bad.
>
> Anyway, my not knowing that Dotnet was autocommitting was the core
> problem. By changing the code to use a transaction, my insert rate went
> from 40 per second to 1250 per second. That's still not quite the 7000
> per second I get in MySQL using Dotnet, or the 10,000 per second I get
> in Oracle when using SQLplus.
>
> But it's a start. And a big one at that.
>
> I thank you...and everyone in this forum that replied for your gracious
> help!
>
> Larry
Glad I could help. I think much of the difference between dotnet and using
native PL/SQL might stem from the necessary roundtrips through the network
stack and the accompanied technology stacks. Secondly, although you're sending
always the same statement, Oracle will do a soft parse. The PL/SQL version
does only one parse and then reuses the cursor. Parsing is very expensive
(another lesson to be learned) and should be avoided whenever possible.
Oh, and just to give you an idea of what Oracle is capable, try running the
same benchmark(with transactions) against Oracle and MySQL, but this time make
it a multi-user benchmark by running it ten times in parallel. And compare
how MySQL supports transactions. (Last time I looked, it didn't, but that depends
of the version you're using and you have to use InnoDB etc.).
Cheers,
Holger
Anton Dischner
thers another point not metioned yet.
The insert rate per second will stay the same even if you have 100 mio
rows and more in a table.
I am not talking in theory. I have several of this beasts.
Many other databases will decline in insert rate and even come to a
complete failure.
kind regards,
Toni
PS: Next thing to learn is that delete is the most time consuing job.