This service will be undergoing maintenance at 00:00 UTC, 2016-08-01. It is expected to last about 1 hours
Bug 722600 - consider adding index to RHQ_MEASUREMENT_SCHED.RESOURCE_ID column as recommended by Oracle EM to improve performance of call-time data purge delete queries
consider adding index to RHQ_MEASUREMENT_SCHED.RESOURCE_ID column as recommen...
Status: CLOSED CURRENTRELEASE
Product: RHQ Project
Classification: Other
Component: Core Server (Show other bugs)
4.0.1
Unspecified Unspecified
high Severity high (vote)
: ---
: ---
Assigned To: Robert Buck
Mike Foley
:
Depends On:
Blocks: jon3 jon30-perf rhq41beta
  Show dependency treegraph
 
Reported: 2011-07-15 15:32 EDT by Ian Springer
Modified: 2013-08-05 20:39 EDT (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2012-02-07 14:29:33 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:


Attachments (Terms of Use)
new Oracle index does indeed exist -- verification (77.60 KB, image/png)
2011-08-10 15:25 EDT, Mike Foley
no flags Details

  None (edit)
Description Ian Springer 2011-07-15 15:32:22 EDT
This was recommended by Oracle EM in our perf environment:

SQL Text
--------
delete from RHQ_CALLTIME_DATA_KEY where SCHEDULE_ID in (select id from RHQ_MEASUREMENT_SCHED measuremen1_ where measuremen1_.RESOURCE_ID in (:1 ))
	
Select Recommendation
---------------------	 		
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.
Comment 1 Robert Buck 2011-08-02 09:55:09 EDT
Diffs Here:

http://git.fedorahosted.org/git/?p=rhq/rhq.git;a=commitdiff_plain;h=5697849505fb1a792f3a45935b82aeb6ea3f0234

Branch: feature/performance

Commit:

commit 5697849505fb1a792f3a45935b82aeb6ea3f0234
Author: Robert Buck <buck.robert.j@gmail.com>
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.
Comment 2 Robert Buck 2011-08-08 16:20:58 EDT
Committed this to master in:

d7e9e35c5326c1c36f1d323fc5448d703f225bca
d678847b36a71932f8c7d7cd1c7fd4296378b44f
Comment 3 Mike Foley 2011-08-10 15:25:05 EDT
attaching image documenting that this index does indeed exist in RHQ 4.1 Beta.
Comment 4 Mike Foley 2011-08-10 15:25:47 EDT
Created attachment 517679 [details]
new Oracle index does indeed exist -- verification
Comment 5 Mike Foley 2011-08-10 15:31:05 EDT
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.
Comment 6 Mike Foley 2012-02-07 14:29:33 EST
changing status of VERIFIED BZs for JON 2.4.2 and JON 3.0 to CLOSED/CURRENTRELEASE
Comment 7 Mike Foley 2012-02-07 14:30:19 EST
marking VERIFIED BZs to CLOSED/CURRENTRELEASE

Note You need to log in before you can comment on or make changes to this bug.