Queries in a Grail/Hibernate environment

Does anyone have good solutions to an issue I'm experiencing with using DPA in and environment where the use of Grail/Hibernate by the developers is causing the queries to appear in DPA as different queries. This make it difficult to see trends. The queries are treated as different in DPA, since the SQL text is different.

The root of the problem is that with Grail/Hibernate, the query can apparently have changing aliases for the column name.

I found this old feature request on Thwack.

https://thwack.solarwinds.com/ideas/3908#comment-203308

Does anyone have any other experiences? Is there another way to deal with this? What terminology should I use when talking to the developer and asking them if they have the option to tell Grail/Hibernate not to do this.?

Thanks!

Find more posts tagged with

hibernate

grail

oracle_queries

dpa

Accepted answers

All comments

mandevil

Unfortunately, it's not us seeing the statement as different, it's the database engine that's assigning a different hash/handle that we rely on.

If they are using dynamic SQL generation, what they may want to do is look into using bind variables (that's more from a generic development perspective). I'm not familiar with Grail/Hibernate, so perhaps others can chime in?

m60freeman

The issue isn't one of parameters or bind variables. Grails/Hibernate seems to randomly generate SQL with different column aliases for the same query. For example:

(@P0 bigint)

SELECT setracking0_.OrderNum as OrderNum9_1_,

setracking0_.AutoNumber as AutoNumber1_,

setracking0_.AutoNumber as AutoNumber2_0_,

setracking0_.Carrier as Carrier2_0_,

setracking0_.OrderNum as OrderNum2_0_,

setracking0_.TrackingId as TrackingId2_0_

FROM SETrackingView setracking0_

WHERE setracking0_.OrderNum=@P0

Tomorrow, "OrderNum9_1_" could be "OrderNum4_1_" (likewise with the other aliases) for no reason that I understand. (Dont' even get me started about why the same column is requested multiple times i the same SELECT with different aliases.)

I'm a DBA, not a Grails/Hibernate developer. It would be unreasonable to expect that DPA will ever be able to automatically identify these variants as the same query on its own, but we do really need the ability to group them ourselves as they turn up.