The query of concern is: select alertCondLog.ID, alertCondLog.CTIME, alertCondLog.VALUE, alertCondLog.ALERT_ID, alertCondLog.CONDITION_ID from RHQ_ALERT_CONDITION_LOG alertCondLog, RHQ_ALERT_CONDITION alertCond where alertCondLog.CONDITION_ID = alertCond.ID and alertCond.ALERT_DEFINITION_ID=:1 and (alertCondLog.ALERT_ID is null) The cardinality of the RHQ_ALERT_CONDITION_LOG and RHQ_ALERT_CONDITION tables in this case were 2963114 and 5268, respectively.
branch: feature/performance commit 5b82b1e38bc6df304ae5eb11dfa87a13091c20d5 Author: Robert Buck <buck.robert.j> Date: 2011-07-28 18:22:40 -0400 [BZ 726524] Consider adding an index for the RHQ_ALERT_CONDITION_LOG.CONDITION_ID to optimize queries.
Diffs Here: http://git.fedorahosted.org/git/?p=rhq/rhq.git;a=commitdiff;h=5b82b1e38bc6df304ae5eb11dfa87a13091c20d5
Fixed and merge to master in: 6328219f0cb2a251463111bf5f481d92fafc78a6 d7e9e35c5326c1c36f1d323fc5448d703f225bca
verified as follows: visual inspection of an oracle database to verify the presence of this new index, functional testing of alerts, reviewed server logs looking for sql or hibernate errors (none).
changing status of VERIFIED BZs for JON 2.4.2 and JON 3.0 to CLOSED/CURRENTRELEASE