Skip to Main Content
  • Questions
  • Object relational impedance mismatch

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: August 06, 2009 - 12:11 pm UTC

Last updated: January 18, 2010 - 12:33 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked


What are your thoughts on the so called "object relational impedance mismatch"?

http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch

This seems to fuel the mentality that for an object oriented application to work people need to get away from the database as much as possible. The DB is not seen as an asset but as a liability or an inconvenience developers have to deal with. I think there should be a happy medium between how the overall application is designed, not everything should be in the application code, or the database but let each technology do what they do best.

Any thoughts on how to best confront this dilemma?

Thanks.

and Tom said...

... Any thoughts on how to best confront this dilemma? ...

never let any developers that write code in java, visual basic, C and the like write any code that has INSERT, UPDATE, DELETE or MERGE in it. Grant them access to a series of stored procedures that return the data they need - or process the transactions necessitated by the system itself.

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25405782527721

for my take on all of this.

Rating

  (60 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

O-R mapper

A reader, August 06, 2009 - 2:52 pm UTC

So, an object-relational mapper is not absolutely necessary to make an OO application work well with a relational database?

I'm asking because I'm trying to understand why the project I work on uses Hibernate. I was beginning to think the O-R mapper was necessary because the application can't "persist" data in the database as an object representation (which I think that's what object oriented databases claim to do).

Developers are not writing native SQL in their code, they write HQL which I've noticed doesn't support all SQL features and sometimes generates suboptimal SQL due to its limitations (but that's a differente matter!).

Perhaps the real reason behind using Hibernate is the mythical database independence?


Tom Kyte
August 06, 2009 - 3:04 pm UTC

... So, an object-relational mapper is not absolutely necessary to make an OO
application work well with a relational database? ...

No

... because I'm trying to understand why the project I work on uses
Hibernate. ...

because the developers feel SQL is something that is too hard to learn (but java, that was easy see...). That the database is a black box and we should remain independent from it. That the database an an encumbrance (they'd probably rather just store XML files if they could).

A reader, August 07, 2009 - 3:34 am UTC

Just to complete my information

"never let any developers that write code in java, visual basic, C and the like write any code that has INSERT, UPDATE, DELETE or MERGE in it."

and what about SELECT?

Thanks

Tom Kyte
August 07, 2009 - 9:43 am UTC

doh, I totally forgot that one - I usually start the list with it


never let any developers that write code in java, visual basic, C and the like
write any code that has SELECT, INSERT, UPDATE, DELETE or MERGE in it.

Michael Virnstein, August 10, 2009 - 7:57 am UTC

> never let any developers that write code in java, visual basic, C and the like
> write any code that has SELECT, INSERT, UPDATE, DELETE or MERGE in it.

Why should developers in java not SELECT? I could use readonly views to get developers read access to the basetables and let them write via procedures and functions. You once said "if you can do it in sql, do it in sql", so why should i use PL/SQL to read from the database?
Tom Kyte
August 11, 2009 - 1:50 pm UTC

Because as soon as they put SQL into their application, which we cannot see nor touch nor fix, they will say "database is slow, make it go faster"

even though it is because they are joining together 15 inappropriate views


You would be doing it in SQL - you would be putting the SQL into plsql so you can centralize it, manage it, tune it, have it written by someone that can actually spell SQL

I see nothing contradictory with the "mantra". I'm not saying "write really bad plsql that does "do it yourself nested loops joins" (that's the domain of many a java programmer)"

I'm saying write a plsql wrapper so we can insulate the developers from the heinous complexity of SQL (sarcasm on full)

A reader, August 11, 2009 - 2:56 am UTC

Developers always choose rows in tables are the objects they need to work with. But they should treat databases are the objects not the rows in tables. Use methods ( stored procedures ) to access the database objects to get or to set data. Then no more OR mapping. In OOP, developers don't want to know the detail implementation within an object. developers should use the same concept to treat databases.

No Object Oriented Language, therefore no ORM

Nicholas Sushkin, August 11, 2009 - 3:48 pm UTC

Tom,

I think what you're saying is equivalent to recommending non-object language (PL/SQL)
for business logic, eliminating the need for Object-Relational Mapping.

If you write your stored procedure in Object Oriented Java, you're back to ORM.
Tom Kyte
August 13, 2009 - 9:10 am UTC

well, if you look at it, plsql has lots of the feature set of so called "OO things".

If you write your stored procedure in "OO Java", you are not back to an ORM at all, I fail to see how "using OO java" implies "must use ORM"

DBAs Extreme Views Due to Past

Jimmy C., August 11, 2009 - 5:16 pm UTC

Can a Java developer add to this conversation? We have many Java developers, but only two DBAs. There is no way our schedule can handle waiting for overloaded DBAs to place our SQL into stored procedures, grant access, etc.

I think Hibernate has really pissed off a lot of DBAs, and rightfully so. But know that it isn't the only game in town. Ibatis SQLMaps, for example, stores all the SQL in XML files that DBAs can easily review. And the technology doesn't add a new SQL flavor like Hibernate's HQL. This should be a fair compromise. And way to stay professional with the insults. That'll help bridge the DBA-to-software developer divide.
Tom Kyte
August 13, 2009 - 9:13 am UTC

Maybe, just maybe, some of the java developers needs to

a) retrain and be taught what a database is, does and how they work so they can provide this interface

b) be replaced by someone that does.

just because you have 500 people that don't do databases and 2 that do doesn't imply, infer, mean you should keep that structure. perhaps the structure is wrong.

Add Vantage, IMHO

Duke Ganote, August 12, 2009 - 11:26 am UTC

At the risk of a sweeping over-generalization, most application software has, well, a distinct application-focus rather than enterprise-focus. I can google some references to "Enterprise Objects" or "domain models", but most applications are inherently "stovepipes".

However, a relational model is more flexible for reporting purposes because you can "invert the access paths" used by the application. Reporting often demands that access inversion because the vantage shifts from stovepipe to enterprise.

Java developers unable to multithread?

Jim, August 12, 2009 - 12:43 pm UTC

So Jimmy for Iowa are you saying that Java developers are not able to learn pl/sql? That pl/sql is too complex for them? That seems rather odd.

DBAs Extreme Views Due to Past, pt 2

Jimmy C., August 12, 2009 - 2:36 pm UTC

My response wasn't about PL/SQL -- but rather an alternative ORM that places Java-developer SQL into an XML file instead of the Java source code. This way DBAs can review the SQL easily (if they have desire) and the Java developer can be less dependent on the DBA to install procedures for us, grant us access, etc. The original question asked for 'happy medium' so I wanted to point out one such solution.

Java developers can indeed learn PL/SQL, and many have. Speaking for myself only, I don't really like to use vendor specific technology if possible in case someday my clients want to switch databases. I also think that Java is better (easier to read/easier to maintain/nice library support/etc.) for logic/processing code than PL/SQL. Further, with computationally intense operations I prefer to off load the DB as well if it will improve overall system performance. In other words, I am in agreement with the original question's point that each technology should do what it does best.
Tom Kyte
August 13, 2009 - 12:08 pm UTC

... I don't really like to use vendor specific technology if possible in case
someday my clients want to switch databases. ....

which means you won't ever use the thing they paid so much money for. And, you won't understand what it means to even switch databases - since you don't understand how the various databases implement concurrency controls (radically differently).

... Further, with computationally intense
operations I prefer to off load the DB as well if it will improve overall
system performance. ...

If I had a nickle.... "we don't sort in database, we offload that", "we don't join in database, we offload that" - in short, you offload so much you end up KILLING the database (with billions of small keyed reads)


The database processes data many orders of magnitude faster and using less resources than java (or c or VB or language X). So, if you really think "use each technology appropriately", you would use the HECK out of the database you are implementing on.

Fernando, August 12, 2009 - 2:53 pm UTC

I like idea of letting each component do what they do best. But regarding app logic being split in two layers? I have seen many applications doing online processing from app layer and batch processing on db layer. As a result, business logic is duplicated !
In any case one has to be pragmatic when deciding how to split the business logic. Sometimes it is better to put everything on app layer, sometimes on db layer and even sometimes in both ! It all depends on size ,use, company, staff and many other factors.
Tom Kyte
August 13, 2009 - 12:08 pm UTC

not really, if you put it into the database you can use it and reuse it from any other layer - anywhere - regardless of language.

From The Daily WTF ...

David Aldridge, August 13, 2009 - 6:19 am UTC

My new favourite example of why you provide an interface to the database instead of letting developers write SQL.

http://thedailywtf.com/Articles/A-Confusing-SELECTion.aspx
Tom Kyte
August 13, 2009 - 12:17 pm UTC

believe it or not, I have that linked on my desktop and was planning on blogging it :)

Good Staffing Needed

Gordon C., August 13, 2009 - 8:50 am UTC

Jimmy C.:

"We have many Java developers, but only two DBAs."
You have a staffing issue. You are doing database development and have NOT ONE database developer on staff. ("DBA" is a different skill set. Writing SQL and PL/SQL IS software development.) That's like trying to build a hotel with no electricians and having the painters try to do the wiring.

"... place our SQL into stored procedures"
You've already off course. You should not have Java-developer-written or OO-tool-of-the-week-generated SQL in the first place. When you fix your staffing problem, the database developers can write good SQL and PL/SQL to begin with and you will save all that time you waste reviewing and reworking bad code.

You can fix the problem by training as well as by hiring, but once you do, you will no longer call people "Java developers" or "X developers", but just "developers". Even better is if the developers know more than one DBMS. Then they will understand that there is no such thing as "SQL" but rather "SQL as interpreted and run by a given DBMS".

Aside from the unreadability of XML, I don't see how any product can write out "all the SQL" when so much of it is modified at run time, with (bindless) where clauses tacked on.

Tom's sarcasm is the result of thousands of programmers and IT managers flatly refusing to understand the technology they are using or to learn how to use it effectively. (Talk about being non-professional!) The problem has reached epidemic proportions where I work, where entire projects are led by Java programmers who don't know the difference between a DBMS and a disk drive. And then the programmers work overtime doing things like auditing or history that could be set up in minutes in the DBMS.

As for database independence, you're probably going to switch from Java to Ruby on Rails to something else long before you switch DBMSs, especially if you want to avoid "vendor-specific technology", such as SQL Server or Sybase. (And I don't suppose you use any flavor of Windows?) It's best to minimize the code base that is most likely to change.


Jim from Oregon:
I think the problem is the opposite - programmers who think they know PL/SQL but only know its syntax.

Database agnostic

A reader, August 13, 2009 - 11:32 am UTC

The proper way to support multiple databases is an abstraction layer under which each database has a fully-developed and feature-specific implementation.

DBAs Extreme Views Due to Past, pt 3

Jimmy C., August 13, 2009 - 2:30 pm UTC

Gordan C.:

"You have a staffing issue. You are doing database development and have NOT ONE database developer
on staff."

Of course we have staffing issues -- welcome to IT development. We do actually have PL/SQL and Oracle Forms developers, note. For whatever reason, however, we get more highly skilled optimization input from our DBAs. And the DBAs are the gatekeepers in our environment. Outside my personal schema, I can't get a DB table change or a DB procedure added without having the DBAs deploy it.

"You should not have Java-developer-written or
OO-tool-of-the-week-generated SQL in the first place."

Ibatis SQLMaps was started in 2001, and unlike some XML schemas, its XML schema so straight forward that a DBA who has never read one doc on it would be able to easily read the SQL. And in our environment, we need developers that can wear multiple hats in order to get anything done. The only specialized people in our staff are the DBAs and database developers. None of them know Java or OO to the level we know SQL, for example.

Even though we have what I labeled as "Java developers", we do consider ourselves general software developers with most of our staff having degrees in computer science. I added in 'Java' to emphasize the OO angle in relation to the original question. Further, it is rare (less than 1% of our queries) that our database developers need to improve our SQL, but when they do, it is appreciated.

"Tom's sarcasm is the result of thousands of programmers and IT managers flatly refusing to
understand the technology they are using or to learn how to use it effectively."

I am very skeptical of any argument that always blames the other side. I think there is a bit of arrogance and ignorance from both sides of the argument which prevents clear discussion. I also think there are some turf protection issues that fuel some emotions.

"...where entire
projects are led by Java programmers who don't know the difference between a DBMS and a disk drive."

It would be hard for a Java developer to have a respectful conversation with a database developer who actually made the statement above.

Tom Kyte
August 24, 2009 - 7:10 am UTC

...It would be hard for a Java developer to have a respectful conversation with a
database developer who actually made the statement above.
....

if it were true, why? I find it time and time again...

Good Staffing Still Needed

Gordon C., August 14, 2009 - 1:58 am UTC

I didn't say that ALL Java programmers are ignorant of the distinction between DBMSs and disks, just many of them around here, and my remark is quite true. I have had more than one conversation with a project lead who insisted on loading up as much data as possible onto application servers and avoiding SQL at all costs because they thought that executing SQL = disk I/O. And one thought he was doing the DBA a favor, "minimizing" the load on the DB server by querying for only one row at a time. These are people who claim to know SQL.... That's just an example of the kind of thing that drives database specialists nuts. I have not seen anything that indicates that ignorance runs the other direction to that degree.


"We do actually have PL/SQL and Oracle Forms developers, note."

... who could be writing the stored packages, grant statements, etc., not just the two DBAs as you had stated originally....


"Outside my personal schema, I can't get a DB table change or a DB procedure added without having the DBAs deploy it."

If that includes the development environment, it does seem overly restrictive. But developers can write the code, including any deployment sql*plus scripts; it only take a few seconds to deploy it. (And I recently worked at a place where Data Modelers - a third group - had to write all the DDL scripts for DBAs to run, even for application temp tables. Things turned around well enough.) As for deciding whether to make that design change, normally a DBA should not really care what objects are in the schemas - that would be up the the system designers (which on a small project could well be the entire development team). And with a good data access layer, one doesn't need to care about the actual table structure. Packages can encapsulate all that and the O-R "mismatch" goes away.


This all gets back to the real issue underlying the original post: once one has chosen a system architecture, staff up for that achitecture and write specifically for it in order to use it effectively. You may be much closer to that than many people in this region, but I still agree with Tom that if one uses each technology most effectively, there would be no SQL outside the DBMS.

Tom Kyte
August 24, 2009 - 7:28 am UTC

... And one thought he
was doing the DBA a favor, "minimizing" the load on the DB server by querying
for only one row at a time. ....

I think they are taught that, based on the number of times I've encountered the same exact thought.

A reader, August 14, 2009 - 5:02 pm UTC

You can achieve database independence by having a wrapper in Java/ VB / Front End that makes a call to the database object (aka Package / Proc / Function , ... ) .
When you really switch the database (!) , you can change the setting in your properties to point to the appropriate dbms
and access its db object via the wrapper .

You can't treat Porsche and Ford the same ...

Michael Virnstein, August 14, 2009 - 5:05 pm UTC

> Because as soon as they put SQL into their application,
> which we cannot see nor touch nor fix, they will say
> "database is slow, make it go faster"

I think this is all about comunication between DBA and developers. And if developers are well trained in SQL and in the DB they use, i see no problem in letting them do SQL in any application.

> I'm saying write a plsql wrapper so we can insulate the
> developers from the heinous complexity of SQL (sarcasm on
> full)

I really see no point in creating a plsql wrapper for every possible sql. I can see that using OR-mappers will result in suboptimal sql, but disallowing the usage of all SQL outside of the DB seems quite exaggerated to me.
Tom Kyte
August 24, 2009 - 8:02 am UTC

... And if
developers are well trained in SQL and in the DB they use, ....

ahh, but therein lies the rub.....

... usage of all SQL outside of the DB seems quite exaggerated to me. ...

show me the place where the developers are well trained in SQL and the DB they use and I'll start agreeing with you.

Oleksandr Alesinskyy, August 16, 2009 - 5:18 pm UTC

I would rather disagree with many statements in this very discussion.

1st of all Hibernate - if properly used - is not evil or some kind of brake applied to the application performance. For many kind of applications it is possible to produce a better performing application with Hibernate then with e.g. plain JDBC (or iBatis) - partially due to advanced caching, SQL statement batching (automatic!) and so on.

Definitely, there are tasks for which it is less suitable - or even unsuitable.

Neither Hibernate requires placing SQL in the application code - it may (and typically should) be offload to the XML configuration, not it requires usage of HQL - if you see that your HQL operator performs not so well you may replace it (with very reasonable efforts) with SQL native to your DB. As well it is absolutely possible to mix and match Hibernate code with the plain JDBC.

The real evil is to think that some tool (does not matter which, you may name any) would automagically do your work.

If you use some tool you have to understand what it do best, what worst and use a right tool for a right job.
Tom Kyte
August 24, 2009 - 8:40 am UTC

... 1st of all Hibernate - if properly used - ...

agreed, however, the vast majority of the times I see it, it is being used by people that do not understand it, nor do they understand transaction processing.

... partially due to advanced caching, SQL statement
batching (automatic!) and so on.
....

all features for many many releases of PLSQL....

... The real evil is to think that some tool (does not matter which, you may name
any) would automagically do your work.
....

and you have just described the majority of the users of ORM's

Auto-generated schemas

Captain Teeb, August 21, 2009 - 4:38 am UTC

Hi Tom and everyone,

I am currently working at a site using Hibernate, where I am the one "Oracle guy". We have schemas generated entirely by Hibernate, with no data model (but a nice UML model). This has been OK during development, but is starting to present mysterious performance problems.

When I went to CS school in the mid 90s we learned that a big advantage of relational/SQL DBs is that they are "loosely coupled", that is, the data model makes sense regardless of the applications using it. This makes it easy for multiple applications to access the same data.

With auto-generated schemas we are back to the pre-relational days, when application and data were inseparable, and the only way to the data was through the single application that built it. So was loose coupling just a fad?
Tom Kyte
August 25, 2009 - 8:48 am UTC

... present mysterious performance problems. ...

why mysterious? it is to be expected

... So was loose coupling just a fad? ...

what goes around comes around I find.

client server was going to decentralize everything, the methods of the 1980's (mainframe, virtualization, centralization) was all wrong - power to the people

That didn't last very long did it - we are back to mainframe concepts, using virtualization, in a highly centralized fashion.


Your observation is one I've made - that a data model made for an application is a rather bad idea - because applications come and go rapidly but the data - the data lives forever.

Performance problems

Enrique Aviles, August 22, 2009 - 12:38 pm UTC

Captain,

Can you share a few examples of the "mysterious" performance problems you mentioned on your post?

Thanks
Tom Kyte
August 25, 2009 - 9:03 am UTC

performance stank is what they meant to say, it isn't mysterious - the developers just didn't have any idea what was happening so to them it was a mystery


Loz, August 24, 2009 - 8:31 am UTC

"....performing application with Hibernate then with e.g. plain JDBC (or iBatis) - partially due to advanced caching..."

Automatic caching = stale data. Only if you know your data at the application level can you know if you can cache it and you always need to consider if it can be out of date in a multi-user environment. Only your database can effectively cache without human decision (which is how I would define advanced)

"...SQL statement batching (automatic!) and so on."
It's called a stored procedure.

... (and typically should) be offload to the XML configuration..."

Why on earth do you think client side XML is better than a stored procedure or package? Oracle provides both and they are designed to not just store queries, but code, execution plans, manage dependencies, protection, abstraction etc. Would you store the classes in your java code in XML? You could argue the same points if you were equally unknowledgable about Java.

ORM and database API

Charlie MuJiang, August 25, 2009 - 8:16 pm UTC

After rewrite our inventory application with database API, remove all the row-by-row (slow-by-slow) process, wrap up the data process logic into database packages, reduce the network traffic, reduce the database resource contention,
now we can handle 10 times more transaction volume(throughput) than before, and reduce the turn over time, sell more books.

The number is speaking. Discard ORM is really really sweet.

To: Loz

Oleksandr Alesinskyy, August 26, 2009 - 10:21 am UTC

"Automatic caching = stale data."
Definitely not true - for many reasons.

1st of all if all your users come via an application server then it is perfectly capable to cache the data. As for DB-level caching it is useful but not always sufficient as database (Oracle) caches data blocks and not processed results (if we left alone 11g result cache).

2nd - stale data may be tolerable - it depends on application.

3rd - by default Hibernate is quite conservative in caching.

"...SQL statement batching (automatic!) and so on."
- It's called a stored procedure. -
It is not. A stored procedure should be pre-created, Hibernate batches statements dynamically and then sends them to DB in one shot (via JDBC batch update feature). It ensure much higher flexibility. Definitely, if statements are known in advance stored procedure is highly preferable - but it is not always a case.


"Why on earth do you think client side XML is better than a stored procedure or package?"
Due to the higher flexibility - which is sometimes important. Especially when we speak about data retrieval and not about data manipulation.

"You could argue the same points if you were equally unknowledgable about Java." I|m sorry, but I'm convinced that I'm much more knowledgeable about both Oracle and Java then you.
So I can see both sides of a problem, while you see only one.
Tom Kyte
August 26, 2009 - 7:29 pm UTC

... (if we left alone 11g result cache). ...

that would be a wrong thing to do...

... 1st of all if all your users come via an application server then it is perfectly capable to cache the data ....

not if you have N copies of your application server running it isn't.


...
It's called a stored procedure. -
It is not.
...

yes, it is, you saying "no it is not" does not make it so.

... Especially when we speak about data retrieval and not about data manipulation. ...

how do you do data retrieval (or maybe a better question is "why do") without data manipulation??!?!?!?!

If an Oracle Hammer is your only tool...

Jimmy C., August 26, 2009 - 11:07 pm UTC

Tom wrote in response to my comment about off-loading the DB:

"If I had a nickle.... "we don't sort in database, we offload that", "we don't join in database, we offload that" - in short, you offload so much you end up KILLING the database (with billions of small keyed reads)"

When I mentioned off-loading, I meant business processing with business logic. Obviously I would let the DB do sorting and joining as that is what it does best. I can't figure out if you are making up these uhm, 'real-life' Java people to bolster your argument or if you just choose to interpret my text the most negative way to suite your point.

To Oleksandr Alesinskyy about Hibernate:

I agree with many of your points except your thoughts on Hibernate. Hibernate can work well if you become a guru with it -- but why put yourself and your team through the pain for what turns out to be little gain. I think any technology that has such a large ramp up time ultimately is designed poorly. Simplicity and transparency are why I like Ibatis over Hibernate, and I am sad to say that I think Hibernate complexity leads to non-gurus making poor solutions causing performance problems and bugs. Hibernate gives other ORMs a bad name which people here fixate on.

General comment:

I can tolerate putting all SQL into the database instead of XML -- it's not that big of a deal to me nor does it gain us much either way technically. Any performance issues (excluding data warehousing) we've had would have also shown up in the same SQL had they been in a procedure -- so in our case the issues weren't the ORM but rather the SQL/indexes/etc. At the end of the day, however, I have to make deadlines. More often than not in my dev environments I have to wait on a DB developer or DBA to get my stuff approved, version controlled, and deployed. I can't control how many people we hire or their skill level -- but with an ORM solution I can control the SQL I need, the version of it, and how it is packaged. I also get nice ORM time saving aides such as dynamic SQL, Object-to-Table/SQL mapping, etc. ORMs are still a young technology and greatly improving, and they will lead to the happy medium proposed by the original questioner. Most of the discontent here I am going to chalk up to exaggerated misinformation and petty turf war emotions.
Tom Kyte
August 28, 2009 - 4:24 pm UTC

... I meant business processing with business logic. ...

you will have to define that for me, because if it involves DATA LOGIC, it belongs in the database.

If it involves "making the data look pretty for the end user", then yes, it is business logic.


... Obviously I would let the DB do sorting and joining as that is what it does
best. I can't figure out if you are making up these uhm, 'real-life' Java
people to bolster your argument or if you just choose to interpret my text the
most negative way to suite your point.

...


UMMM, I meet them everyday. They are not made up.


You can read above from other posters, other than me, that the developer that never touches the database documentation is the bane of our existence, they will scream loudly "database is slow" when they haven't read one page of documentation and in fact - it is their approach to using the database as a bit bucket that is killing us all.


There are too many horror stories to even consider wasting time "making stuff up".

... I can tolerate putting all SQL into the database instead of XML -- it's not
that big of a deal to me nor does it gain us much either way technically. ...

that indicates to me ("nor does it gain us much") that you haven't been doing databases for a long time. Your application - the thing you are so focused on today in 2009 - it won't be around in 2013. But the data will. And if you've hidden everything about the data in your obsolete application written using dead languages, with frameworks no one would consider using anymore (remember, it is 2013 not 2009 now) - you will force us to rip it all out and start again.

put it in the database and whatever comes along - can use it.

put it in the database and I can tell you who is using what.

put it in the database and I can actually help you make it work better.

put it in the database and we can change it in a single place and immediately have it (the fix) propagated to the dozen different applications that need it.

put it in the database and whatever new whizz bang "this is the last application development environment we'll ever need" comes along can use it.

... Most of the discontent here I am going to
chalk up to exaggerated misinformation and petty turf war emotions. ...

Or hammers (the tools) being used by people that have screws and never read about screwdrivers - it could be that too.

Performance Anti-Patterns in Database-Driven Applications

Charlie MuJiang, August 27, 2009 - 12:48 pm UTC

Excellent article

Enrique, August 27, 2009 - 5:43 pm UTC

Charlie,

Thanks for posting the link to the "anti-patterns" article.

I think the keyword is misuse. Any component in a software application that is not used/implemented correctly stands a great chance of causing performance/scalability problems. Notice that I said *any* component, I'm not solely focusing on an ORM tool. But, unfortunately, more often than not the latest and greatest and less understood technology is misused and in this case ORMs fits the bill. Marry this new technology mentality with the illusion that and RDBMS is "old" technology that has not being able to keep up with OO trends and you get disastrous results.



Tom Kyte
August 28, 2009 - 5:09 pm UTC

This is the same reason I would like to

a) have triggers removed from the database, obsolete the feature, make it go away
b) remove "when others" from the language PLSQL
c) get rid of autonomous transactions


When used correctly (which means triggers are FEW and FAR between and very simple in nature, which means that WHEN OTHERS is followed by RAISE eventually, which means that autonomous transactions are used solely in error logging routines) - they are great.

But, they are so widely abused, mal-used, mis-used, misunderstood, incorrectly applied - that I know the quality of software developed against Oracle would improve immediately by orders of magnitude without them.


@Oleksandr:

Loz, August 28, 2009 - 11:54 am UTC

@Oleksandr:

"1st of all if all your users come via an application server then it is perfectly capable to cache the data....".

Then:

"2nd - stale data may be tolerable - it depends on application."

So which is it? How does your application server know the difference any more than the database? Someone/something needs to know and make the decision as to whether stale data is ok. You need to build the logic somewhere and usually generic out-of-database solutions that cannot not enforce data integrity are too general (and therefore wrong) unless specifically tuned. I agree though, that something like a list of countries can be cached, but that's about where I would draw the line unless I wasn't too bothered about integrity (e.g myspace, blogs and stuff like that).


"As for DB-level caching it is useful but not always sufficient as database (Oracle) caches data blocks and not processed results (if we left alone 11g result cache). "

We are after fast results, not how they are attained. And as Toms says, why would you ignore a result cache?

"I|m sorry, but I'm convinced that I'm much more knowledgeable about both Oracle and Java then you.
So I can see both sides of a problem, while you see only one. "

I cannot see how you can come to that conclusion. You do not know anything about me, my background or my experience, yet you have drawn conclusions based on your existing beliefs combined with (at best) limited facts. I think I am begining to understand where you are coming from ;)

@Loz

Oleksandr Alesinskyy, August 29, 2009 - 7:54 am UTC

@Oleksandr:

"So which is it? How does your application server know the difference any more than the database?"
Because application knows purpose of the data - and database does not (unless we speak about PL/SQL code - but such code typically performs no caching.

"Someone/something needs to know and make the decision as to whether stale data is ok."
Yes, and only application logic can do it.

"...unless specifically tuned."
Yes, it it is much easier to "specifically tune at application level - it is practically impossible to tune on the database block-level cache, especially if more then one application run on the same DB instance.

"unless I wasn't too bothered about integrity (e.g myspace, blogs and stuff like that)." That's true - the my point was - right from the start - "use right tool for your task", it seems that you completely misunderstood my original post.

"We are after fast results, not how they are attained. "
Exactly - the problem is that obtaining a result from database blocks may be expensive - even when blocks are cached. It might be more feasible to cache application-level objects (note that "might" - it highly depends on the application).

"And as Toms says, why would you ignore a result cache?"
Because I (as many others) do not use 11g right now - and decision to migrate (or not) is out of my remit.

"I cannot see how you can come to that conclusion."
From your post - solely. And, BTW, you were first who moved in this direction :) Here is the quotation " ... were equally unknowledgable about Java.". So you are in the same shoes :)

"I am begining to understand where you are coming from ;)".
Would you like to explain it to me ? :)

Tom Kyte
August 29, 2009 - 7:13 pm UTC

... Yes, and only application logic can do it.... hah, usually not, many times I find the developers *have no idea* if it can or cannot. They assume.


... Yes, it it is much easier to "specifically tune at application level - ...

if you had one application, I'd agree with you (and you don't, no one does). But you know what happens when you do it at the application level? You end up "sharing data", this is "my data", that is "your data", we have the "same data" - enter replication, syncronization, distributed mess - and poor performance (not due to the replication but because the developers don't know the database and cannot tune in the first place - if that were not true, we would NOT be having this discussion in the first place - everyone on the planet would have said 'oh, we did it and it was GREAT')



I'm very knowledgeable about java and more specifically application development (forget language, the language isn't really relevant). And I tend to agree more with the other poster.

The problem is - far too many people, people in 'charge', are not capable of using the tools correctly. They don't know what they are doing database and come to the conclusion "database bad". They are the bane of my existence on this planet.

Oleksandr Alesinskyy, August 29, 2009 - 8:07 am UTC

>>... (if we left alone 11g result cache). ...

>that would be a wrong thing to do...

I meant that in pre-11g such functionality does not exist -not that it should not be used. Sorry, that I did not make me more clear.

>>... 1st of all if all your users come via an application server then it is perfectly capable to cache the data ....

>>not if you have N copies of your application server >>running it isn't.

There are cluster-wide caches (another story that I seriously doubt that they would outperform DB).


>>...
>>It's called a stored procedure. -
>>It is not.
>>...

>>yes, it is, you saying "no it is not" does not make it so.
Stored procedure is static, predefined sequence of statements - and batching to which I have referred is dynamic.

>>... Especially when we speak about data retrieval and not >>about data manipulation. ...

>how do you do data retrieval (or maybe a better question >is "why do") without data manipulation??!?!?!?!

Application, the are read-only for the vast majority of the users are not so rare.
Tom Kyte
August 29, 2009 - 7:17 pm UTC

... There are cluster-wide caches (another story that I seriously doubt that they
would outperform DB). ...

exactly.


... Application, the are read-only for the vast majority of the users are not so
rare. ...

and read only doesn't manipulate data? I think you and I disagree on what it means to manipulate date



select ....
from t1 join - outer, whatever t2 join ... t3
where <using every feature known to sql, analytics, model, whatever>
group by ...
order by ...


that is read only
it is data manipulation
add things like pivot, unpivot, etc - and you are definitely "manipulating data"

everything that touches the database is manipulating data in some way, in some fashion - and they'd do it better if they just picked up a document (a database document)

Oleksandr Alesinskyy, August 29, 2009 - 9:03 am UTC

Hi Tom,

You have written to Michael Virnstein "show me the place where the developers are well trained in SQL and the DB they use and I'll start agreeing with you. ".

Such places exist, and I'm working in one of them, NAVTEQ.
BTW Michael is (as well as I am) from Germany, and it seems to me that there it is not so uncommon :)
Tom Kyte
August 29, 2009 - 7:17 pm UTC

sounds like heaven

Loz, August 30, 2009 - 8:15 am UTC

Here is the quotation " ... were equally unknowledgable about Java.".
Ok - I now see where you think I am coming from. What I actually meant by that is "If somebody who does not know DBs thinks putting SQL in XML is a good idea then somebody who is as equally unknowledgable about Java might make the same mistake and start putting Java code into XML 'for more flexibility'".
It's a comparable situation and both are equally crazy in my opinion. It was not meant as a personal insult on you and I am not going to rise to your bait any further.
The fact that at some places it appears to be ok to use a DB witout understanding them is as strange as allowing someone to develop code (in Java or whatever) without understanding it either but it is quite widespread.
For the record, I have almost 25 years in IT under my belt, including a fair amount of Java, and I like to think this gives me the experience to evaluate technology tools objectively according to their merits.

Oleksandr Alesinskyy, August 30, 2009 - 1:04 pm UTC

"read only doesn't manipulate data? " - OK, I have formulated it wrongly - English is not my native language.

@Loz

Oleksandr Alesinskyy, August 30, 2009 - 1:24 pm UTC

"For the record, I have almost 25 years in IT under my belt, including a fair amount of Java, and I
like to think this gives me the experience to evaluate technology tools objectively according to
their merits."

And for me it would be 30 years in next February :) , all of them with DBs of some kind, last 15 years with Oracle DB.
And 6 years of an active Java development. So my experience is at least comparable with yours.

And I do not understand why you believe that externalization of queries - or for that matter Java objects - to the configuration is a bad thing? It is typical dependency injection (and it absence, at least in the natural way, is one of the most important PL/SQL drawbacks in comparison to the most modern languages). Have you ever heard of Spring? I'm sure, the answer is yes.

But to be honest, this discussion becomes not very related to the original topic.

To summarize it -

Select a right tool (and an approach) for your task.

There is no one tool that suits all needs.

Db is asset, not liability (I never stated otherwise - you misinterpreted my post) and should be used to the last bit possible. :)

Hibernate (or other ORM mapper) is not evil by itself - its misuse (and overuse) is evil (but the same applies to any tool, while currently ORMs are misused quite often).



Cultural mismatch

Enrique, September 02, 2009 - 8:40 am UTC

I think the different mentality between OO developers and database professionals is described here:

http://www.agiledata.org/essays/culturalImpedanceMismatch.html

I'd say possibly the number one factor that drives any tool misuse is having a team divided between an "us versus them" mentality. If the entire team comes together to appreciate the strength of each tool/application/language and works together to leverage those strengths the end result will be a well disigned high performant application. But, that would sound "like heaven" as Tom replied to another poster.

some inputs

jv, September 07, 2009 - 8:04 am UTC

Dear Tom,

good day to you, I am working on a project which has all the business logic and code to access the Database in Java and it's using hibernate.

While the discussion for moving data access part to database procedures are going on I want to share my findings with the team but I thought to get your inputs on the same.

1.since customization is not done for fetching data in hibernate it's fetching all the columns of the table whether they are required or not. I want to show them using autotrace that columns not required for processing should not be fetched as it results in lot of data being sent from DB server to Webserver, which is not been used.

2. Also there's use of "left outer join" where natural join should have been used. I am working on the test case to show that "left outer join" is not bad but should not be made thumb rule.

Requesting your inputs on this please.

Thanking you for your help and time on this.

Regards,
JV

Tom Kyte
September 07, 2009 - 11:04 am UTC

... I am working on a project which has all the business logic and
code to access the Database in Java and it's using hibernate.
....

I am working on a project which has all the business logic and code to accss the database in Cobol and it's using modular coding.

Your words will sound as cool as that someday soon....


1) not just the overhead of returning the data over the network, but also the fact that if you select only what you need - query plans can be more efficient - much much more efficient.

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select * from all_objects order by dbms_random.random;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(owner,object_type,object_name);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.



ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select * from t where owner = 'SYSTEM' order by object_type, object_name;

425 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 961378228

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2099 |   190K|   160   (2)| 00:00:02 |
|   1 |  SORT ORDER BY     |      |  2099 |   190K|   160   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| T    |  2099 |   190K|   159   (2)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYSTEM')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        697  consistent gets
          0  physical reads
          0  redo size
      26264  bytes sent via SQL*Net to client
        708  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        425  rows processed


ops$tkyte%ORA10GR2> select /*+ first_rows */ * from t where owner = 'SYSTEM' order by object_type, object_name;

425 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2099 |   190K|  2115   (1)| 00:00:26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2099 |   190K|  2115   (1)| 00:00:26 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  2099 |       |    17   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYSTEM')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        460  consistent gets
          0  physical reads
          0  redo size
      26264  bytes sent via SQL*Net to client
        708  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        425  rows processed

<b>Index or not - a query that selects only what it needs can skip sorts, use indexes more efficiently, skip going to the table altogether</b>

ops$tkyte%ORA10GR2> select owner, object_type, object_name from t where owner = 'SYSTEM' order by object_type, object_name;
425 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |  2099 | 81861 |    17   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_IDX |  2099 | 81861 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER"='SYSTEM')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
      14595  bytes sent via SQL*Net to client
        708  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        425  rows processed

ops$tkyte%ORA10GR2> set autotrace off


which of those three queries would you like to use day in and day out?


2) tell them point blank "it is stupid of you to outer join 'just in case' every time. STUPID - not 'bad idea', but plain STUPID. You will remove the ability of the optimizer to pick a driving table in most cases and in cases where the predicate makes the outer join 'not necessary', just makes you look STUPID"


thanks for your reponse

jv, September 07, 2009 - 11:27 am UTC

Dear Tom,

thanks a lot for your response to the above query.

Regards,
jv

Case-in-Point: 1990 Data in 2009

Ted, September 12, 2009 - 5:56 pm UTC

Hi,

I found this "conversation" while researching Hiberante and it's potential impact on my organization's databases. I can confirm that data outlasts development "paradigms" In Oct., 2007, we finally moved our primary application from Alpha OpenVMS/Oracle7/Oracle Forms 4.5 to RedHat Linux/Oracle10g/Oracle Forms 10g (via Oracle Application Server). The only thing that did not need to be changed during this was the data - we were able to use a combination of exp and ftp to safely transport all of the data and complete the data migration.

I can also confirm that putting "database functionality" into application code can result in defacto "legecy systems" which depend upon a specific technology (which is, by itself, not portable) to operate. The reason our organization stayed on Oracle7 for so long was because we had a very compilicated Pro*C UserExit program which was doing the bulk of the "Business Logic" but after the consultants who built it left, everyone was afraid to touch it because they could not figure out how it worked. Since the Pro*C Userexit had been compiled using OpenVMS - by this time defunct with HP/Compaq providing per-phonecall type of support - We could not just port it to Linux.

During this, I took a closer look at this program and I found that it was trying to use internal C arrays to store values from the code tables to "speed things up". In other words, the C Developer tried to build a mini-RDBMS within the program (there was other "DBMS-like" code.)

We subsequently had to spend alot of money to have this program re-written into PL/Sql so that it could fit in with the Oracle-forms upgrade from 4.5 to 10g.

It took us so long only beacuse someone hard-coded the business logic into a hard-to-understand 3GL program.

My shop is now looking at rebuilding the system using Java and I'm seeing the same thing happening again. Why do these people keep trying to nre-invent SQL and re-do the 20-30 years of effort put in.

Ted
Tom Kyte
September 14, 2009 - 1:11 pm UTC

... Why do these people keep trying to nre-invent SQL
and re-do the 20-30 years of effort put in.
....

because we have grey hairs and they do not for the most part.


Chuck, September 14, 2009 - 5:32 pm UTC

Personally, I think it's because they have taken Java classes but not Database Theory.
If all you have is a hammer, everything looks like a nail.

Persistant Database Connections

A reader, September 15, 2009 - 9:17 pm UTC

I hope this is not considered a new question.

The Java application being developed in my shop is
only to be used internally by less than 500 staff.

The Java developers say we have to go with an "Internet-level"
non-persistant database connection approach. That is - one database account
for all users and a fixed set of application-controlled database sessions.

I do not agree with this but I am not sure how to show them
that moving all of the row-level locking and cursor management
to Java will turn this into a nightmare for everyone.

I should mention the Java application is replacing two existing database
applications which are already using persistent connections without any
performace issues.



Tom Kyte
September 16, 2009 - 9:42 am UTC

for 500 users, I too would go stateless.

You would use what is called "optimistic concurrency control", a well known- documented and classic approach that works well if consistently implemented.

The single user approach doesn't need be, they can use database users (search documentation for

n-tier proxy

They would not be doing row level locking in the application, they would be using "lost update detection" - very common, very good.

They would not be doing cursor management - they would be using cursors just like anyone else, not sure what that means?

that would still be stateless "internet-level" (what a silly bit of nomenclature truly - it is just called "stateless")




If done correctly, this stateless implementation will

a) run on a fraction of the hardware your existing one is running on
b) not be a night mare


The goal should be to get them to consider n-tier proxy authentication so you can audit, use fine grained access control, understand who does what, control access control IN THE DATABASE (that is something they would be massively re-inventing), and allow users to use the database with tools other than that single application (which will be retired in a few years)


If they use n-tier, users would still be users, application would still be stateless, and you would still have the full power of access control, grants, security - everything - even when the database is accessed NOT via the application.

RE: Persistant Database Connections

Ted, September 16, 2009 - 8:43 pm UTC

Thank you for this. I will be following up with my java co-workers.
I should mention that the "Internat-Style" thing was something I, a 20-Year Oracle Developer and DBA made up...I probably should have used the correct term - stateless - for the technology I am trying to work with.

Hope this also helps any other DBA coming from the "mainframe" woods like me.
Ted

Tom Kyte
September 17, 2009 - 8:47 am UTC

In the mainframe woods - we used to do this "internet style" thingy way back when

client: 3278 'dumb' terminal (the worlds first web browser, works alot like a web browser)

app server: CICS (a transaction manager, runs your code)

database: vsam, IMS IDMS, whatever...

and it was stateless...

nothing is new :) everything people are doing today that they say is so new, so revolutionary - we've been doing it forever. Nothing has really changed.



N-Tier Authentication

Ted, September 17, 2009 - 6:40 pm UTC

Thank you again for your help

Quick note that when I put "N-Tier Authentication" into Google (to start my research), a link to your "Expert Oracle" book came up as the second search result...pretty cool.

Ted

Maintainability, Testability and Propagation

Glen, September 27, 2009 - 10:17 pm UTC

I started out as a presentation-layer developer and have worked with many software languages. PL/SQL (and its cousins in the other databases) is second only in maintainability to the SQL itself (for instance, most data-centric organizations have DBA and BAs with at least some understanding of SQL and PL/SQL). I've also found that the relationship between SQL and the business-level requirements have a better match (I've worked through a query with executives with very little technical background who understood quickly whether the query would answer their question). I've also found that testing business logic is much easier and thorough when done using the database. Finally, propagation and modification of business rules are all but insured.

The sad truth ...

A victim, November 12, 2009 - 5:34 am UTC

Beware, opinion ahead, let's see if this get censored ;-)

Most java deveolper don't know databases (or relational technology) and - worse - they don't want to know about databases.
In this case they regard ignorance as a virtue.

Tom is unfortunately just so right:
"That the database is a black box and we should remain independent from it. That the database an an encumbrance (they'd probably rather just store XML files if they could)."

They want some pretty screens as fast as possible (to get a raise from the fools called "mXnXgmXnt"). Transaction issues, data qualitity etc. are for them things of the distant past.

And tools like Hibernate quite deliberately promote - in almost every case - this mindset: "Hey, smart and trendy java guy, use this API and everything about the data will be fine - you just don't have to know about databases and all this oldfashioned relational stuff."
(Consistently, in many, many projects the database schema is generated from the object model -- welcome to the horror trip.)

Pointed:
Some java developers + the typically amount of ignorance about databases + Hibernate = a failed database project (no consistency, no reliable security, no performance) + some pretty (but useless) screens.

Now, what happens when the developed garbage reveals its problems (this may take some time)? Well, the next project is started with the same methodology and mindset, but now, they think, the usage of the newest java technology hype will ultimately make the differance ... Alas ...

But, of course, there a some (rare) exceptions to this rule; if you are a java developer, who reads this at asktom, it's quite possible that you are one of the them.

I fear that the worst part about this trend is still ahead of us ...
Tom Kyte
November 15, 2009 - 1:48 pm UTC

well, technically, I cannot censor. Only governments and the like can censor. On a forum like this, I can moderate if I choose - but I choose not to.

Besides, I could have written what you just wrote :)

De-professionalization

Michael, November 25, 2009 - 3:05 am UTC

Unfortunatly, i also have to agree with "A victim".

But, unlike Tom, i would not use a smily, because for database developers this issue is deadly serious.

Just image what the java guys tell their fo..., errr, managers: "With Hibernate we don't need the database guys (anymore) ..."
(You can replace "Hibernate" with another tool, that - in the hand of the ordinary java developer - is used to justify database ignorance.)

The question is: What can we do to counter this trend?

Me, too - no wonder we're becoming Grumpy Old Men

Geoff Duniam, December 07, 2009 - 10:34 am UTC

"Now, what happens when the developed garbage reveals its problems (this may take some time)? Well, the next project is started with the same methodology and mindset, but now, they think, the usage of the newest java technology hype will ultimately make the differance ... Alas ... "

A Victim - You forgot a vital point - who gets to "fix" this delightful melange of (possibly) beautifully written <insert latest, greatest application language/framework here - C#, C++, Java, Beans, Hibernate, Big Harries Pineapple Development Environment> code that issues generated SQL statements that do 800 table joins in a generated SQL statement so long we had to load it into a clob for analysis?

We do!! Lucky, lucky us...

The phrase "your application is fatally flawed at the architecture and design level because of <specific examples here - just read above!>. If I'd submitted a design like that as a 3rd year university project, I'd have failed. Go back to the Project Manager and tell him it's not fit for purpose" wins you no friends come appraisal time!

Michael - seriously, the only thing is education, IF you get support from your management team that a) understands the issues or b) wants to understand the issues. If it's c) don't know, don't care, just get it out as fast as you can so we can keep billing, our <choose one - Global Architecture team, Technical Director, Lassie the Wonder Dog> has ratified this approach - you're pretty much scr...erm, I mean, your work will be cut out for you. One approach that may work (depending on your relationship with the <Java, .Net, C#, C++ > guys is along the lines of "hey, check this out - it'll cut your work load back, and it's quick and very secure - your manager will love it" (and show them why) and ask them what you can do to make their life easier.

Failing that, you could always try the "not fit for purpose" argument. That should be good for a few laughs - you'll need them.

Update on "Persistent Database Connections"

Ted, January 02, 2010 - 5:34 pm UTC

Hi,

Follow-up that I was able to convince both my boss and the lead Java developer to at least experiment with the Oracle Proxy Login approach. The Java developer was able to have the code switch to specific database users (I had to tell him to include a "SELECT USER FROM DUAL" to confirm) but his code is not re-using the sessions; it just keeps creating new sessions for each Database call his code executes.

We have a new manager of software development who has, in the past, preferred the "one-db-account-for-all-users" approach - I will now need to convince this person and try to get them to fix the multi-session issue. Sigh.

For those who posted; I feel the basic problem with things like Hibernate is that they are code generators. About 10 years or so ago, there were several products that promised to generate complete software applications - Oracle Designer was one of them. The problem is that at best you can use a templete or framework as a guide to building a customized compter application - at some point, your users will force you to custom-tailor the generated application. You do not see these "silver-bullets" much any more.

I suspect that developers forget the reason SQL was created in the first place - to fix the problemn with Cobol programs having the database design embedded within the software, making the database design very rigid and expensive to change. SQL, to me, is a software development language which allows me to work with physical data in a logical way and significantly reduce the amount of new code which needs to be written, tested and maintained.

I originally started as a 3GL coder so I, to this day, do not quite understand why a sophisticated, modern language like Java has so much trouble working with Relational Databases but it seems to work with flat files very well (Cobal seemed to be good at that too). Maybe Java is really just some sort of version of the C language (?).

Ted
Tom Kyte
January 04, 2010 - 11:14 am UTC

... but his code is not re-using
the sessions; it just keeps creating new sessions for each Database call his
code executes.
...

exactly, that is precisely what it is supposed to do. The CONNECTION (you know, the thing that is expensive to set up) is permanent, the session is transient. The session is created as a subsession of your connection. We do that all of the time, consider:

ops$tkyte%ORA10GR2> select paddr from v$session where sid = (select sid from v$mystat where rownum=1);

PADDR
--------
3FF2482C

ops$tkyte%ORA10GR2> select username, sid, serial# from v$session where paddr = '3FF2482C';

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
OPS$TKYTE                             279       3430

ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select username, sid, serial# from v$session where paddr = '3FF2482C';

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
OPS$TKYTE                             277       1563
OPS$TKYTE                             279       3430


Execution Plan
----------------------------------------------------------
Plan hash value: 3733760267

.....



autotrace does this to create a session to watch your current session - using the same connection.



@Ted

Oleksandr Alesinskyy, January 06, 2010 - 3:00 am UTC

Hibernate is not a code generator, it does not generate your application code. It merely converts some API calls to SQL statements as well as HQL (Hibernate Query Language) statements to SQL statements.

As any tool in the world it may be both (properly) used and misused.

BTW Hibernate and a stateless design are completely independent.

As for schemas generated by it - in most cases they are good starting points for manual tuning.

And DB-developers' ignorance of OO basics is not much more excusable then Java developers' ignorance of DB.

Tom Kyte
January 06, 2010 - 8:30 am UTC

... It merely converts some API calls to SQL statements as well as HQL (Hibernate Query Language) statements to SQL statements. ...

that is a code generator. HQL->SQL, code generation. Hibernate developers do not want to touch SQL, do not want to think about database, use whatever hibernate generates for them.


... And DB-developers' ignorance of OO basics ...

where do you see this ignorance? Point to example here on this page please?

Where is database independence in the JVM?

Galen Boyer, January 06, 2010 - 12:24 pm UTC


Hey java guys, imagine if the JVM implementors left the manipulation
of a file out of the JVM, and instead just offered up interfaces for
file manipulation! Imagine if the manipulation of a file were just an
interface! What would you do? I would bet something like hibernate
for file manipulation would have been born and then, the OS admins
would be screaming at you like Kyte is everytime he has to look at the
hodgepog of SQL scattered throughout a codebase. The JVM spec writers
just gave up the ghost on a very very, probably the most important
implementation that should have been part of the JVM. Could this
actually have been part of the JVM? I don't know, but, at least
recognize that the care it took to make the JVM OS independent needs
to be mimic'd when you interact with a database. You are executing
processes outside the JVM. Would you willy-nilly just run a bunch of
OS tasks from java? You'd probably be very cognizant of how badly
that could perform, you'd really watch out for it. Well this is
what's going on with the database as a central part of your app. You
are interacting with something outside the JVM. Take care when you do
so.

Oleksandr Alesinskyy, January 06, 2010 - 4:11 pm UTC

...... It merely converts some API calls to SQL statements as well as HQL (Hibernate Query Language) statements to SQL statements. ...

...that is a code generator. HQL->SQL, code generation. Hibernate developers do not want to touch SQL, do not want to think about database, use whatever hibernate generates for them.

This statement is a bit of stretch, in this case anything is a code generator, as at the very end any code is converted into to the processor instructions. You may say that those extraction are generated from source - bit nobody would say that it is a code generation (save compiler developers).

Hibernate by no means is a code generator to the same extent as Oracle Designer (and similar tools) mentioned by Ted was.

...... And DB-developers' ignorance of OO basics ...

... where do you see this ignorance? Point to example here on this page please?

In many posts in this and similar threads (by not by you).
But when you say "Hibernate developers do not want to touch SQL, do not want to think about database, use whatever hibernate generates for them." better say "Some Hibernate developers ..." or "Many novices ...", because such generalization is simply untrue, I regularly see just opposite. Neither Hibernate creators (Gaven King and Co) have promoted such approach.


Tom Kyte
January 06, 2010 - 4:23 pm UTC

if you are not writing SQL

and what you get out the back end is SQL

then in the middle - what must you have? No stretch, you are generating code. Just like a compiler. Only hibernate isn't the greatest at writing complex, optimal SQL, it likes simple stuff, generic stuff, database independent stuff.

I've already told you Oleksandr that you work in a rare rare place, the exception - not the rule.

I see the rule.

And for the same reasons I would like to have

triggers
autonomous transactions
when others

removed from Oracle (I know they can be used wisely, they can do good, they can be awesome, somethings would be much harder without them - trust me, I do) - I shy away from this O-O "database independent", don't worry about the big bad database we'll hide it from you, tools.

@Galen Boyer

Oleksandr Alesinskyy, January 06, 2010 - 4:19 pm UTC

... Hey java guys, imagine if the JVM implementors left the manipulation of a file out of the JVM,

We need not to imagine it - it is exactly as it is done in reality. JVM does not manipulate files by itself, it (and even rather not it but Java SDK) just provides an uniform top-level interface to underlying OS(es) file-manipulation capabilities.

BTW, for this very reason file-manipulation capabilities of Java programs are limited if compare them with languages that provide more platform-specific approach (they rather represent a "common denominator").

Yes, they supposed to be extended in Java7.


Session info helps

Ted, January 06, 2010 - 7:04 pm UTC

Thank you, Tom, for clarifying connections vs. sessions. I do appreaciate your help with this.

Sorry if I started a flamewar here - I know this site is for asking Oracle-related questions and receiving answers to those specific questions. A debate over the pros and cons of things like Hibernate probably should be conducted elsewhere.

Ted

oracle

A reader, January 06, 2010 - 10:29 pm UTC


flamewar

Sokrates, January 07, 2010 - 2:25 am UTC

I love flamewars.

What about TopLink ?
Owned by Oracle now, as far as I know.
Is it any better than Hibernate or any worse ?
Who has experience with it ?
Tom Kyte
January 11, 2010 - 8:27 am UTC

I have an issue with any "we shall protect you kind developer from the evil database" piece of software.

They can be used correctly, I know that, some readers here know that.

However, we've all seen the novice developer (ones with 20 years of experience even) use them to protect themselves from the evil database that is just out to make their lives hard.

Hence, they fall into the category of a trigger, autonomous transaction, when others - they all can be used properly, but are mostly horrendously abused leading to failure.

I thought I was on my own...

Vince, January 07, 2010 - 6:26 am UTC

What an EXCELLENT thread.

I am a lowly Oracle bod of some 20 years experience, and rarely touch on the Java/OO world. However I recently worked on a project involving 50 so-called Java programmers (read - 1 real programmer, and 49 who'd just done a course) who cross-trained (in a week) to become what I was told were proficient PL/SQL developers....on a massive and critical Oracle-centric project. Needless to say, Java informed all software and design decisions, the database was indeed seen as an encumbrance, and we ended up doing inane things such as writing scheduling software in Java (DBMS_SCHEDULER anyone?) and taking similarly idiotic architectural and design decisions (Hibernate, also take a bow) because Java looked somehow easier and more familiar than Oracle and, ahem, being more modern, was of course the way to go....

What happened of course was that so much work was done, in an unnecessarily complex manner, outside the database when it could have been done far more easily within Oracle...debugging and performance tuning became nightmares. The bottom line was that the Java bods didn't have a clue what was going on in the database, and the result was an unperformant, difficult to maintain system.

The biggest joke, when I asked what the positives of Hibernate were, was that it would be easy to replace the database should the need arise...!...about as likely to happen as Monday being followed by Friday.


Opinion from a DB perspective

Rob, January 11, 2010 - 8:05 am UTC

I never really see what this Object Relational mismatch is supposed to be. Whether I’m programming in an ‘OO’ language or not the key thing I’ll be using to make the code manageable is an API to call between the different modules of the application. The advantage of that approach, always, is that it is consistent, reusable, tunable and just as importantly documents the code you are writing at the, most important, interface level. You then have the ability the rewrite the underlying code of the API as much as you want as long as you don’t change the interface. Therefore, to me, it doesn’t really matter how the J2EE app connects to the database as long as it has been ‘abstracted’ at least to the level of an API to talk to the database. This can connect via hibernate/JDBC/stored procedure or whatever and also changed if necessary for performance reasons without affecting any other part of the code base.

The biggest problem I see in the hibernate world, standard website, forms and reports kind of stuff, is that the code to render the page and the data access code is literally glued together in the application code. In this case even when you can spot poorly performing SQL in the database it is not a trivial task to be able to tune this. The biggest issue I tend to see is the application code asking for more data than it actually needs and the consequence of this tends to be more serious to the application server than the database, though it could be called a database performance problem. To me this is just poor application design and seems like a big step backwards in good practice. For the typical web type application that I see I don’t really see how OO design is any different, I still just see an ‘Object’ as something like a customer account with maybe all the related transactions/addresses etc tied in to form a 'Customer Object' say. I don’t view this as any different to the Customer table and the hierarchy of child tables that exists in the database from where all the ‘objects’ data originally comes from.

Another common issue I see with standard hibernate/jdbc code is the amount of dynamic SQL that gets thrown at the database. The whole application stack has a strongly typed compile stage to verify good code but buried in there is some hibernate code that then generates SQL code on the fly (A code generator as Tom points out.) that then gets thrown at the database to compile into a plan all at runtime, which as well as being inefficient also has the problem of having to completely recompile and deploy the application when you find that the sql you generated had a typo in it. The advantage of the stored procedure approach (or SQLJ if going the pre-compiler route) is that you at least know you are syntactically/type OK after deployment and cuts down on a lot of these types of errors. I’ve never quite seen why the SQLJ/pre-compile type route was so unpopular apart from the slight increase in complexity, and need to actually have a database design present, when writing the application.

Tom Kyte
January 18, 2010 - 12:09 pm UTC

... the key thing Ill be using
to make the code manageable is an API to call between the different modules of
the application....

what I've called an XAPI - a transactional API.

Oleksandr Alesinskyy, January 11, 2010 - 4:52 pm UTC

then in the middle - what must you have? No stretch, you are generating code. Just like a compiler. Only hibernate isn't the greatest at writing complex, optimal SQL, it likes simple stuff, generic stuff, database independent stuff.

I've already told you Oleksandr that you work in a rare rare place, the exception - not the rule.

I see the rule.

>And for the same reasons I would like to have
>
>triggers
>autonomous transactions
>when others
>removed from Oracle
Cannot agree more (on triggers and when others, as for autonomous transactions - not so sure).

>I shy away from this O-O "database independent", don't >worry about the big bad database we'll hide it from you, >tools.
Aside of mythical, unreachable (and unnecessary) DB independence Hibernate provide enough real advantages to justify its usage (in many situations). And it does not imply "evil database" approach.

>I've already told you Oleksandr that you work in a rare rare place, the exception - not the rule.

I have worked in many places (and in different countries) - and in most of them approach was the same - select the right tool for right tool for the job, learn it, use it properly.

Yes, I have often met developers advocating an approach hated by you (and no less by me) - but mostly not in my personal experience but in forum discussions and even there it becomes more and more rare.




Tom Kyte
January 18, 2010 - 12:19 pm UTC

... And
it does not imply "evil database" approach.
...

my reality and experience is different from yours and apparently others. Hibernate - the word sends chills down my spine, every. time.


Maybe it is because no one every calls me into look at their awesome, no performance problems here, systems. I only get to look at the - well - not so good ones.

RE: Update on "Persistent Database Connections"

Ted, January 11, 2010 - 8:25 pm UTC

Hi,

This additional information is very helpful and reading Oleksandr's opinions is useful.

I failed to mention that my java developer, when implementing the proxy authentication, was causing new database OS processes to be created on the database server. Are these OS processes needed for the "transient" sessions as you described...I suspect sessions should be "logical" to the database and not require OS processes.

The java developer's current solution will result in a new OS process for each database call made by every user of the application - the opposite of what I'm trying to do.

Is it possible to have a Java program/application re-use previously established sessions within a single database connection (via Proxy Authentication) without needing these aditional OS-level processes? If so, where can I get the information on how to do this that my Java developer can understand. I provided him with all of the documentation I could find but I'm not sure the examples/samples included this re-use part and so he may have not fully implemented the proxy-authentication. I do not know where the code is so I cannot check/debug myself.

Ted
Tom Kyte
January 18, 2010 - 12:33 pm UTC

then they did it wrong.

the goal of n-tier proxy authentication is to create ONLY a new session - not a new connection, not a new process.


... Is it possible to have a Java program/application re-use previously established
sessions within a single database connection (via Proxy Authentication) without
needing these aditional OS-level processes?...

YES

http://docs.oracle.com/docs/cd/E11882_01/java.112/e10589/proxya.htm#CHDHHAAD

Even more worrying

Rupert, January 12, 2010 - 1:27 am UTC

"The biggest joke, when I asked what the positives of Hibernate were, was that it would be easy to
replace the database should the need arise...!...about as likely to happen as Monday being followed
by Friday."

What is frightening is that there are people who actually believe that. I often say that 10 - 20 lines of SQL code is representative of a greater degree of complexity than entire "applications".

How many of these apparently "complex" applications actually involve any kind of algorithm? I would say less than 0.4%.

Don't want to flame, but ...

Michael, January 21, 2010 - 2:51 am UTC

@Oleksandr Alesinskyy
You experience must to be very different to ours - or we have a serious misunderstanding or very different standards.

Unfortunately, i have to agree wholeheartedly with Tom:
"my reality and experience is different from yours and apparently others. Hibernate - the word sends chills down my spine, every. time."

You say: "And it [Hibernate] does not imply "evil database" approach."

Are you sure about that?
My experience is that Hibernate - even when used with care and understanding - leads to a database agnostic approach: the database is regularly treated like an ISAM engine or keyed reads (and this may in some cases not lead to disaster) and, consequently, all the normally data-centric business logic is implemented in Java.
To be able to just this (and treat the database as a black box) seems to me to be the main basis of existence for Hibernate (and other similiar tools).

But, perhaps, you have a different understanding of the term "evil database approach" ...

ORM-Impedence

Graham Willis, March 31, 2011 - 5:50 am UTC

I would like to share my thinking on ORM and the impedance mismatch with a possible way forward out of the mire.

First my take on the problem...

I like to think that software domain objects are subjective and relations are objective.
In the object model the relations between objects are described by data structures held within the related objects. There is no description of the relationship independent of the objects; so a doctor has a department and a department has a set of doctors, but the relation between a doctor and a department is not known in itself, but must be referenced either though a doctor or a department object. This is, in analogy, a subjective viewpoint. The relations between objects across inheritance hierarchies are further defined by their position in the hierarchy definitions.
In a relational database there are no objects. The relations between datasets are defined by other datasets. Every dataset and its relations are equally accessible. Essentially the user of a relational database has a God’s eye view of the universe with all perspectives equal. The independency of the relations allows easily for set operations and hence enables business intelligence, which is tortuous in an object environment.
I would assert that application developers use objects because they find them a natural means to express development problem solutions, not because they allow the development of large more complex systems, complexity hiding and reuse, because all of these can be achieved through good modular design.
To the application developer the methods on objects are more important than their relations with other objects. A developer prefers to maintain a bi-directional relationship then maintain an independent relation because they want express the act of relation as a method – the object is autonomous, it knows its place in the grand scheme and it knows its relations. However there is a problem; if I want to change the relation between two objects, I need to maintain the relation in two places. If I want to maintain the relationships between n objects I have to maintain n(n-1) relations in n objects. Although this is inexpensive to solve in code it is nonetheless a fundamental weakness in the object model – a more profound problem than mapping inheritance which is simply an ORM implementation nuisance rather than a fundamental flaw in the conception of O-O.
Another profound problem with the relational model is this: what is an object id? This is just an entity that is required by the model that has no real-world meaning, yet has an indirect logical relationship to the real world. So two equivalent objects (a.isEqual(b)==true) will have two differing id’s with each instance of the class representing the same physical thing. If object A is a booking reservation then object B may be the same booking reservation with all the implications for concurrence and duplication that this brings. Problems of identity in software are not confined to object models, they can exist in relational models and 3GL software also, however the object model forces this problem to persist. Why does Java not allow you to express the identity of an object directly in the language? Because that would mean the identity would have to be established at object instantiation which is hugely inconvenient. With O-O/ORM the identity may never resolved. Identity ambiguity is persistent. This is another fundamental flaw in the conception of O-O.
I have 25 years of development, both as an application designer and a data warehouse specialist. I have experience of an array of development environments starting with assemblers and moving through FORTRAN, C, C++ and the Java stack. In addition I worked for years on databases, ETL and the DW landscape. When I started to look at O-O in the early nineties, I was a strong advocate. However as time went on I became more and more sceptical about it, I now see its proper problem domain as defining user interfaces, graphics programming and other such specializations. I do not believe it to add any great benefit to the business application domain. Perhaps my view is clouded by my experience of maintaining other peoples C++, possibly the world’s worst programming language.
However I readily admit that the object model is unlikely to disappear any time soon. It has become near universal. Application developers are religious about the object model, Java continues to proliferate and SOA further entrenches it: only a complete paradigm shift, akin to the client-server revolution could shake it out.
In my view if the adoption of object databases became widespread; the object relational mapping problem could be pushed to the interface between object databases and reporting databases. Now it is very possible that this is, in fact, the best place for it. With sufficiently powerful hardware and smart mapping tools it may well be easier to create mappings to enable ETL batch loads between object databases and relational databases than to have application developers concerned with a problem which is of no direct relevance to application problem domain and outside their area of specialization.
The process would go something like this; firstly the class hierarchies would be scanned and used to create a set of base tables; an ODS layer from which the data may be rolled up into reporting structures.
The ODS layer would use a table for each concrete class. Where relations are held in a class hierarchy and inherited, they would only be related in the ODS at the highest level in the hierarchy. The implied relations at lower levels in the class hierarchy will be achieved by joining to the table representing the highest level in the hierarchy at which the relationship exists.
The often stated objection to this is that it gives rise to excess table joins which is expensive. I would ignore objection as it is in my opinion erroneous. Databases are great at joining – they are designed to do it and have multiple mechanisms for accomplishing it.
In the ODS layer the object identifiers would be used as the primary keys for the table; identity problems would be resolved in the roll-up to the reporting structures. Where relations are found within the object definition then an appropriate database construct is created; a foreign key reference for a 1:M relationship or a table of relations where a M:M relationship is detected.
Relationships through maps are more complicated:
Class A { Map<ObjectB, ObjectC> m }
Would cause a table A_B_C to be created with a M:M:M cardinality.
What about enclosed classes? These may be treated as ordinary sub-classes; the encapsulation would not be represented in the relational database schema, only in the data itself.
The creation of the ODS layer would in large part be mechanical; the rollup into the reporting layer would require human knowledge of the business model and the object model.
This would mean that neither the object model nor the relational model would need to be compromised (in so far as the object model is not inherently compromised). The relational model would be driven, as ever, by the reporting requirements and the object model would simply be designed to best support application development. You would not be concerned that the application database is a silo as it would essentially be just the persistence component of the application. This would allow applications to become fast, efficient, lightweight and quick to deliver. It would rid the world of nonsense like Hibernate.
The costs of storage due to the data duplication would be offset by the vastly reduced costs for application development. Application developers can forget about databases altogether and a new career in business intelligence OR-ETL will be created.
Of course once the OR-ETL mapping becomes established then changing the application, specifically adding new object relationships, becomes more difficult. But then if you are adding new relations you have an ORM anyway and the change to the OR-ETL will be a configuration change rather than a build change.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library