Oracle objects and collections with Java

lewisc

Hi.

I've designed a lot of database applications in my time. I pretty much always make heavy use of objects (oracle database defined, complex types - also collections) for parameters and such to procedures. I've never had any problem with it in the past (8i, 9i, 10g or 11). I made some suggestions for improvements at my current gig and got some fear and doubt coming back at me.

I've used objects with both Java and .Net with no issue but I was not the one doing the middle tier coding. So, I don't really know how to respond to this. I can do Java coding but am nowhere near knowledgeable on connection pooling.

Specifically, the reason that the dev team does not want to do this is because, and I quote:

Java mapping for oracle type and table type is oracle.sql.STRUCT. When oracle.sql.STRUCT is used then vendor connection needs to be used. Vendor connection is not logical connection it is physical connection. So whenever we close the connection that connection gets destroyed and connection pool will create new connection.

So, the fear is that this will cause a performance issue if every call has to reconnect to the database.  I *know* this *can* work without performance issues. Is there a particular thing to look at? Type Map vs Struct? Pool configuration? Something else?

This Tomcat (and Mule) on 11g.Linux is the OS. We're using the latest Oracle drivers.

Thanks in advance for any insight.

LewisC

dsurber-Oracle

Tell your Java dev team that they need to use java.sql.Struct, not oracle.sql.STRUCT. This is part of standard JDBC and does not require access to the vendor connection. Or set the type map and use your favorite Java class to represent the Struct objects.

Douglas

PS oracle.sql.STRUCT is an implementation of java.sql.Struct but that is an irrelevant detail so far as your Java dev team is concerned. They can write everything they need to with just java.sql.Struct.

Unknown

Specifically, the reason that the dev team does not want to do this is because, and I quote
 

Sorry - but that isn't sufficient information to help you.

That 'quote' is NOT from any documentation that I can find. It appears to be from your 'dev team'.

You need to provide actual facts and not just opinion or conjecture. Can you provide a link to any documentation that supports the statement that your dev team made?

I also have some serious reservations about this statement:

I pretty much always make heavy use of objects (oracle database defined, complex types - also collections) for parameters and such to procedures.

If I were reviewing an architecture proposal that would raise several red flags for me.

1. why the 'heavy' use of objects?

2. why the need for complex types?

3. why the use of collections?

I would want to look into the actual use cases that justify the use of ANY of those as opposed to letting the database do the work and return  standard results to the client or return a ref cursor that allows the client to fetch and process the result set.

Use of complex types and collections should be an exception and not the rule. They contribute to performance, scalability and maintenance problems and also require that at least TWO tiers have detailed knowledge of those complex structures. When at all possible funcitonal implementations should be limited to a single tier.

Can you shed any light on the actual need to use some a complex implementation?

Java mapping for oracle type and table type is oracle.sql.STRUCT.

That is certainly ONE way to map them. But it isn't the only way. Which suggests that your 'team' is simply using the default mapping methods and doesn't want to use alternate methods.

See the JDBC Dev Guide section on Working with Oracle Object Types

http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraoot.htm

When oracle.sql.STRUCT is used then vendor connection needs to be used.

I've never heard of anything called a 'vendor' connection. Perhaps the 'team' is using a 3rd party library and/or is attempting to create database/vendor agnostic software that isn't aware of whether the database is Oracle, DB2, etc. If so that is an admirable goal but that typically means you settle for 'least common denominator' functionality.

Vendor connection is not logical connection it is physical connection.
So whenever we close the connection that connection gets destroyed and connection pool will create new connection.

I have no idea what that even means. It suggests that you can ask for some sort of 'special' connection which will be treated as a 'one off' and not really pooled. I don't know what sort of connection pools your 'team' is using but since every connection is to a vendor-specific database I certainly hope your 'team' is not creating a connection pool that has connections for different databases.

lewisc

The "quote" was from the dev team. I can't find anything in the documentation specifically that addresses it, which is why I am here.

As far as,

"If I were reviewing an architecture proposal that would raise several red flags for me.

1. why the 'heavy' use of objects?
2. why the need for complex types?
3. why the use of collections?"

Thanks for the laugh.

"That is certainly ONE way to map them. But it isn't the only way. Which suggests that your 'team' is simply using the default mapping methods and doesn't want to use alternate methods.

See the JDBC Dev Guide section on Working with Oracle Object Types
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraoot.htm"

I did look there. It was the first place I went. I didn't see anything that pertained. Again, that is why I asking, here,

I think their concern comes from this document:

Using API Extensions in JDBC Drivers

I raised the point that that is a weblogic doc, but they feel that same applies to tomcat. Does it?

To use the extension methods exposed in the JDBC driver, you must include these steps in your application code:

Import the driver interfaces from the JDBC driver used to create connections in the data source.
Get a connection from the data source.
Cast the connection object as the vendor's connection interface.
Use the API extensions as described in the vendor's documentation.
The JNDI lookup is wrapped in a try/catch block in order to catch a failed look up and also that the context is closed in a finally block.

The following sections provide details in code examples. For information about specific extension methods for a particular JDBC driver, refer to the documentation from the JDBC driver vendor.

Getting a Physical Connection from a Data Source

Note:
Oracle strongly discourages directly accessing a physical JDBC connection except for when it is absolutely required.
Standard practice is to cast a connection to the generic JDBC connection (a wrapped physical connection) provided by WebLogic Server. This allows the server instance to manage the connection for the connection pool, enable connection pool features, and maintain the quality of connections provided to applications. Occasionally, a DBMS provides extra non-standard JDBC-related classes that require direct access of the physical connection (the actual vendor JDBC connection). To directly access a physical connection in a connection pool, you must cast the connection using getVendorConnection.
Note:
Oracle also provides another mechanism to access a physical connection getVendorConnectionSafe. This mechanism also returns the underlying physical connection (the vendor connection) from a pooled database connection (a logical connection). However, when the connection is closed, it is returned to the pool, independent of the setting of Remove Infected Connections Enabled. For more information, seegetVendorConnectionSafe.

So back to my original question:

So, the fear is that this will cause a performance issue if every call has to reconnect to the database.  I *know* this *can* work without performance issues. Is there a particular thing to look at? Type Map vs Struct? Pool configuration? Something else?

Thanks,

LewisC

Unknown

I did look there. It was the first place I went. I didn't see anything that pertained. Again, that is why I asking, here, 

Then you either didn't look very well or you didn't understand what you found.

That doc section pertains EXACTLY to the use case you described and explains the difference between using Oracle extensions and use standard Java to accomplish the same thing by writing your own classes. The latter entirely avoids the use of STRUCT and what you are referring to as a 'vendor connection'.

As I already said above in response to your team's comment that 'Java mapping for oracle type and table type is oracle.sql.STRUCT'.

That is certainly ONE way to map them. But it isn't the only way. Which suggests that your 'team' is simply using the default mapping methods and doesn't want to use alternate methods.

As far as:

Thanks for the laugh.

If you think my concerns about the overuse of objects and collections is funny then your architectures have bigger problems than the one you ask about.

Objects, complex types and collections are probably the most misused functionalities in all of Oracle. That 'misuse' contributes to more scalability, maintenance and performance problems that almost anything else I have dealt with in almost 30 years of Oracle work.

To then add to that by actually misusing those functionalities in server/client communications makes things even worse.

That is why all of those raise red flags about whether they are really being used properly and the questions I ask about them are entirely appropriate.

The fact that you don't recognize that speaks volumes.

Good luck with your problem. I suggest you wait for others to offer help.

lewisc

Ego much?

I did look there. It was the first place I went. I didn't see anything that pertained. Again, that is why I asking, here, 
Then you either didn't look very well or you didn't understand what you found.

The link you provided (and that I have already viewed) says absolutely nothing about connection pooling or physical connections, hence my questions.

Instead of trying to show what a smart guy you are by talking around the issue, why not read the question and try to answer it?

So, the fear is that this will cause a performance issue if every call has to reconnect to the database.  I *know* this *can* work without performance issues. Is there a particular thing to look at? Type Map vs Struct? Pool configuration? Something else?

Good luck on the next 30 years.

jschellSomeoneStoleMyAlias

> I pretty much always make heavy use of objects (oracle database defined, complex types - also collections) for parameters and such to procedures

Then you are doing it wrong or stating it very badly. And that is based on my experience of many years along with working with other Oracle DBAs (which is all they did) with many more years experience.

But other than that I suspect the concern might be...

Connection pools tend to abstract specific driver details away and often make them entirely in accessible.  So getting to something like a struct might not be possible.  Even so that doesn't mean much because you could of course create your own connection pool.  One need not even start from scratch - just get an existing one and modify it.

But connection pools only have a certain real benefits in real situations.  In modern data centers connection creation is trivial and in other distributed networks connection instability make them less than usable (but presumably not relevant to your situation.)   In terms of where it does make it difference it is often with high transaction rate systems with a need to minimize servers.  So if you are getting by with one main server then a connection pool probably won't mean anything.  If however you are adding one a day due to db problems then reducing that to one server every other day would be a win.

lewisc

Thanks for the reply.

> I pretty much always make heavy use of objects (oracle database defined, complex types - also collections) for parameters and such to procedures

Then you are doing it wrong or stating it very badly. And that is based on my experience of many years along with working with other Oracle DBAs (which is all they did) with many more years experience.

Ok. I must be stating it badly because I have been doing it since 8i very successfully. How about we drop the "always" and say "when appropriate"? Everybody's fuzzy all toasty now? I design VLDB and high transaction rate systems and have for quite some time now. I'm database, not java. Objects used properly are not a problem. But that is a completely different discussion, and beside the fact that it involves objects, has nothing to do with the actual question.

Anyway,

Connection pools tend to abstract specific driver details away and often make them entirely in accessible.  So getting to something like a struct might not be possible.  Even so that doesn't mean much because you could of course create your own connection pool.  One need not even start from scratch - just get an existing one and modify it.

I think that is their concern. I had another conversation, and while I am still not entirely sure of the doubt, I think your description describes it. Is creating a new pool the only option or are there alternate ways of using the objects (as parameters to stored procedures)? What about type maps and custom java objects? Would those need a physical connection that would need to be recreated after every call?

This is a distributed, high volume system. A dozen or so application nodes against a rac cluster.Each app node has 50 or so active connections. I am refactoring the design to improve maintainability and performance.

LewisC

Joe Weinstein-Oracle

Hi. Beware of complex driver-delivered objects, especially any that exhibit any 'liveness' to them, becuase

they are very likely to retain and use internal references to the JDBC connection that produced them, and

they are in fact tied to the fate of that connection. Some calls that use the object and a connection may not

behave if the connection is not the one that produced the object. So if you plan to pool such objects, it may

be best to have a per-connection pool... I *may* have added something to this thread... I hope so.

lewisc

Hi Joe.

Thanks for the reply.

What do you mean by " complex driver-delivered objects"? Is that specifically Oracle object types or something related?

Do you have some more details on what you have run into or possibly some links?

Thanks again.

LewisC

Joe Weinstein-Oracle

Yes, the Oracle-specific objects such as structs, arrays, bfiles, REFs, and even LOBs. And their descriptors, whose

constructors also take a connection...

jschellSomeoneStoleMyAlias

> Ok. I must be stating it badly because I have been doing it since 8i very successfully

There is of course a difference between succeeding at using it and using it appropriately.

>  I design VLDB and high transaction rate systems and have for quite some time now

So do I.  Exclusively.  Yet I have never needed any exotic features (via JDBC API itself.)

> Is creating a new pool the only option or are there alternate ways of using the objects (as parameters to stored procedures)?

Some features in Oracle JDBC are only accessible when you use the Oracle JDBC API directly.  So if you are using a pool then only one of the following can be true.

- Use the pool, but don't use some features.

- Use a pool that provides access to the native JDBC API via some mechanism.  Such mechanisms are likely to be a bit hokey.

- Don't use a pool