Bug 1032192

Just for reference, the alert fetch stack trace:

        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
        at org.rhq.core.domain.util.JoinFetchReportingQueryTranslator.list(JoinFetchReportingQueryTranslator.java:84)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1246)
        at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
        at org.rhq.enterprise.server.alert.AlertConditionManagerBean.getAlertConditionComposites(AlertConditionManagerBean.java:158)

...

These are settings I use, but not sure if they help that much:

@@ -12,11 +12,14 @@
          <!--
          <property name="hibernate.show_sql" value="true" />
          <property name="hibernate.format_sql" value="true" />
-         <property name="hibernate.use_sql_comments" value="true" /> 
+         <property name="hibernate.use_sql_comments" value="true" />
          -->
          <!--<property name="hibernate.hbm2ddl.auto" value="create-drop" />-->
-         <property name="hibernate.jdbc.batch_size" value="30"/>
+         <property name="hibernate.jdbc.batch_size" value="100"/>
+         <property name="hibernate.jdbc.fetch_size" value="100"/>
          <property name="hibernate.cache.use_second_level_cache" value="false"/>
+         <property name="hibernate.order_inserts"   value="true"/>
+         <property name="hibernate.order_updates"   value="true"/>
 
          <!--<property name="com.intellij.javaee.persistence.datasource" value="rhq-oracle" />-->
          <property name="com.intellij.javaee.persistence.datasource" value="rhq-postgres" />
@@ -26,7 +29,7 @@
          <!-- This value doesn't have any theoretical grounds and is purely based on experiments. Value of 32 gives
               the best compromise between the quick load of queries with low and high limits (i.e. page size in tens vs.
               thousands) in an inventory with 30 agents and >20000 resources. -->
-         <property name="hibernate.default_batch_fetch_size" value="32"/>
+         <property name="hibernate.default_batch_fetch_size" value="300"/>
 
       </properties>
    </persistence-unit>

There have improvements been made to speed up alert reporting server side 
in master b84dcfa (and other commits before).
See also Bug 1037616

More logs from alert cache loading. It takes about 23 seconds to load 250 cache elements. Might be better to load more at once or something.

16:16:01,014 DEBUG [org.rhq.enterprise.server.alert.AlertConditionManagerBean] (pool-7-thread-1) Found 36068 elements of type 'Resource Availability for Duration', list was size 250
16:16:24,698 DEBUG [org.rhq.enterprise.server.alert.AlertConditionManagerBean] (pool-7-thread-1) Found 36068 elements of type 'Resource Availability for Duration', list was size 250

The bug is each alert composite load runs this query:

11:20:06,258 DEBUG [org.hibernate.SQL] (http-/127.0.0.1:8080-1) select alertcondi0_.ID as col_0_0_, alertdefin1_.ID as col_1_0_, resource2_.ID as col_2_0_, (select availabili3_.AVAILABILITY_TYPE from RHQ_AVAILABILITY availabili3_ inner join RHQ_RESOURCE resource4_ on availabili3_.RESOURCE_ID=resource4_.ID where resource4_.ID=resource2_.ID and (availabili3_.END_TIME is null)) as col_3_0_ from RHQ_ALERT_CONDITION alertcondi0_ inner join RHQ_ALERT_DEFINITION alertdefin1_ on alertcondi0_.ALERT_DEFINITION_ID=alertdefin1_.ID inner join RHQ_RESOURCE resource2_ on alertdefin1_.RESOURCE_ID=resource2_.ID where (alertdefin1_.RECOVERY_ID=0 or alertdefin1_.RECOVERY_ID<>0 and (exists (select alertdefin5_.ID from RHQ_ALERT_DEFINITION alertdefin5_ where alertdefin5_.ID=alertdefin1_.RECOVERY_ID and alertdefin5_.DELETED=false and alertdefin5_.ENABLED=false))) and (resource2_.AGENT_ID=? or ? is null) and alertdefin1_.ENABLED=true and alertdefin1_.DELETED=false and alertcondi0_.TYPE=? order by alertcondi0_.ID

This returns only the alert condition ID, not the full value.

Then for every entry, this query is run:

16:42:11,585 DEBUG [org.hibernate.SQL] (EJB default - 2) select alertcondi0_.ID as ID8_0_, alertcondi0_.ALERT_DEFINITION_ID as ALERT8_8_0_, alertcondi0_.TYPE as TYPE8_0_, alertcondi0_.COMPARATOR as COMPARATOR8_0_, alertcondi0_.MEASUREMENT_DEFINITION_ID as MEASUREM9_8_0_, alertcondi0_.NAME as NAME8_0_, alertcondi0_.OPTION_STATUS as OPTION5_8_0_, alertcondi0_.THRESHOLD as THRESHOLD8_0_, alertcondi0_.TRIGGER_ID as TRIGGER7_8_0_ from RHQ_ALERT_CONDITION alertcondi0_ where alertcondi0_.ID=?
...
16:42:11,674 DEBUG [org.hibernate.SQL] (EJB default - 2) select alertcondi0_.ID as ID8_0_, alertcondi0_.ALERT_DEFINITION_ID as ALERT8_8_0_, alertcondi0_.TYPE as TYPE8_0_, alertcondi0_.COMPARATOR as COMPARATOR8_0_, alertcondi0_.MEASUREMENT_DEFINITION_ID as MEASUREM9_8_0_, alertcondi0_.NAME as NAME8_0_, alertcondi0_.OPTION_STATUS as OPTION5_8_0_, alertcondi0_.THRESHOLD as THRESHOLD8_0_, alertcondi0_.TRIGGER_ID as TRIGGER7_8_0_ from RHQ_ALERT_CONDITION alertcondi0_ where alertcondi0_.ID=?

The solution is probably to force a load of the attribute. Trying to figure this out...

Merged associated pull request: https://github.com/rhq-project/rhq/pull/65

master commit ab6e5c067e4a81c3caa55b5d9f374bbdd2ed3093
Merge: 2f35925 3713036
Author: jshaughn <jshaughn>
Date:   Tue Aug 19 13:59:58 2014 -0400

    Merge pull request #65 from genman/BZ1032192

    BZ1032192 - AlertCondition composite queries, query each column




Testing Notes:
This is basically an internal change for performance.  Testing is covered by unit testing.