Im facing a problem I definately need help.

We are running a jboss 4.2 eap with hibernate 3.2.1 using a second-level and query cache. Hibernate is running against a really slow oracle 9i db.

During simple not parallel tests things are working fine.
The query cache as well as the entity cache is constantly growing and the response times become very fast starting with a seond call to the same finder.

The situation changes once I run parallel tests against the appserver.
Important to know is that a first query might take up to 3 seconds !!!

The same query having exactly the same parameters appear more than one time in the list of cached queries.
The merely differentiate on the first value param ( i reckon that this is a timestamp ).

log snipplet:
/sql: select /*+ USE_NL(c d cd) */ c.document_id, c.title, c.label, c.released, c.cover_jpg_mid, c.ftm_containertype_id, c.full_artists, c.product_id, c.genre_id, cd.usr_rating_avg from j_ftm_container c, (select row_number() over(order by part, pos nulls last) pos, part, document_id from (select min(part) part, min(pos) pos, document_id from (select * from (select 'a static' part, row_number() over(order by t.sort desc nulls last) pos, t.document_id from j_documenttree t where t.parent_id = (select id from j_documenttree where path = ? and name = ? and state_id = 2) and exists (select 1 from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?) and d.ftm_container_document_id = t.document_id and d.released between ? and ?)) where pos <= ? union select * from (select 'b dynamic' part, row_number() over(order by d.released desc nulls last) pos, d.ftm_container_document_id from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.released between ? and ? and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?)) where pos <= ?) group by document_id)) d, j_ftm_containerdetail cd where d.pos between ? and ? and d.document_id = c.document_id and c.document_id = cd.ftm_container_document_id; parameters: ; named parameters: {country_id=1, containertype_id3=3, pos_start=1, containertype_id6=6, containertype_id2=2, date_start=Thu Feb 01 00:00:00 CET 1900, containertype_id5=5, containertype_id1=1, pos_end=10, date_end=Thu Nov 22 23:59:59 CET 2007, containertype_id4=4, documenttree_path=root/full_length/catalog/Startpage, documenttree_name=Neuheiten}
item: [11957355210, 6721540, 6934684, 6540921, 6542774, 6713918, 6717515, 6714902, 6714040, 6873547, 6721517]

/sql: select /*+ USE_NL(c d cd) */ c.document_id, c.title, c.label, c.released, c.cover_jpg_mid, c.ftm_containertype_id, c.full_artists, c.product_id, c.genre_id, cd.usr_rating_avg from j_ftm_container c, (select row_number() over(order by part, pos nulls last) pos, part, document_id from (select min(part) part, min(pos) pos, document_id from (select * from (select 'a static' part, row_number() over(order by t.sort desc nulls last) pos, t.document_id from j_documenttree t where t.parent_id = (select id from j_documenttree where path = ? and name = ? and state_id = 2) and exists (select 1 from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?) and d.ftm_container_document_id = t.document_id and d.released between ? and ?)) where pos <= ? union select * from (select 'b dynamic' part, row_number() over(order by d.released desc nulls last) pos, d.ftm_container_document_id from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.released between ? and ? and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?)) where pos <= ?) group by document_id)) d, j_ftm_containerdetail cd where d.pos between ? and ? and d.document_id = c.document_id and c.document_id = cd.ftm_container_document_id; parameters: ; named parameters: {country_id=1, containertype_id3=3, pos_start=1, containertype_id6=6, containertype_id2=2, date_start=Thu Feb 01 00:00:00 CET 1900, containertype_id5=5, containertype_id1=1, pos_end=10, date_end=Thu Nov 22 23:59:59 CET 2007, containertype_id4=4, documenttree_path=root/full_length/catalog/Startpage, documenttree_name=Neuheiten}
item: [11957353586, 6721540, 6934684, 6540921, 6542774, 6713918, 6717515, 6714902, 6714040, 6873547, 6721517]

Dont know if its important but isolation level is REPEATABLE_READ and locking schem is PESSIMISTIC

Any help would be more than appreciated ...

thanks

I have the same problem. Our app uses session.find() extensively and I had hoped that the query cache would ride to my rescue. Instead, I find that Hibernate's UpdateTimestampsCache gets invalidated by concurrent threads that update the same tables, even if those concurrent updates do not actually change the query result sets.

Worse, UpdateTimestampsCache is synchronized and I am getting very bad lock contention.

I've given up on it for now and instead opt for a more targeted and application-specific caching strategy.