Hello All:

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

Actually your statement

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

Maybe you are opening and closing the connection every time you execute
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).

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!

Larry schrieb:

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

It might help if you post your C# code...  I'd like to see how the
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

Maybe the CSharp code will help...

=========================================================================== ====

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();
   }

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!

--
Sybrand Bakker, Senior Oracle DBA

You are not using bind variables and over parsing. Each time you build
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

On thing that might help you diagnose the problem is a free tool called
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.

No.

I never said 'it doesn't work'.
I simply gave you the insert rate per second.

No, I fully expect that people who frequent these boards are here to
make extra cash on the side.

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

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!

There is something else wrong here, it is not the database that is
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

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.

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

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

news:qOLne.9857$xm3.5416@attbi_s21...

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

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.

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();
   }

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?

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!

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.

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:

       OracleTransation TransX = con.BeginTransaction();
       cmd.Transaction = TransX;

       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

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

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
damor...@x.washington.edu
(replace x with u to respond)

I would guess that the back and forth between the client and the server is
the limiting factor at this point.

If you can use MySQL, then use it.  If you can't use MySQL, then it
doesn't matter how fast it is.

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

xhos...@gmail.com schrieb:

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

hth,
 Moritz

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