Re: Wrapping all tables with packages and scalability
- From: "Vadim Gorbounov" <v.gorbunov@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2004 19:07:26 +0400
----- Original Message -----
From: "Nuno Souto" <dbvision@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 03, 2004 4:18 PM
Subject: Re: Wrapping all tables with packages and scalability
> Last time I looked there was only one J2EE environment?
> I didn't say that stored proc helped to avoid traffic, I said that stored
Ok, I must admit, sometime reducing network traffic helps scalability. That
is, when network is the bottleneck. Sometime DB cpu is the most difficult to
scale in 3-tier systems. I believe, stored procedures by themself may not
help save CPU cycles, and there are very common cases where it turns out
quite opposite. SP wrappers are good to facilitate application maintenance,
but when it comes to scalability one may need to sacrifice maintenance for
performance.
> proc WITH object types as parameters helped to avoid it.
> Something that is bypassed if you specify an object type as the
> parameter: its contents are not individually checked.
Not really true. It just happens different way. Oracle performs
serialization and deserialization both on jdbc driver and database side. It
might save network roundtrips, but when it comes to CPU there might be
unpleasant surprises.
>
> > most cases. Of course, no need for metadata when cursor is already
persed
> Yes it does let you use batch and it affects no scalability whatsoever.
> Nothing to stop you from using a batch statement returning multiple rows
in
> a
> ref cursor.
I don't get it. You can get ref cursor back from stor. proc. Than fetch
rows. BUT, you pass one set of parms and get one object (ref cursor) at a
time from stored procedure. It doesn't look batch processing to me.
And Oracle doc says: "Executing a batch that includes an operation that
attempts to return a result set will cause an exception.". True.
One thing doesn't stop another. And using stored procedures is
> not
> a stopper for any other technique nor does it preclude use of any other
> technique:
> it is completely compatible with any other access to the tables.
Check
http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/oraperf.ht
m#1056233
section "Types of Statements Supported".
Except reading manuals, one may set sql trace and try batch
CallableStatements from Java. And watch SQL*Net events between stor proc
execute calls (Oracle 9.2.0.4).
>
>
> You completely misunderstood what I said. I did not say to use Oracle
> OBJECTS. I said to use Oracle Object types. Which describe an
Nop, I perfectly understood this. To be precise, yes, one definitely uses
object types to define something, but ultimately passes objects (actual
data), and these objects must be serialized and deserialized, and my point
is, I do not care much about serializetion price on App server side, it is
easy to add more servers when needed, but I care about this price on
database side, because it is more difficult to scale the database.
Serialization CPU price is high enough to go back to many SQL calls and many
network roundtrips when DB is really busy. Serialization/deserialization is
not cheap in general, and Oracle server side implementation is not most
efficient too. It is easy to collect stat from 10046 traces to see how much
it costs.
> object's data as a structure, passed to-and fro by JDBC as a single atomic
> operation. And no, NOTHING says that objects have to be manipulated
Cheers, it's Monday.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
