[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster

[Loïc LEFEVRE (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28139 ] 

Loïc LEFEVRE commented on HHH-2448:
-----------------------------------

A quick comment on this (real life feedback):

We had 2 crashes of our production databases because of a tricky issue. We had set the Oracle parameter CURSOR_SPACE_FOR_TIME to TRUE and the Statement Cache Size parameter to 256 on our weblogic cluster (8 instances).

Since, 256 SQL statements could be kept open per database Connection, and because Oracle was not freeing all the allocated memory per cursors (CURSOR_SPACE_FOR_TIME=TRUE), our shared pool was filled in 2 weeks => fragmentation implied no possibility of new memory allocation for medium "big" (4200+ characters).

Of course since we use Hibernate in a weblogic cluster environment and since this issue is still open, this phenomenon was of course magnified by a *maximum ratio* of 8 (i.e. eight times the amount of Oracle memory required for one query).


> Generate identical column aliases among cluster
> -----------------------------------------------
>
>                 Key: HHH-2448
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
>             Project: Hibernate3
>          Issue Type: Improvement
>          Components: query-criteria, query-hql
>    Affects Versions: 3.2.2
>         Environment: Hibernate version: 3.2.2, 2.1.8
> Database: Oracle 9.2.0.8
>            Reporter: Loïc LEFEVRE
>         Attachments: report_1.txt, report_2.txt
>
>
> Among our weblogic cluster (12 instances), we can see that a query can have different column aliases.
> Although all seems correct, when regarding database reports like StatsPack or Spotlight we can see that because of these different aliases, the reports are wrong. Indeed, a resource consuming query can see its associated report properties (cpu usage, buffer gets, number of executions...) divided by the number of weblogic instances of our cluster (i.e. divided by 12) thus preventing us to pinpoint the queries to look at.
> On a 3 instances cluster, we can see this report:
> REPORT#1: one statement with a poor number of buffer gets/execution is reported splitted in 3, see the alias generated for column DTO.CREATION_DATE
> for example
> On a 4 instances cluster, we can see this report:
> REPORT#2: one statement responsible of the latch free/cache buffers chains wait events splitted in 4, note the column alias generated fordeffcashcy0_.BEST_EXPECTED_CY
> More than confusing the DBAs about the same query with n different "faces", our reports don't show us all the queries to look at: indeed, in our "Top 50 queries", a lot of them are duplicates! Also the memory required in the SGA to store the queries, the execution plan and so on is increased...
> Finally, although the column aliases can have up to 30 characters under Oracle, the limit is set to 10, why? 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira