This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 2, 2013 5:11 PM by lewisc RSS

Oracle objects and collections with Java

lewisc Oracle ACE Director
Currently Being Moderated

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


  • 1. Re: Oracle objects and collections with Java
    rp0428 Guru
    Currently Being Moderated

    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.

  • 2. Re: Oracle objects and collections with Java
    lewisc Oracle ACE Director
    Currently Being Moderated

    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

  • 3. Re: Oracle objects and collections with Java
    rp0428 Guru
    Currently Being Moderated
    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.

  • 4. Re: Oracle objects and collections with Java
    lewisc Oracle ACE Director
    Currently Being Moderated


    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.

  • 5. Re: Oracle objects and collections with Java
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

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

  • 6. Re: Oracle objects and collections with Java
    lewisc Oracle ACE Director
    Currently Being Moderated

    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

  • 7. Re: Oracle objects and collections with Java
    Joe Weinstein Expert
    Currently Being Moderated

    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.

  • 8. Re: Oracle objects and collections with Java
    lewisc Oracle ACE Director
    Currently Being Moderated

    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

  • 9. Re: Oracle objects and collections with Java
    Joe Weinstein Expert
    Currently Being Moderated

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

    constructors also take a connection...

  • 10. Re: Oracle objects and collections with Java
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    > 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

  • 11. Re: Oracle objects and collections with Java
    lewisc Oracle ACE Director
    Currently Being Moderated

    > 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'm not sure what your point is. It sounds to me like you are just trying to argue a point unrelated to the question.

     

     

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

     

    Seriosuly? Oracle Objects = Exotic Feature? I guess we live/work in different worlds.

     

    > 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

     

    I don't think the pool is the issue. I think the struct is the issue. I think if we use type maps and custom objects, we remove the issue with the structs. I was hoping to clarify that in this group, but it looks as if people are incredibly hung up on objects.

     

    We're doing a POC so the devs can show me exactly what issue they feel they are facing.

     

    LewisC

  • 12. Re: Oracle objects and collections with Java
    lewisc Oracle ACE Director
    Currently Being Moderated

    Joe,

     

    Thanks for the info. I'll dig into that a bit more.

     

    LewisC

  • 13. Re: Oracle objects and collections with Java
    rp0428 Guru
    Currently Being Moderated
    I think if we use type maps and custom objects, we remove the issue with the structs.

    So you are finally willing to go back to that doc I referred you to that says EXACTLY that but that you said

    I didn't see anything that pertained

    The chapter I linked does nothing but explain the choice between an Oracle-specific implementation using STRUCT and Oracle extensions and creating your own Java classes and providing the Type map needed by the driver.

     

    Those two choices are what the chapter is all about.

     

    Now if you can just bring yourself to take the next steps and review the section

    Creating and Using Custom Object Classes for Oracle Objects

     

    And try the examples provided in the doc.

     

    This is what I had said before:

    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.

    If the Java team hasn't used custom Java methods and a type map before they may very well not want to do it this time either. And unless you have experience on the Java side your chances of swaying them on this issue don't look very good. Your best argument would be to present the alternatives to them and show them how the alternative solutions are worse than the custom Java approach.

     

    The doc is pretty clear. You either need to use Oracle's predefined objects (e.g. STRUCT) and methods or you need to provide your own customized versions that use standard Java objects and then a map to bridge the gap.

     

    That will also avoid the 'connection-linkage' problem that Joe (who, by the way, is a member of the JDBC driver development team) referred to above.

     

    As I previously mentioned connection pools maintain a set of connections to the same server which, by definition, means that all connections are to the same vendors database (e.g. Oracle). That means that if you create a pool of Oracle connections your code can be assured that any connection you get from that pool MUST BE a connection to the same Oracle database as the last connection you got from that pool.

     

    The 'generic' verses 'vendor' issue is due to the 3rd party software. Some implementations provide a 'sanitized' connection such as 'WLConnection' that implements their own interface. But a connection, is a connection, is a connection. And unless the vendor actually builds in code to close a connection that is obtained using a 'vendor' request connection pools have no reason at all to close the actual connection when it is returned to the pool.

     

    The actual connection in the pool 'is what it is'. An interface may present a limited window of methods that can be used on it but that can't hide what the actual object really is. Under the covers it will always have its true identity.

  • 14. Re: Oracle objects and collections with Java
    Joe Weinstein Expert
    Currently Being Moderated

    RP, just a side correction, I am in cahoots with the driver dev folks and have access to the code,

    and I wrote drivers for WebLogic back in the day, but my for-pay stuff now has to do with JDBC in

    WebLogic. I am a member of the expert panel for the JDBC specification though...

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points