This was recommended by Oracle EM in our perf environment:
delete from RHQ_CALLTIME_DATA_KEY where SCHEDULE_ID in (select id from RHQ_MEASUREMENT_SCHED measuremen1_ where measuremen1_.RESOURCE_ID in (:1 ))
New Explain Plan
Index The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.
JON.RHQ_MEASUREMENT_SCHED("RESOURCE_ID") Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.
We should try adding this index and see if EM stops reporting the call-time purge queries as problematic.
Author: Robert Buck <firstname.lastname@example.org>
Date: 2011-07-28 18:06:59 -0400
[BZ 722600] Add index to RHQ_MEASUREMENT_SCHED.RESOURCE_ID column as recommended by Oracle EM to improve performance.
Committed this to master in:
attaching image documenting that this index does indeed exist in RHQ 4.1 Beta.
Created attachment 517679 [details]
new Oracle index does indeed exist -- verification
verified as follows:
1) index exists in oracle
2) basic functional use-case works (monitoring of traits, metrics, call times
3) no SQL or hibernate errors in RHQ log file
4) out-of-scope: did not verify the performance improvement of the addition of this index.
changing status of VERIFIED BZs for JON 2.4.2 and JON 3.0 to CLOSED/CURRENTRELEASE
marking VERIFIED BZs to CLOSED/CURRENTRELEASE