thanks tom, for the fast replay, I was afraid I missed the time cause locked so fast again...

I see you share my opinion (and my practical experience)... but you know these kind of "experts"...

so what i hoped that you can as an expert I trust (i love your answers about DB2, MsSQLServer... vs. Oracle !!!) can give me more detail answers...

Even Oracle always say "we are optimized for Java", so of course they don't say OCI is faster, there is no comparsion i can find... nowhere...

but that will convince experts or at least can make my position in front of my customors stronger...

Is there an percental number about speed-advantige of OCI vs Java/ Hibernate? I believe Java is slower than C++, pls, even Strings are classes, garbage collector, using so much memory...

so what i don't know about JDBC: is it using OCI and OCI tells direct to the listener ? so JDBC is a real layer which takes time ?

I hope the questions are not too long, but you see it's really my field of interst, and I love Oracle !!!

Thanks, Essy

p.S. I searched bevore I asked your forum with hibernate, but can't find what is really helping me...thanks tom, for the fast replay, I was afraid I missed the time cause locked so fast again...

I see you share my opinion (and my practical experience)... but you know these kind of "experts"...

so what i hoped that you can as an expert I trust (i love your answers about DB2, MsSQLServer... vs. Oracle !!!) can give me more detail answers...

Even Oracle always say "we are optimized for Java", so of course they don't say OCI is faster, there is no comparsion i can find... nowhere...

but that will convince experts or at least can make my position in front of my customors stronger...

Is there an percental number about speed-advantige of OCI vs Java/ Hibernate? I believe Java is slower than C++, pls, even Strings are classes, garbage collector, using so much memory...

so what i don't know about JDBC: is it using OCI and OCI tells direct to the listener ? so JDBC is a real layer which takes time ?

I hope the questions are not too long, but you see it's really my field of interst, and I love Oracle !!!

Thanks, Essy

p.S. I searched bevore I asked your forum with hibernate, but can't find what is really helping me...

If you run an identical OLTP-style query either way, you probably won't notice much difference. The benefit to - and the problem with - Hibernate is that it's auto-generating SQL for you. It's good if you'd otherwise have to handcode a vast amount of SQL. It's bad because Hibernate is oblivious to some features of standard SQL, let alone Oracle extensions. Here are some comments I've previously written on the subject, as well as others' opinions...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:224022800346940201#232561200346219713

first i say sorry for double paste, but there is no way to edit or delete text after COMMIT... sorry !

Hi mark, thanks for your link, I read all... and somehow it looks like professionals are not too fascinated with hibernate...

but yeah, there is the other side, always tell about benefits, just goggle for "Hibernate Caches" and similar... first-, second- and statement-caching... and so much more... I am sure oci actually does all of this if you use it right, they just don't put it out as THAT benefit... am I right ?

what i mean bevore with percent-speed is: if I want an list with let's say 100.000 of entries row by row from an query, how much speed-diffrence I can expect between Hibernate and OCI ? in here the diffrence between OCI und JDBC should be relevant, right ? ( I love OCIDefineByPos rather than so difficult handling in java, will also take effect )

and i feel a little bit blame, I think I took the words of my "experts", who say Oracle want to push people to use java and optimize it in that condition...happy I talked to you, experts !!!

I think in the hibernate world you can define some things as cached. What this means is that if it reads a table once it won't reread that table more than one time per hour. In Oracle the philosophy would be different. The philosophy is to read the data when you need to. If you access it frequently enough then it will be in db cache buffers. Also if it change you won't be seeing old data. (as in Hibernate). So in that sense hibernate is "faster". However that is at the expense of returning the wrong data. (so it is faster at giving you the wrong answer.)

I think in 11G there is a feature in OCI to do the caching on the client but have the cache updated (or at least marked stale) when the underlying data changes. So if you issue the query one time you get the data from the database. if the data does not change and you issue the query again them you get it from the local cache. if it changes and then you issue the query then it goes to the DB. Look in the 11G new features guide. I think I read something about it there.

Ok, it is just not possible to compare Hibernate with OCI - they are just on different levels of abstraction and are purposed for (somewhat) different goals.

It is absolutely clear that performance of a single SQL query would be better (sometimes much better) with OCI then with Hibernate or even plain JDBC, but as soon as we speak about performance of the whole application things become much more involved. I dare to say that in real world Hibernate-based OLTP application would perform quite comparable with OCI-based one.

And, BTW,I have first-hand experience with both OCI and Hibernate (and plain JDBC for that matter).

Despite the lot of nonsense that was said in this thread about Hibernate cache (not by Tom!) it provides significant performance benefits.

And in case of need you may tune-up SQL used by Hibernate manually, as well as use stored procedures etc ... . But typically only tiny percents of code needs this tune-up.

And Hibernate saves a lot of development efforts and facilates for more clear application structure.

I would say that situation closely resemble well-known one - Assembler vs. some high-level language. There is no doubt that perfectly-written Assembler program should perform at least not worse that any high-level language, but in reality good optimizing compiler provides code that outperforms code manually written even by experienced Assembler programmer (save probably, few gurus). And efforts for Assembler programming are much higher. and maintability - much lower.

Here we have exactly the same.

i agree to you comment, but totally disagree to your assembler analogy. SQL != ASM

because: sql is a 4th generation language. assembler is 1gl or 2gl? you tell the computer what you want. the way it'll be executed depends on statistics indexes whatever - you don't care.
sql and hql (hibernate query language) MUST have the same complexity. why? compare the queries. they are both 4gl. if you put something over sql you cannot reduce complexity becaus you must tell the computer what you want and where the data come from and how they are connected. i see no loss of complexity here. the disadvantage is see is: you must learn hql, you are loosing features (connect by prior...), sometimes the automatically generated sql is terrible.

best regards
gaus

I use oracle to write forms and reports, means that I send sql-statements to the server... ok, let's say there is no speed lost, we just send less statements... but what I want to know is the for me more relevant point of the GET result-rows (OCIStmtFetch2 in OCI)... I think that we loose a lot of performance cause:

1. the field-data-results are in OCI directly set in variables, which are once define and set by OCIDefineByPos...

in Java/Hibernate we give an variableName and ask JDBC for an special column value, that is loose of performance ?!?

2. as I understand, OCI gets the result direct from ORACLE or better direct from the listener... so how about MoveNext in Java vs. OCI_FETCH_NEXT in OCI ? also sends to the server for EACH row...

JDBC must ask OCI and CONVERT it to the JDBC-Standard, so we can easy use the JDBC-Commands, how much we loose in here ?

3. Java takes so much RAM and must interpret the code (even if JUST-IN-TIME-Compilation, it also takes time ?)

and if we talk about JDBC, there is also ODBC and OLE DB, what is the diffrent between them, which one is the fastest ?


again, I don't talk about send one ore two statements to Oracle, but receive 100.000 rows once direct(OCI) and once over JDBC (JAVA/Hinernate)... this can't make minimum diffrence...

I just want to verify this, if possible with any percentage numbers of performance loose...

but it's good if there is also experts who prefer hibernate in this forum... thanks, for me is really helping to build my opinion...

When you talk of performance, as soon as you talk about bulk updates, there's no comparison.

Using OCI and Oracle's object-relational type collections, you can pass a single suitable collection in and have an appropriate bulk processing. With Hibernate, you likely looking at row-by-row updates.

was the only arguments for re-writing OCI as hibernate based on performance? if so, it is a very bad argument.

If "is more up-to-date" means was born after OCI. that's surely correct. does that make hibernate better for your resume than OCI? who knows, but that definitely is not a motivator for a IT project.

keys to performance tom says over and over.. don't do work that doesn't need to be done. everything that can be done in a single SQL statement should be. if not a single statement, use bulk binding/processing.. worst case is row-by-row processing.

hopefully all languages support those concepts, although a few don't.

if you want to agitate the developers that started this with you, tell them that they should migrate their ORM to Oracle's toplink. It supports much more of the native oracle features than hibernate and fills that same "framework" role, and it is optimized for bulk processing. that's a separate discussion entirely that has been hashed out on java forums for years.

while OCI may be tough for a JDBC developer to read/write, just because they don't understand your code, it doesn't mean it is wrong or is done better in their paradigm. If you are team of developers, do you think you can learn hibernate faster than they can learn OCI? Do you need to share maintenance responsibilities of this code? are the clients typically developed in java?

write pl/sql that does everything in bulk fashion, and tell all developers OCI or otherwise, that the only interface to the data is through your bulk binding pl/sql layer.

they may have to write pure JDBC to access it if hibernate doesn't support pl/sql bulk binding, all you'll continue to write OCI that uses that same pl/sql api.

if you took everything oracle says and didn't apply appropriate filters. you'd be a little confused.
oracle says in whitepapers that the key to performance is to move the data as close to the application as possible. this approach means that stuff like ORM/SQL for data access/persistence all happens asynchronously behind the scenes and the application just reads/writes all data as objects from/to memory (coherence).

we know what Tom and others think of that concept, but i am getting off target.

really, there were very helpfull answers, but you realy put it on the point with some sentences... next week I meet my new customer again and his experts, let's see, I will show them our discussion in here, I know they know about asktom.oracle.com and accept is THE oracle-forum, which is not compare-able with any other forms... thanks to all of you !!!

Essy

p.S. if I get some new feedback, I hope it's ok to post it in here...

OCI vs Hibernate is classic apples vs toaster ovens... they do not solve the same problem. OCI is a means of communicating with Oracle, Hibernate is a means of persisting objects in a relational database.

But if, by "OCI", you mean: manage the persitance of Java objects through do-it-yourself DML, then you can make a comparison.

You can manage persitance of Java objects in Oracle through:
1. DIY queries
2. Hibernate
3. Toplink
4. Java 5 EE persistance

And for a comparison I would refer you to Google, and advise that no 'neutral' answer exists. (Not even on Tom's site! ;-)

But since 4. (standard Java EE persistance) was created by an expert group containing members who were in involved in Hibernate and Toplink, I would say that it supersedes both of them.

Following Malcolm's lead I found this article
http://today.java.net/pub/a/today/2006/05/23/ejb3-persistence-api-for-client-side-developer.html
which includes such fine whine as "I have a confession to make: I've been a professional software engineer for close to ten years now and I don't know anything about databases...To me, a database is simply a box to store stuff. I don't know how they work and I don't want to know... almost every application needs a database. But this doesn't change the fact that I still hate them." (bolding in original)

Then we get right to the example which begins like this:
"In this article, we will build a simple address book application. The heart of any application is its data model."

I'm still trying to reconcile that last sentence with the initial statements...

    private void create() {
        System.out.println("creating two people");
        EntityTransaction tx = manager.getTransaction();
        tx.begin();
        try {
            Person person = new Person();
            person.first = "Joshua";
            person.middle = "Michael";
            person.last = "Marinacci";
            manager.persist(person);

            Person sister = new Person();
            sister.first = "Rachel";
            sister.middle = "Suzanne";
            sister.last = "Hill";
            manager.persist(sister);
            tx.commit();
        } catch (Exception ex) {
            tx.rollback();
        }
        System.out.println("created two people");
    }

> I don't want to know ... I still hate them.

Very professional statements ;)

I feel now we come to the point I ever wanted to reach... Application developement with GUI... we need GUI's for users to work with the application and put their daily data... and the others, who want to get reports...

(of couse it's only a small part, but I want to compare exactly here...)

--- OCI vs Hibernate is classic apples vs toaster ovens... they do not solve the same problem...

So I don't see hibernate and OCI too diffrent like I have the feeling you do... let's forget the part, that OCI can do (nearby ?) everything what oracle offers...

We use VC++ to create the GUI - the forms, reports and local functions... and we use the OCI-functions to get data for the fields und lists from oracle - and of course to update them on the server if the user adds, edits, delets... like so many others do too... - that is (an part of) OCI-Programming

and there are people who use hibernate and java to create their forms and reports and to load and update the data from the server... - they do the same, maybe even looks same GUI, that is (also only an part of ???) Hibernate-Programming


so why it's not compareable ? I think, in hibernate we also can show lists with for example all transactions on a day, let's say that are many thousends - with OCI it's extremly fast...

I want the compare with hibernate which is using JDBC, but as I understand that's not the big point of speed-loose...

so, why we can't compare them in their common field ?

From a database perspective, note that "HIBERNATE...simply led to frostbite".
http://it.toolbox.com/blogs/confessions/mentoring-update-1-16527

hi duke I read your first article, the second is next...

but I have a question on tom's comment:

--- A FRAMEWORK TO BUILD GUI'S IS DEFINING HOW YOU STORE YOUR DATA

somehow I can't find this in the article, somehow not even something similar... they don't talk about how we HAVE store data ??? we can store as we want them to store ???

and huhu...I even can't see something wrong in your example, it's just an table and they want to fill, they can use any table... please explain, what is the "entire premise" ???

really, I am not sure, if we blame hibernate or we blame programmers who don't use hibernate too professional ? aren't there also documentations like "oracle for beginners (and not one step more) ?"

I also read the second artikel (thanks to duke), but it also looks like the programmes problem, if they let ORM create their datamodel... maybe cause they get the wrong abstraction or something, but I think it's not cause Hibernate/ORM itself... ?



"you have to have a single column primary key, its name is ID"

I also wonder about this, I think they also autoincrement them, so it's really like hidden (wow, so you don't need to care !!!)

and there are also one-to-many and many-to-many -relations... but I can't believe that's the only way, they can't stop at that level... maybe there are professional hints ?

but also THEIR example, it's not the limition of hibernate (?) but I can't say what hibernate allows in condition of error-handling, I know with OCI we can get every error thanks the great Error-Handle-Concept... if in hibernate, would it be an part of JDBC ?

(huhu... is from german,cause i really feel a little but ashame not see what you mean)...

and "the entire premise" is, as I understand, an (humorous) private opinion of the autor, not the premise of hibernate ???


maybe not too related, but i read "transparency layers" and just googling it ... you know about it ?

Essy-- Reading this question to Tom may help. Note that the FRAMEWORK "requires a certain format for the table structure..."
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:969294900346588814
-- Duke

I'll never get boring when reading asktom... one link puts to other links and so on... and all are interessenting for the topic...

maybe I am naive (I am PRO OCI, that's why I start the topic, just want to be fair with hibernate...) the ID-Column... it's a MUST cause the hibernate-creators WANT IT, am I right ? they don't read the complains? I think if they would listen to all the feedback (as they always say they do - with all groups of experts) and put it in their product, hibernate would not be that bad ? that's one of the arguments I heared today in discussion, hibernate is developed by so many people and there are often updates and it becomes better and better... difficult to answer, sounds good...

or will it always have weak points, couse Frameworks MUST have them ? I don't use frameworks, so i don't know about the limitations but that's why they are difficult to imagine for me...(if there are limitions, I think, they can be solved ... ?)

I'll never get boring when reading asktom... one link puts to other links and so on... and all are interessenting for the topic...

maybe I am naive (I am PRO OCI, that's why I start the topic, just want to be fair with hibernate...) the ID-Column... it's a MUST cause the hibernate-creators WANT IT, am I right ? they don't read the complains? I think if they would listen to all the feedback (as they always say they do - with all groups of experts) and put it in their product, hibernate would not be that bad ? that's one of the arguments I heared today in discussion, hibernate is developed by so many people and there are often updates and it becomes better and better... difficult to answer, sounds good...

or will it always have weak points, couse Frameworks MUST have them ? I don't use frameworks, so i don't know about the limitations but that's why they are difficult to imagine for me...(if there are limitions, I think, they can be solved ... ?)

I think the tone of the Hibernate roadmap speaks volumes:
http://www.hibernate.org/357.html
"Hibernate3 makes it easier than ever before for Java applications to interact with persistent data, allowing a single definition of the transformation between various in-memory representations of the entity data and the relational schema, even in the case of very complex legacy schemas... [it includes] a few new features that are powerful, if used judiciously. They are certainly not to be considered essential, but they round out the structural mapping functionality [such as]
* single class to multiple table mappings (the <join> mapping element)...
* ability to map association tables as having one-to-many multiplicity at the object level."

There are upsides to Hibernate:
http://www.hibernate.org/117.html#A28
Q: How can I change the identifier value of an existing instance (ie. the primary key)?
A: It is part of the definition of a primary key that it is not only unique, but also constant throughout the life of the row. Hibernate does not, and should not, support changing identifier values.

And this beauty (my bolding added!):
http://www.hibernate.org/117.html#A12
Q: Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)?
A: First, you need to understand SQL and how OUTER JOINs work in SQL. If you do not fully understand and comprehend outer joins in SQL, do not continue reading this FAQ item but consult a SQL manual or tutorial...

I read your links (and the links in this links, quiet a lot but interessting...) no one in here (COMPLETE askTom ?) is really fighting PRO hibernate, don't this people come to here? as I heared again, hibernate is used very often...

but as I start to understand (even difficult to believe) is that hibernate want to "educate" their programers follow special rules (THEIR rules)... - whereby tom and you say WHY rules - we want to do everything (by prinzip ???)

why rules- if they are restrictions ? they give the reason that they are experts and based on a lot experience and if we do like they tell us -> we get the best possible result...

and if we ask more, they will tell us: learn SQL first... (we can't ask tom to put questions there ? ;-) I understand if not, but yeah, hibernate-people don't want to discus... so difficult to proof or not - no, not difficult, the proof is the praxis and the problems they have there...)

is this right ? thanks again for all the answers and links (i read all)

I don't have any questions pro hibernate now (and we got so much negative...) but I already forward it to hibernate-experts, hope they at least read it and I can discuss with them better than before... if I get news (sensefull news), I will post them in here, but for now I got really what I wanted to know... thanks to all of you !


I asked before, but no answer, so I ask you again...
in dukes first artikel i read about "transparency layers" and just googling it... but I can't find till now... you know what's the meaning of this? related with datastructure ?

Underneath this confusion lies a fallacy.

> since i meet another programmers who tell me that
> hibernate is more up to date than using OCI,

That's utter non sense. It's something like saying that MFC or COM are more up to date than Win32. They aim at solving different kind of problems. At most, in Java planet OCI is similar to JDBC:
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#02_01 With Microsoft tools, it's easy to build applications around recordsets. With Java the approach is more object oriented (OO), however, storing in a relational database the data contained in the objects (not the objects themselves nor the application logic) is a pain in the neck because of the differences in their respective paradigms. With Nth normal form you avoid repeating information, with OO principle of encapsulation you actually hide information. This problem is widely known as an "impedance mismatch". ORM tools aim to ease this problem and Hibernate is one of such tools.

Thinking that Hibernate is like OCI is like thinking a Visual Basic grid is comparable to OCI because is what programmers bind to the database. Ah! And Perhaps both Hibernate and grids are evil because of the bad practices they promote. ;-)


> so my question is: is hibernate really FASTER than OCI

And if one of them were twice faster than the other, then what? Lets rebuild those hundreds of forms that take 4 seconds to load for new forms that take only two seconds?

> hibernate encourages, promotes bad practices and that is taken up by the developers.
> you have to have a single column primary key, its name is ID

Sorry, this is ludicrous, and I accept is common knowledge disseminated on the web even among some Hibernate "pundits". From the features page of Hibernate (and from my own experience):

- Support for natural and composite keys <- no need AT ALL to use an autogenerated PK called ID, since earlier versions!
- Derived entities and attributes (support of subselects)
- Support for hand-written SQL.
- Support for stored procedures.

Same goes for other tools like Oracle's TopLink. To me, a prevalent problem with Java developers is the generalized lack of knowledge about databases in general and SQL in particular, and some stupid dogma around database independence. Those are the primary source of evil, not the ORM tools by themselves.

An accounting program that my Java colleagues once thought should be solved with overnight processing because of the amount of data, is taking just 30 seconds of processing with well written SQL (just views, not even procedural code with stored procedures!). Naturally I refrained from using ORM for bulk and data operations. Since the data processing is solved with what I consider the better alternatives: SQL and raw JDBC (pretty equivalent to OCI), I'm about to enjoy the benefits of using an ORM tool (TopLink Essentials) and a GUI generator for the forms.

I don't really understand that bitterness against Hibernate, if a so tiny project like mine could accommodate both approaches, I just can't understand how in bigger projects people question the simultaneous use of low level (OCI/JDBC) and high level (Hibernate/ORM) APIs and tools in different parts of the system under construction.


Cheers

Javier

> I send sql-statements to the server... ok,
> let's say there is no speed lost,

Ha! This is were most of the time is spent: latency. So much time wasted that it could even higher than SQL processing for simple requests. As Tom said, if SQL takes most of the time and latency takes even more time, you get the idea.

> for me more relevant point of the GET result-rows (OCIStmtFetch2 in OCI)...
> I think that we loose a lot of performance cause:
> 1. the field-data-results are in OCI directly set in
> variables, which are once define and set by OCIDefineByPos...
> in Java/Hibernate we give an variableName and ask JDBC for an special
> column value, that is loose of performance ?!?

I don't understand, in JDBC the rough equivalents are Statement and ResultSet classes, where you specify more or less the same data as in the OCI calls, granted, you use variables and no pointers, but, do you know how Java passes variable references? By value! Always! And the value that is passed internally in the Java Virtual Machine is the address where the data resides. and not, this is not a pointer, although C programmers pretend otherwise.

> 2. as I understand, OCI gets the result direct from
> ORACLE or better direct from the listener...

As JDBC does too, just try shutting down the listener and see what happens with the Java application.

> so how about MoveNext in Java vs. OCI_FETCH_NEXT in OCI ?
> also sends to the server for EACH row...

Well, it depends on the value of the property FetchSize of the ResultSet instance. IIRC, it defaults to 10 in Oracle's JDBC driver, which is generally too low, and which purpose is to avoid a round trip around the network for each row. However, you should test your application in order to get the best performing size.

> JDBC must ask OCI and CONVERT it to the JDBC-Standard, so
> we can easy use the JDBC-Commands, how
> much we loose in here ?

If you're using a Type 2 driver, there is a conversion between the OCI representation and the Java one. If the driver is a Type 4, that is, fully implemented in Java, there is no call to OCI.

> 3. Java takes so much RAM and must interpret the code
> (even if JUST-IN-TIME-Compilation, it also
> takes time ?)

There are several implementations of JVM and each one the JVM takes it's share of memory,however almost no one performs just-in-time compilation anymore, that was true 10 years ago. Now, JVMs use to compile only the most used calls, in order not to waste time compiling code that is almost not used. Of course, once a piece of bytecode has been compiled, it's not interpreted anymore.

> and if we talk about JDBC, there is also ODBC and OLE DB, > what is the diffrent between them, which
> one is the fastest ?

Which is faster? ANSI SQL-89 or ANSI SQL-92. You can't tell because these are standards, what you can do is compare implementations. Oracle is not the only one vendor where you can get an ODBC or JDBC driver for its flagship product. By design ODBC is a C based interface and JDBC is a Java based one. Don't get fooled, that doesn't mean anything, do your homework and perform your own performance tests. And take other's benchmarks with a grain of salt. You know better than anyone the bedtest that best emulates your application.

> again, I don't talk about send one ore two statements to
> Oracle, but receive 100.000 rows once
> direct(OCI) and once over JDBC (JAVA/Hinernate)... this
> can't make minimum diffrence...

I think this is useless, but since it took only 5 minutes... I've just fetched and iterated over 427,830 records from a SQL Server instance with MS JDBC Type 4 driver. It took 28 seconds to complete the task: 15 seconds for the server to resolve the query and 13 seconds when finally I started to iterate over the rows returned by the server. I also created 427,830 instances of a Java class named Balance.

CREATE TABLE Balances(
 Natural_Account varchar(10) NOT NULL,
 Sub_Account varchar(11) NOT NULL,
 Center varchar(6) NOT NULL,
 Interco varchar(4) NOT NULL,
 Product varchar(6) NOT NULL,
 Trans_Type varchar(4) NOT NULL,
 Project varchar(9) NOT NULL,
 Trx_CCY char(3) NOT NULL,
 Balance_Date datetime NOT NULL,
 Opening_Balance_Trx_CCY numeric(18, 4) NULL,
 Ending_Balance_Trx_CCY numeric(18, 4) NULL,
 CONSTRAINT PK_Balances_1 PRIMARY KEY
(
 Natural_Account ASC,
 Sub_Account ASC,
 Center ASC,
 Interco ASC,
 Product ASC,
 Trans_Type ASC,
 Project ASC,
 Trx_CCY ASC,
 Balance_Date ASC
)

> but it's good if there is also experts who prefer
> hibernate in this forum... thanks, for me is
> really helping to build my opinion...

hibernate wouldn't impose a noticeable overhead over my test, really!, test for yourself and keep an open mind.

Cheers

Javier

I think Javier is dead on with his assessment. As a java developer for the last 10 years, I've learned a lot, most of which goes against the grain for your run-of-the-mill java developer. To add my own opinion (it may help to hear the same things put another way):

1) Hibernate is a framework designed to eliminate the necessity of understanding SQL to interact with a relational database. I think this is bad (really really really bad actually). As a developer, you are responsible for the resources you consume and how efficiently you handle their consumption. I've seen many java developers simply pawn their performance issues off on the DBA because their code generators generated crappy code (specifically Hibernate). I've seen the same developers make their lack of ability to properly deal with opening and closing file handles a problem of the system admins instead of taking responsibility themselves. The bottom line is you own your code no matter where it came from. Code generator, the tips of your fingers, craker-jack box, I don't care, if it doesn't run well, 98% of the time its your code at fault, suck it up and fix it. Asking a DBA to add an index or issue a daily compute statistics during off-peak hours is the last thing you should do. Go to your DBA with actual metrics and statistics for why a given performance problem is their problem, don't just hand it off. If you don't know how to obtain these metrics, perhaps you should learn a little more about the resources you're utilizing and understand how to use them better (notice a trend here?). Your DBA will generally be an invaluable resource for learning more about your database.

2) Hibernate offers database independence. Well isn't that statement a buncha BS? As I've heard Tom say time and again, "If you remain database agnostic, you'll lose any performance implication you'd used as a justification for making such a large enterprise purchase anyway". There are advantages to neutrality. A very specific list of criteria may be applied to justify the use Hibernate in a database agnostic way:
1) You provide an application for sale or use in an environment which you do not control.
2) You do not have re-distribution rights to the database your application uses.
3) The enterprise landscape in which your application runs in is unknown for all of your clients at the time of its creation.

This does not apply to the majority of Java development happening in the free world. Most of us are likely "Corporate Code Cutters" who are forced to write code in an environment which is not going to change. Your company bought Oracle for a reason and they expect to see a return on it. Don't cheat them out of it on some principle of database agnostics when you know darn stinkin' well that your company is going to be an Oracle shop from here to eternity.

3) Hibernate will do a great many things on your behalf. That doesn't mean that you should let it. If you do not understand what it is doing and why, you may find that not only is it not doing what you thought it would do, its probably doing it all wrong. Let's take a few examples:
1) Hibernate will generate your database schema for you (if you let it). This is not a particularly grand idea. As mentioned previously in this thread, an object model may not (usually does not) translate directly to a good relational model. Hibernate knows nothing about your Enterprise Landscape (reporting requirements, other applications that simultaneously make use of the same data repositories, etc.), nor does it have any means of indicating such usage. Design your database to be a clean effective relational database and your object model to be an efficient and effective hierarchy.
2) Caching. Now this is nothing specific to Hibernate, but TOPLink at least forces you to consider which box to check before it generates its bindings for you. If you don't understand the implications of caching under concurrent access of a data repository, you will be in for a world of hurt. If you think that your application will be the only subscriber of the data in your schema for now and forever, I'm sorry to say, but that is also untrue. Once the corporate monkeys know that you're sitting on a gold mine, they will want 10 different ways to pull the data out and make pretty little graphs and charts from it.

Now to completely contradict the tone of this review. I myself am a Hibernate developer. I use it, and I enjoy some of its features. I will say though that I fully understand my responsibilities as a developer and I'm trying to get better at understanding my responsibilities as a client of the Oracle database I'm paid to write applications on top of. Most of the issues listed above are behavioral (anti-)patterns in modern java developers. Understanding what it is you are doing will only help you do it better. Cutting out the redundancy of building objects from a ResultSet is one of the main advantages of Hibernate. If you can separate the good from the bad from the completely useless, you'll fair pretty well.

I'd love to discuss all with you, you answers gave new stuff to me and I checked it out till now...


So let's discus about Java vs. OCI first, since your answers are related to this


> Ha! This is were most of the time is spent: latency. So much time wasted that it could even higher than SQL
> processing for simple requests. As Tom said, if SQL takes most of the time and latency takes even more time,
> you get the idea.

latency... if it's that tragic (is it ???), it's affected to both, OCI AND Java...



> I don't understand, in JDBC the rough equivalents are Statement and ResultSet classes,
> where you specify more or less the same data as in the OCI calls, granted,
> you use variables and no pointers, but, do you know how Java passes variable references?
> By value! Always! And the value that is passed internally in the Java Virtual Machine is
> the address where the data resides. and not, this is not a pointer, although C programmers pretend otherwise.

here I still see one of the big diffrences... in OCI we use OCIDefineByPos (normaly) ONCE (!) per Statement and Column/Variable,
means we tell Oracle the ADRESS where to write the value of the column... if we have OCIStmtFetch thousends or more times,
Oracle writes the values for each column in the SAME place in the RAM... i'm still impressed of this idea !

in JAVA/JDBC we say: recordSet.GetString(1)

what happens (in my opinion, correct me, if I'm wrong !):

JDBC get's the values from the server and hold it internally. we use an METHOD of an Class to get value,
and get the value means COPY it in the ram to the new address... and this is done for EVERY record when we say MoveNext, MovePrevious, ...

if we say recordSet.GetString("Column1"), it's more slow, cause we give as an argument an STRING, not an number, and this string must
converted to get the column-number (I think with String-Maps, but it still takes time - for every record and every column)
case is of even more slow than the first case, cause they must, which column has this name...

and Strings again, in VC++ we have an CString-Class, it's much much much slower than if we use GlobalAllocs, strcpy, strcat...
since Strings in Java are also classes, I think, we also loose speed there...

I created an (easy) test-evironment with an Oracle-System-View, Java/JDBC (typ 4) looks really much more slow than VC++/OCI, but I don't want to write now since I am sure I compare the
best Java-Code with the best VC++ -Code...

I will also test the fetch-size and other parameters...


> If you're using a Type 2 driver, there is a conversion between the OCI representation and the Java one.
> If the driver is a Type 4, that is, fully implemented in Java, there is no call to OCI.

cool info, thanks !
Type 4 even gives the possibility to work on clients without Oracle, I don't have this problems, but yeah, is it
true, that OCI can ONLY be used on Clients with Oracle ? or is there another possibility ?


if find about ODBC, there is an bridge to JDBC... this sounds really bad ?!?



Javier, I hope you don't missunderstand, we don't blaim Hibernate, and we NEVER blaim real experts (like you ! give REASONS and explainations, not say: learn SQL first)
, just some (many ?) of the "experts", who can't tune statements...
In my case we find out that the "tuning" bevore was so bad, so the loose of hibernate is given but in absolute quiet less...

so I'll post after I find out more with my tests...

> latency... if it's that tragic (is it ???), it's affected to both, OCI AND Java...

Latency affects any I/O operation because there is a cost associated with moving bits from one point to another. It's slower to access data from disk than from memory, ¿OK? It's even slower if data has to be serialized in order to be transmitted down the wire, broken down and packed within SQLNET, TCP, IP, 802.11 packages and all this work in the inverse order in the host. OCI and JDBC are *remote* interfaces, and thus their implementations are affected by latency.

> here I still see one of the big diffrences... in OCI we use OCIDefineByPos
> (normaly) ONCE (!) per Statement and Column/Variable, means we tell Oracle the ADRESS
> where to write the value of the column...

Sorry, you're not telling the Oracle server anything, the pointers are local to your OCI application, even if it is located in the same node as the database, your OCI client is using an entirely different memory space than the database server.

> if we have OCIStmtFetch thousends or more
> times, Oracle writes the values for each column in the SAME place in the RAM...
> i'm still impressed of this idea !

Mmmhhh, I understand we're passing a reference to the statement struct, and hence its rows associated, so no writes on the "same" place occur. Of course there is data copying in some point, since the data format that comes from the network is pure text, and once the call is made it has to be casted to the right type anyway. I believe that unless a row is fetched, no data casting/copying happens.

Oracle server writes data to a network stream that is buffered by the OCI implementation *in the client*. The pointers are indicating OCI where to *look for* data that already has been pulled from the database and is in the said buffer (OCIStmtFetch2() fetched rows are kept in a user's buffer)

In JDBC, the same happens when ResultSet.next() is called. While in OCIStmtFetch2() the nrows parameter indicates the number of rows to be fetched from the database, in JDBC the corresponding call is ResultSet.setFetchSize().

Fetch size is so important that is will have more impact on performance than the use of JDBC or OCI by themselves. For example, if the average number of rows for a given query is in the thousands, I'd never call OCIStmtFetch2() with a nrows parameter of 1, because that would imply thousands of network round trips to the database with all the latency associated. By comparison, the default fetch size on JDBC is 10 rows at at time, so there would be 10 times less round trips, and it would give the appearance that JDBC is "faster" than OCI.

> in JAVA/JDBC we say: recordSet.GetString(1)
> what happens (in my opinion, correct me, if I'm wrong !):
> JDBC get's the values from the server and hold it internally.

As I stated earlier, this is not the case, when you call ResultSet.getString() the data is already in the user's buffer.

> we use an METHOD of an Class to get value, and get the value means COPY it in
> the ram to the new address... and this is done for EVERY record when we say
> MoveNext, MovePrevious, ...

Well, actually where not calling a method on a class, but on an *instance*, that is an object that had to be created and assigned a memory space, a task that in a Java Virtual Machine (JVM) is so fast that is discouraged to create object pools in general.

Second, in Java variables are references, so if you have thousands of different strings in the buffer, and you call thousands of times ResultSet.getString() you're not copying the data, the JVM just create references to the data already in memory :-) Java applications have a big footprint, but data copied is not the reason for that big size.

> if we say recordSet.GetString("Column1"), it's more slow, cause we give as an argument an STRING,
> not an number, and this string must converted to get the column-number (I think with String-Maps,
> but it still takes time - for every record and every column)
> case is of even more slow than the first case, cause they must, which column has this name...

If your recordset has 32 columns, Java has to create only *once* the 32 strings that represent the column names. After that, every time you call ResultSet.getString("columnname") no new objects are allocated, just references to the existent strings are passed around by the JVM.

Furthermore, if a concurrent thread is using the same call, the JVM guarantees that there won't be two instances of the same string within the same security domain in the JVM.

Again, in the String-Maps, you're suggesting what Java would maintain are references, not the actual objects.

> and Strings again, in VC++ we have an CString-Class, it's much much much slower than if we use
> GlobalAllocs, strcpy, strcat...
> since Strings in Java are also classes, I think, we also loose speed there...

That's the very reason strings in Java have an special treatment from the JVM. From the String JavaDoc:

"A pool of strings, initially empty, is maintained privately by the class String ... if the pool already contains a string ... Then [a reference to] the string from the pool is returned. Otherwise, this String object is added to the pool and a reference to this String object is returned."

I'm not trying to convince anybody that object creation is instantaneous, because it's not. But what I'm trying to create awareness is about certain missconceptions about modern virtual machines (like Java, Perl, etc.)

> I created an (easy) test-evironment with an Oracle-System-View, Java/JDBC (typ 4) looks really much
> more slow than VC++/OCI, but I don't want to write now since I am sure I compare the
> best Java-Code with the best VC++ -Code...

I think performance difference would be marginal. Look for performance elsewhere: avoid moving data outside the database as much as you can (avoid latency), tune your SQL, avoid scrollable cursors (that is, prefer forward only resultsets), use batching, connection pooling, session pooling, statement caching, etc. All these techniques are attainable either with JDBC and OCI, and will have a greater performance impact.

> is it true, that OCI can ONLY be used on Clients with Oracle ? or is there another possibility ?

Yes, you need the "thick" client.

> f find about ODBC, there is an bridge to JDBC... this sounds really bad ?!?

It's not that bad as it seems... it's worse. You're adding the performance penalty of making Java calling the OCI functions, and it takes time. Also, that bridge was a temporal solution more than ten years ago when there were no native Java drivers. The bridge implementation is lousy, recommend it only to your enemies.

Cheers


Javier

I did some tests with JDBC and OCI, as Javier said, OCI is only a bit faster, less than 10% if same code, at least for queries...

I wanted to test an SELECT on an table, and I choose
"SELECT * FROM Sys.Argument$" ... any table...

but exactly if THIS table, in OCI I get the error ORA-32255 - "Message 32255 not found; product = RDBMS; facilty = ORA"

SELECT on other tables work... the error happens on the FRIST OCIStmtFetch2()... it's not the problem of OCIDefineByPos()... it's not too urgent cause it only happens on this table, but ... what's the meaning ?

in JDBC it's no problem to access this SELECT-statement... and I can't find documentation for this error...

and please, explain what's the diffrence between

set OCI_ATTR_PREFETCH_ROWS to 100
and
OCIStmtFetch2() with parameter nrows=100 ?

thanks tom !

> and please, explain what's the diffrence between
> set OCI_ATTR_PREFETCH_ROWS to 100

It means "Please when going to the database server bring 100 rows in the result set"

> and
> OCIStmtFetch2() with parameter nrows=100 ?

nrows parameter is used in conjunction with fetchOffset and orientation parameters. Lets say nrows = 10, fetchOffset = 12 and orientation is set to OCI_FETCH_ABSOLUTE. Then, from 100 rows pre-fetched from the database, OCI is being told to give you rows 12 to 21.

Cheers

Javier

On more precision, as Tom has already point out, if nrows specifies more rows than there are in the buffer, then another roundtrip to the database is executed, regardless the prefetch setting.

Cheers

nrows parameter is used in conjunction with fetchOffset and orientation parameters. Lets say nrows = 10, fetchOffset = 12 and orientation is set to OCI_FETCH_ABSOLUTE. Then, from 100 rows pre-fetched from the database, OCI is being told to give you rows 12 to 21.

where the rows 13 to 21 will be ? in the local cache ?

the row 12 is in the defined variables...

there is for one column one defined variable, so what if there 10 rows to get cause nRows = 10 ?

if we prefetch 100 rows with OCI_ATTR_PREFETCH_ROWS, they are local on the client, not at the server ?

is it usefull to use both commands together or better we prefer one of them ?

thanks !
thanks !

for x in ( select * from t ) 
loop
   process row
end loop

open c for select * from t;
loop
   fetch c bulk collect into l_array limit 100;
   for i in 1 .. 100
   loop
       process row(i)
   end loop
   exit when c%notfound
end loop

We have web based application . The architect wants to know which driver ( Type 2 / Type 4) will give a better performance.
What are the pros and cons of these drivers?

"thick OCI drivers" ...
Does it fall under Type 2?

There is a term, database independence by the codebase, and the problem with is that that term has expanded to now mean, database ignorance by the programmer. Database independence is a pretty neat concept and can be strived for and when not achieved that part of the app is forked. This is all well and good, but to achieve it requires serious database expertise by the programmers. Sure, strive for database independence in your codebase, but that by no means you do not need to understand the database, it demands and requires it. But, the code being database independent has morphed into the developer actually stating with all seriousness, that they themselves, are the ones that are database ignorant.

If I had to blame this mess that hibernate is just yet another example of, "independence equals ignorance" is the true issue.

great argument - i like it

I work on a project where Hibernate dictates everything. The database is just there to store data, that's it. Recently the data model was modified to use sys_guid primary keys across the board (stored as varchar2(32), not raw) and to make it even better they're working on deploying the application in a 2 node RAC.

The so called architects talk about "Database Independence" , "universal codebase" , so on and so forth.
When somebody makes the case for "Database Independence" , they should know the downside of "Database Dependence"(!).


In my new job , we have a generic data model. Their argument is "We don't need a DBA to create a table" . Their data model is "highly configurable".
Guess what , they have the application on RAC , to scale (!!!) .

I can not raise my voice , just for the fear of losing the job.

From my experience , when the project's schedule , performance tuning goes south - then we end up in using database specific features .

I think they are not going far enough in being independent. I have a Modest Proposal: They should only write computer programs in computer language independent code. It should be code that could be compiled to run on any machine no matter how modern or ancient. After all computer code is just a bunch of bits, 1's and 0's.

If we wrote computer programs in hardware,OS, and compiler independent code then it would be more portable and encourage more competition.

I think we should act on this modest proposal swiftly. :)

does someone know about the Entity Data Model and about Entity SQL ?

is it compareable with hibernate? it promises to free the user from the datastructure... does Oracle also have tools like this ?

It's very difficult to find literature about entity SQL and I am using Oracle, cause the Microsoft SQL-Sever is extremly slow compare with Oracle - so is it worthed to have a close look at it ?

thanks in advange !

"I use Oracle because MS SQL is slower compared to Oracle"
There are various ways to make things go slow and various means to make it go fast as Tom says. To borrow a phrase it always comes down to "Understand thy database".

commiting frequently is an approach in taken in MS SQL to make transactions short as possible, but in oracle its a recipe for make things go slow. eg: inserting data in a loop and commiting frequently.
If i look at a piece of code build on MS SQL and ported to Oracle then i can say that MS SQL is faster than Oracle!!.