| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon May 03 2004 - 10:04:52 CDT
 |
 |