10:09 a.m.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-sql
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
10:21 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2448) Generate identical column aliases among cluster
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=all ]
Max Rydahl Andersen updated HHH-2448:
-------------------------------------
Component: (was: query-sql)
query-hql
query-criteria
this is actually query-hql and query-criteria in relation to alias generation which
apparently is not deterministic.
one issue i know is that Table has a static variable counter that affects the alias
genreation.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
10:37 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Emmanuel Bernard commented on HHH-2448:
---------------------------------------
While I agree that determinism is preferrable, report and stats tools should be able to
merge queries only having alias diffs.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
10:39 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Emmanuel Bernard commented on HHH-2448:
---------------------------------------
Note that in Annotations I did fix a deterministic issue wrt property configuration and it
definitely affects column ordering. Can you give it a try with the next 3.2.2 version of
HAN (or SVN Branch_3_2)
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
11:06 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Loïc LEFEVRE commented on HHH-2448:
-----------------------------------
The problem, is we don't use annotations and we are using JDK 1.4.2.
About the "should be able to merge" part, it is not possible, the text of SQL
queries stored in the SGA are accessible:
- either in v$sql (first 1000 chars),
- either in v$sqltext (or v$sqltext_with_newlines) by chunks of 64 chars
Merging statistics of queries bypassing the column aliases would mean rewriting in SQL an
SQL parser...
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
11:24 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Loïc LEFEVRE commented on HHH-2448:
-----------------------------------
Moreover, the memory consumption is not solved as well as the CPU used for hard parsing
over and over the same querieS...
Given a n-cluster, we can have memory consumption and CPU utilization multiplied up to n
times.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
12:17 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Max Rydahl Andersen commented on HHH-2448:
------------------------------------------
yes, it is not the issue of the reporting tool - it's the matter of the sql parsers on
oracle (and probably other db's) apparently does not utilize the optimization of
"merging" queries that only have alias diffs.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
7:05 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Loïc LEFEVRE commented on HHH-2448:
-----------------------------------
Following is the answer of Thomas Kyte on the subject:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:92062...
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
9:23 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Christian Bauer commented on HHH-2448:
--------------------------------------
Yeah, the usual Oracle "we don't really know how to do this stuff" excuse.
It should be obvious that you can separate aliases from queries, and merge/cache the
latter.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
10:01 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Max Rydahl Andersen commented on HHH-2448:
------------------------------------------
I don't actually think this is an oracle only issue. would be good to get verification
from sql server and db2 users since i remember having issues with this in another context
than hibernate; but that was a couple of years back.
Note as Thomas says the ordering is different which does make it harder to match so could
be cool to know that if the queries were to be equal in ordering and syntax except for the
aliases if oracle would be able to optiimze that.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
10:15 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Steve Ebersole commented on HHH-2448:
-------------------------------------
Guys, it is not quite so easy. In order for the DB to know that an incoming query only
differs from any other query based on the defined return aliases, the DB would need to
perform at least some degree of lexical analysis on that incoming query. At the very
least that lexical analysis would be needed to determine the overal structure and identify
the tokens which are aliases. So where is that fulcrum? This is exactly why we do not
cache criteria query plans, and our caching of HQL query plans has this same exact
characteristic.
And really, is this a requirement for the underlying DB? This is something Hibernate
could be made to do.
However, this is obviously fairly low on my radar ;)
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate3
Type: Improvement
Components: query-hql, query-criteria
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
12:05 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:43 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Loïc LEFEVRE commented on HHH-2448:
-----------------------------------
I can see, nobody answered my proposition:
"Finally, although the column aliases can have up to 30 characters under Oracle, the
limit is set to 10, why? "
is this easily feasible?
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:57 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Brian Curnow commented on HHH-2448:
-----------------------------------
We need to have a solution. The issue at hand is not whether Oracle/monitoring tools
should be able to handle SQL that differs only by alias, we know they can't and
that's the world we live it today. The issue is that the current way Hibernate is
generating those aliases is causing production issues.
Is anyone working on this problem?
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:15 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Emmanuel Bernard commented on HHH-2448:
---------------------------------------
Not at the moment. It would be great to have someone of the community step up on that
one.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:24 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Loïc LEFEVRE updated HHH-2448:
------------------------------
Attachment: report_03.txt
Okay after some more investigation, I can say that columns aliases are mainly compound
of:
-1- the first letters -- including the '_' character or not (depends upon the
version, commented right now) --
of the column: if column.name = TOTO_OID keep only TOTO_ or TOTO so that the length of
"TOTO" + uniqueInteger + "_" is 10 chars long
-2- the unique integer of the table the column belong to + a trailing '_' (the
unique integer of the table is generated in the constuctor
of the org.hibernate.mapping.Table class and depends of the mapping loading *order*
Whereas the attachment report_1.txt shows differences in columns aliases naming regarding
the column unique integer, we decided to
load all the mappings at application starting so now the first unique integer
"bug" is corrected.
However we then encountered problems regarding the table unique number (see report_3.txt).
And since this is caused by mapping load ordering,
I checked our java code doing this job and found we first load all the mapping filenames
in a HashSet and then we iterate on this to load all
mappings using org.hibernate.cfg.Configuration.addClass() and I'm sure the HashSet is
the cause of our problems. If I use a TreeSet, then the
(same) mappings will be loaded in the same order on any application server instance of our
cluster.
It seems these choices will (theoretically) prevent our problems but we'll try before
saying so.
By the way, this can lead to a warning regarding mapping best practices in a cluster
environment.
Regards,
Loïc
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_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:52 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Loïc LEFEVRE commented on HHH-2448:
-----------------------------------
As a final comment, I can say that our problem has gone with the previously explained
correction.
Consequences for our database: less parse, better perfs
Advice: include this best practice in documentation when dealing with clustered
application (running on more than one JVM).
Regards,
Loïc
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_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:44 p.m.
New subject: [Hibernate-JIRA] Updated: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Oscar Pearce updated HHH-2448:
------------------------------
Attachment: consistent_table_aliases.patch
The following patch (against 3.3.1) assigns consistent unique ids to tables, at least when
using class Hibernate configuration (not tested with annotations or JPA, but I expect it
to work in those cases too).
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate Core
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: consistent_table_aliases.patch, report_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:17 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Charles Canning commented on HHH-2448:
--------------------------------------
What is the status of this now? Has any of the suggestions made it into the 3.2.x or later
branches? This has also been problematic for us for years. We have 10x more queries cached
in our SGA so it is rendered ineffective.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate Core
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: consistent_table_aliases.patch, report_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:27 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Charles Canning commented on HHH-2448:
--------------------------------------
Just a random thought, would it be possible to not use aliases? Would that have a big
impact on the hibernate codebase?
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate Core
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: consistent_table_aliases.patch, report_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:59 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Pierce Wetter commented on HHH-2448:
------------------------------------
I would like to know about the status of this item in 3.5+?
FYI, someone asked about DB2. DB2 can cache prepared statements based on the hash of the
SQL. I suspect Oracle is the same. So as Steve Ebersole points out, the SQL should be
similar each time.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate Core
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: consistent_table_aliases.patch, report_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:30 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Denis Cabasson commented on HHH-2448:
-------------------------------------
The patch seems to make sense to me and should fix the core issue. Why has it been sitting
like that for the past 2 years? Is there a reason it's not good enough? The table are
in a treemap so ordering should be consistent, the patch makes sense in the way of
re-numbering the tables once we have all of them and we are sure about which goes where.
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate Core
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: consistent_table_aliases.patch, report_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:19 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2448) Generate identical column aliases among cluster
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448?page=c...
]
Paulo Silveira commented on HHH-2448:
-------------------------------------
I am also curious about the patch application. Any plans?
Generate identical column aliases among cluster
-----------------------------------------------
Key: HHH-2448
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2448
Project: Hibernate Core
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: consistent_table_aliases.patch, report_03.txt, 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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:34 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
4:10 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
3:10 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
4:08 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
12:01 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
12:26 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
1:20 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
3:02 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
3:43 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
3:50 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
3:54 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
4 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
10:15 a.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
11:42 a.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
11:42 a.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
12:15 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
5:32 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
6:56 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
7:20 a.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
12:35 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
2:36 p.m.
New subject: [Hibernate-JIRA] (HHH-2448) Generate identical column aliases among cluster
4113
days inactive
6279
days old
hibernate-issues@lists.jboss.org
43 comments
15 participants
participants (15)
-
Bill Conner (JIRA) -
Brett Meyer (JIRA)
-
Brian Curnow (JIRA)
-
Charles Canning (JIRA)
-
Christian Bauer (JIRA)
-
Denis Cabasson (JIRA)
-
Emmanuel Bernard (JIRA)
-
Loïc LEFEVRE (JIRA)
-
Max Rydahl Andersen (JIRA)
-
Oscar Pearce (JIRA)
-
Paulo Silveira (JIRA)
-
Pierce Wetter (JIRA)
-
Steve Ebersole (JIRA)
-
Strong Liu (JIRA)
-
Vinay Gajjala (JIRA)