Monday, September 26, 2016

Looking for stories about using Hibernate with Oracle Database

I recently (well, OK, not all that recently) received this request:
Our Java developers think that the work can be done faster with Hibernate by basically eliminating the "middle-men" and "middle-women" (ie. the database developers) for what they think is the simpler database access tasks for basic forms and reports.  It is true that Java developer out number PL/SQL developers by about 10:1 at my company - so we do/would have a lot of work to do.  
But I've talked with the other PL/SQL developers and while we are all rather busy. there are almost no cases of PL/SQL tasks needing to rollover from one sprint to the next (meaning, or course, that a PL/SQL task didn't get done on time). 
We want to make sure that our company continues to use PL/SQL for the database access layer in our application. Can you help?
Hibernate offers an ORM (Object Relational Mapping) tool: "Hibernate ORM enables developers to more easily write applications whose data outlives the application process. As an Object/Relational Mapping (ORM) framework, Hibernate is concerned with data persistence as it applies to relational databases (via JDBC)."

I have never used Hibernate or any other ORMs, as I live within the database. The idea of an ORM is quite appealing: rather than write the SQL to perform the necessary SQL to overcome the "impedance" mismatch between objects and tables, Hibernate generates it for you.

Which is great, until the SQL that is generated doesn't perform well. Then you have to start customizing or going around your ORM, and so on. I have, over the years, heard from lots of frustrated Oracle Database developers who are sick of hearing them (or Oracle Database) being blamed for bad application performance, while they are simultaneously not able to (easily, at least) do what it takes to use Oracle Database properly.

So I have a request: do you have experience with Hibernate, or another ORM? Please share your experience, both positive and negative, below in the Comments section. Others, including the fellow who wrote the above message, can then use this information to help their company make the most informed decision.



  1. I was involved with performance tuning a large Java/Hibernate app. There were several I things I really didn't like about it:

    (i) The SQL text could change between releases for the same SQL statement - the column alias names were auto generated. This prevented SQL plan management being used. It also made comparing performance of the same statement between releases pretty difficult!

    (ii) On the odd occasion where a hint would have been useful, there was no way for it to be added successfully.

    (iii) Some of the SQL constructed with Hibernate was pretty bad, table names used multiple times unnecessarily etc..

  2. I've successfully used hibernate + java in a lot of projects in the past. platform independence was the main driver for me because we always had to support different databases (oracle, db2 and ms sql server) within platform independent rich clients. It never let me stood in the rain but development time and performance were not optimal.

    However, over the last years I've switched more and more to oracle optimized solutions-better application performance, enhanced development speed, built in security and the need for HTML front ends brought me to Oracle / PLSQL and Apex. It's now my first choice because I can focus on business requirements.

    As long as these programs don't need to be platform/database independent that's the perfect combination-in other cases I would still choice Java and JPA (Hibernate)

  3. I've seen .NET Applications that used Microsofts Entity Framework. The net result:

    1. Poorly SQL Performance
    2. Generated SQL Statements that no Human being Can read
    3. Bugs between Entity Framework Releases

    My Suggestion:
    Use a properly designed PL/SQL API!

  4. I'm repeating this over and over, and unfortunately, many Java developers don't understand this (and many database developers don't see it either, each clinging to their preferred platform):

    1. If you do complex writes, use an ORM
    2. If you do bulk operations and/or complex reads, use SQL
    3. If you do all of the above, use both

    I've summed this up in more detail in a blog post where jOOQ=SQL and Hibernate=ORM:

  5. I work as a DBA in an environment totally invested in Oracle database, but had the misfortune of working with several Java apps built on Hibernate. The performance was always questionable, impossible to tune, and custom calls to stored procedures - some of which were required for security purposes - were impossible. Given that we were committed to using Oracle as the back-end, platform independence (don't even get me started on THAT) wasn't ever a consideration. All I could conclude was that the Java developers mostly saw the database as a black box and didn't have any interest in actually learning SQL...

  6. I'm the Hibernate Developer Advocate and the author of High-Performance Java Persistence.

    Hibernate is not a substitute for PL/SQL. In a large enterprise application, you need both OLTP and OLAP.
    Hibernate shines in the OLTP world, while PL/SQL is great for OLAP and intensive data processing.

    Hibernate allows you to automate the bulk of OLTP statements, therefore making it easier to add/remove/rename
    columns since DML statements are automatically adjusted.

    Hibernate allows you to enable JDBC batching with just one configuration.
    This is desirable since few applications are developed with batching in mind, and by the time this happens to be a bottleneck, the application is already running in production.

    The second-level caching mechanism is not intended to replace database caching or application-level caching layers.
    What's great about second-level caching is the tight integration with the data access layer, therefore allowing you to update cache entries synchronously with the database.
    This is very useful in Master-Slave replication schemes, where there is only one Master node which takes all read-write traffic.
    By reducing load on the Master node, the system get accommodate a higher throughput.

    Concurrency Control is another advantage offered by Hibernate.
    When dealing with multi-request logical transactions, the built-in optimistic locking mechanism allows preventing lost updates.
    There are many concurrency control patterns you can built on top of Hibernate, like coordinating changes based on a root entity version.

    All in all, Hibernate is very useful when writing to the database.
    Although Hibernate offers the possibility of fetching entities, this feature is only suitable when entities need to be modified.
    Otherwise DTO projections and native SQL is the way to go.

    All in all, Hibernate is not meant to replace PL/SQL, but it brings a lot of value on the data access layer side.
    As for tuning Hibernate, these 85 articles on my blog demonstrate how you can use Hibernate in high-performance data intensive applications.

    1. Thanks, Vlad. I am sure your links will be very helpful to people who may be wresting with Hibernate. I must say, though, that I am very surprised to see you say that PL/SQL is not suited to OLTP.

    2. PL/SQL is suitable for OLTP if you want the data processing to be done over a given database connection. However, many web flows happen in multiple steps, which require multiple HTTP requests. For this reason, you need multiple database connections and the application-level transactions spans over multiple database transactions. That's where PL/SQL differs the most from application-level processing.

    3. Im impressed by Vlad’s reply. I wish that all developers had that knowledge.
      The framework itself isn’t the problem, but it’s part of a bigger problem.

      The general opinion is that hibernate is the only way too go
      “It’s solves the impedance mismatch”
      “HQL makes us none-database dependent”
      “We develop faster”

      This also means that a lot of projects don’t feel the need for a database developer or an DBA.
      Which in turn often leads too a poor database design , poor sql , poor data quality and in the
      end a poor application.Everyone I know today that work with Oracle, spends and enormous
      amount of time tuning hibernate queries, trying to fix broken database design etc etc.

      If you want support, performance and data quality then use the product “you” pay for ,stay close to the data a process it there, with PL/SQL which is optimized for that purpose.

  7. Seen recently on Reddit:

    I am ambivalent on the commentary, but your blog entry vividly reminded of it. I would think it lies somewhere between a bad implementation and a cautionary tale.

  8. Thanks for all the comments. I initially had trouble searching the internet for this topic until I started looking for articles on "Thick Database vs Thin Database". There does seem to be a lot of research in this area and it seems to all suggest that the Thick (plsql in the database) approach can be one or two orders of magnitude more efficient than the Thin (database access outside of the database) approach. See for example:

    Thick vs Thin Database by Toon Koppelaars

    He suggests that thin implementations can result in row-by-row processing and "chatty" network applications.

  9. Everytime Hibernate executes a query a kitten dies. (tm)

    Honestly, Hibernate is *probably* not that bad in itself. The issue is that Hibernate is chosen to not have to understand the database or SQL. The interest in learning Hibernate in and out is nil. The result is an implementatiuon that frustrates everyone involved and developers hate Hibernate just slightly less than the DB they see as the reason Hibernate is so frustrating.

    How developers can stand not knowing when different queries will be issued is beyond me. I know it can be controlled, but that requires an interest to actually know Hibernate somewhat well. When the interest is to just be able to ignore the database and pretend that it does not exist it ends up being a frustrating piece of black magic that cannot be controlled.

    The fact that Hibernate is slower than a proper packagedriven implementation is not a question. But that is slower when it is exacuting, often the discussion is that it is more productive to develop solutions with Hibernate in pure development time. I know for a fact that in most projects I'd spend less time by writing packages myself than I do on supporting the developers using Hibernate and working around the limitations of Hibernate.

    So in many cases developing with packaged PLSQL would be more productive given good cooperation between Java and PL*SQL developers. It would also end up with a much more performant and scalable implementation.

    Without knowing SQL (the result of using Hibernate) developers end up being reliant on DBAs to mine data for a proper testcase where you need subselects, group bys, analytical SQL and so on to find the situation you need to test for. Supporting production is often very slow by those who cannot combine many SQLs or tables to get out exactly what they need in an adhoc troubleshooting situation.

    I know of no performance person that thinks that Hibernate SQL is a sure sign of a tunable solution. In fact, the turnaround time for tuning a hibernate query is typically at least 10x what it would be if it was straight SQL and available tools are reduced by about 99% when limited by an ORM such as Hibernate.

    In the end, if you care about performance you use tools that gives you access to 100% of the database performance ability. Hibernate cannot even be considered close in that case. A pure JDBC-coded implementation could be possible. For best performance there is nothing that beats (or even comes close to) PL*SQL packages written by performance experts.

  10. I encourage everyone to check out this post:

    "I don't understand the mentality of spending a twice the time to learn an ORM rather than learning the SQL of your current database system. You think you're moving faster in the beginning, but as time goes on debugging that ORM will change your mind... and then you try to rip it out and end up writing a blog post late at night ranting about ORMs and then...

    "I want to vault every ORM into the heart of the sun or, preferably, go back in time and smash all the computers responsible for their genesis. It's 2015, let's wake....up to the power of SQL and our relational systems."

  11. Though I am not a Java developer, I find this discussion very interesting.
    As one who has worked a lot on SQL optimization issues,
    I always supported the idea of thick applications,
    whilst the "pride" point of many managers at our place was to be
    firmly convinced that ALL the business logic should be located
    in the application alone ...
    And why ?
    Because of the eternal
    "What if tomorrow I, the overall-powerful-"manager", will simply decide to throw away Oracle and replace it with another database ?"

    I always felt that I should send such managers to read Tom Kyte's
    statements about "applications' database independence" ...

    But the conclusions of this thread also strongly "drive home"
    to the idea of the thick database and the value of the old good
    Oracle-knowledgeable database developer, and I am very glad of this :)

    Best Regards,
    Iudith Mentzel

  12. I was involved in a project where Hibernate was heavily used with Oracle DB. In most of the areas it was good, but when there was a complex requirement it failed to performed and I wrote pl/sql api and we were good to go. For me hibernate is good for simple projects but when you have some complex business logic, use plsql or sql.

    1. as in "Hibernate makes easy things hard and hard things impossible". (tm)

  13. I've worked with both PL/SQL and Hibernate extensively.

    I have always had good success writing PL/SQL APIs and calling those from Java or .NET. However, there are a lot more Java developers out there than PL/SQL developers, a situation that is not likely to change.

    Hibernate is a very impressive tool. But there are a some significant qualifiers.
    1. You understand it in depth. You must understand session state, transaction definition, object lifecycle, when to attach, when to detach, etc.
    2. You must understand what it means for objects to be eagerly or lazily loaded. You can easily get into situations with nasty cartesian joins when you eagerly load multiple child objects.
    3. You need to understand how/when to provide hibernate with your own, hand-crafted queries rather than allowing it to generate its own.

    In short, there's a significant learning curve. Most Hibernate projects run into trouble not because it's a poor tool, but rather because it's an involved tool that's solving a hard problem.

    Honestly, the problem of mapping object graphs (Java, .NET, Python, whatever) to relational databases is messy. There is no optimal way to map inheritance hierarchies -- it's all fairly situational.

    For myself, I would lean toward a toolkit that's closer to the SQL. Something a bit less of a closed box. I've heard good things about MyBasis, but I have not used it.

    1. Thanks, Scott. I really like your reasoned answer. As with so many other tools and features, it has at least as much to do with how it is used/abused as with inherent issues with the product.

  14. “There are a lot more Java developers out there than PL/SQL developers, “
    Very true

    “In short, there's a significant learning curve”
    Then the framework should only be used by hibernate professionals, I wonder
    how many of these are out there compared to DBA/DB Developers ?

    This is just not hibernate.Too me the frameworks in java only creates unnecessary complexity. Even the most basic application nowadays have XXX frameworks. Every new project have a newer better framework to implement…..
    The management of all these system are and will be a nightmare. It’s amazes me that we haven’t left this path long ago.

    This type of development also creates a database agnostic approach, let’s treat the database as a black box. Which in turn leaves us with quality and performance problem.We kill a lot of performance problems today with hardware, instead of building the application with scalability in mind. We will hit a harder roadblock sooner or later, since we are getting more and
    more data.

    Learn SQL PL/SQL seems like the shortest path to me. Use Rest, Use Apex, All supported.Use the product you bought. All developers i met knows SQL quite well which makes the path even shorter.

    No magic behind the scenes.