Okay dont beat me up too bad, this is my first time posting on this forum.

My lead is thinking of trying to lock oracle 10g plans for hiberante queries. I was looking at the generated sql and I see some items that make me nervous. One thing is the column aliases.

For example:

select alertdo0_.DC_ALERT_SYSID as DC1_23_, alertdo0

Where does the "23" part come from? I run in JBoss and always get "23", but when I run outside the container in JUnit I get a different number, but it is consistent as well.

Locking oracle plans requires identical SQL each time. I am feeling like this is a bad idea to attempt with generated SQL, certainly wont work for variable length "IN" clauses. But if the column aliases are not gauranteed to stay constant simply will not work for anything.

We've noticed the same thing. Stumbled across this post searching for how to lock the column aliases down for the same reason - want to use Oracle 10g SQL Profiles. We need to workaround random, infrequent problems with Oracle using the wrong execution plan.

Yes, I know we can use query hints, but SQL Profiles looked like a nicer option, and something the DBA could have done without needing developer time.

Anyways, we've noticed that when running the application normally, the number is the position of the table in the Hibernate configuration file. So, I suspect the table you are querying from is the 23rd (or 22nd, might be zero-based) in the list in your configuration.

PS: same question as this unanswered thread: http://forum.hibernate.org/viewtopic.php?t=971156

I've had more of a look into it myself now. At least as far as Hibernate 3.2 is concerned, we have no control.

The org.hibernate.mapping.Table default constructor takes the value of a static int (tableCounter) as it's uniqueInteger class variable, then increments tableCounter (and it's zero based, so the "23" in your example means that the table it's referring to was the 24th that happened to be created in your system.

That number is then used by the org.hibernate.mapping.Column class' getAlias() method. That method is what creates the "DC1_23_" string. There is no way to change this behaviour. It's annoying, but I can see why it's done that way - consider selecting from two tables, both of which have an "id" column.

If you desperately want to use Oracle's SQL Profiles, I think you'll be safe so long as you never insert a new table in the middle of the configuration - eg to keep the list in alphabetical order. And you always setup your entire Hibernate system in any testing scenario. We just got hit by this on our last production update, the one SQL Profile we had (so far) fell off because we went from 104 to 106 :(

It would be nice one day to be have the option to provide our own unique table number in the configuration and have Hibernate barf during startup if there is a clash, but I doubt we'll see it - how many people will want to be using Hibernate and care about Oracle's SQL Profiles?

thanks for looking into this. We are hoping not to have to use stored outlines but are looking into what our obstacles will be is we have to do so.

What are the circumstances under which the number part of the alias changes?

We have an online app running across a number of clustered servers and the DBAs are questioning why they are seeing what look like functionally the same SQL statement but with variations in the columns alias, usually in the number part of the alias. The concern is that with some statements that are executing millions of times a day we are not maximizing our use of the statement cache in DB2 because we have lots of variations of the same SQL statement, only varying in the column aliases, but it is still the same SQL statement. It needs to be identical to get a hit on the DB2 statement cache.

I haven't looked at the Hibernate source mentioned in this thread yet, but I'll do that next to get a better understanding of how this works.

Reading the above posts, if the number is based on the position of the mapping file listed in the configuration file, if we are seeing the same SQL statement hit the db but with a different numeric part in the alias then this implies we have different versions of the configuration file deployed across our nodes in our WAS cluster? There's always a possibility, but with the same EAR deployed to to one Websphere ND node which ripples out the EAR to all the nodes in the cluster I don't see (at this point) how this can be possible?

Thanks, Kevin

Did we find the solution for this. It is the same problem where DBAs are not able to stabillize the plan because of this issue.