Bug 535793 (RHQ-2452)

Summary: SQL Performance investigation
Product: [Other] RHQ Project Reporter: Charles Crouch <ccrouch>
Component: No ComponentAssignee: RHQ Project Maintainer <rhq-maint>
Status: CLOSED CURRENTRELEASE QA Contact: Mike Foley <mfoley>
Severity: medium Docs Contact:
Priority: medium    
Version: 1.2CC: cwelton, dlackey, hbrock, hrupp, jshaughn, mazz
Target Milestone: ---Keywords: FutureFeature, Task
Target Release: ---   
Hardware: All   
OS: All   
URL: http://jira.rhq-project.org/browse/RHQ-2452
Fixed In Version: Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2014-05-09 11:59:21 EDT Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---
Bug Depends On:    
Bug Blocks: 577041, 620933    

Description Charles Crouch 2009-10-04 15:06:00 EDT
From case: 350183. A customer has captured some oracle trace files and is looking for feedback on whether any improvements can be made to the performance of certain sql statements, 1) and 2) below. 3) is one statement I saw that I thought could be worth some investigation:

1) Inserting into 1hour table:
SELECT :1, ft.schedule_id, AVG(value), MIN(value), MAX(value)
WHERE ft.time_stamp >= :2 AND ft.time_stamp < :3
GROUP BY ft.schedule_id)

SORT GROUP BY NOSORT (cr=644,930 pr=77 pw=0 time=11.3172s)
FILTER (cr=644,930 pr=77 pw=0 time=8.5638s)
TABLE ACCESS BY INDEX ROWID RHQ_MEAS_DATA_NUM_R14 (cr=644,930 pr=77 pw=0 time=7.0275s)
INDEX FULL SCAN RHQ_MEAS_DATA_NUM_R14_ID_T_PK (cr=11,853 pr=26 pw=0 time=1.9006s)

db file sequential read 	 77.0% 	 73.7165s 	
EXEC calls [CPU] 	         22.8% 	21.7900s

2) Deleting from RHQ_AVAILABILITY

DELETE RHQ_AVAILABILITY (cr=80,497 pr=80,178 pw=0 time=39.3276s)
TABLE ACCESS FULL RHQ_AVAILABILITY (cr=80,497 pr=80,178 pw=0 time=39.3276s)

db file scattered read 	 79.0% 	 29.2807s 
EXEC calls [CPU] 	20.8% 	7.7200s 	

3) Max timestamp from 1hour table:

SORT AGGREGATE (cr=67,082 pr=66,252 pw=0 time=74.9341s)
INDEX FULL SCAN (MIN/MAX) RHQ_MEAS_DATA_1H_TIME_IDX (cr=67,082 pr=66,252 pw=0 time=74.9339s)

db file sequential read 	 89.5% 	 67.7660s 
FETCH calls [CPU] 	10.4% 	7.9100s 	
Comment 1 Red Hat Bugzilla 2009-11-10 16:04:43 EST
This bug was previously known as http://jira.rhq-project.org/browse/RHQ-2452
Comment 2 wes hayutin 2010-02-16 10:44:52 EST
mass move off the qa triage list.  These are tasks for dev.
Comment 3 Corey Welton 2010-08-30 13:42:27 EDT
Mazz -- what would it take to fix this?
Deon -- can/has the workaround been doc'd?
Comment 4 Deon Ballard 2010-08-30 14:02:09 EDT
I'll check again, but I don't think this has been documented anywhere. 

I can doc it. I just need some details:
1. When should this be done (i.e., is it a good change for every deployment or only in certain situations?)
2. What's the performance improvement?
3. Is this done at server install or can it be done at any point?
4. What functional areas does this affect?
5. Can this be done using that admin area for uploading sql commands or does this need to be done using Oracle management tools?
Comment 5 John Mazzitelli 2010-08-30 14:07:56 EDT
Heiko has recently done some performance tuning/testing re: DB performance. Since this is a year old, I would suspect the perf tuning he did either already covered this, this was already fixed, or its not a major bottleneck when compared to other parts that he did tune. Perhaps Heiko can provide additional info regarding what he tuned and if these kinds of queries have already been tuned.
Comment 6 Deon Ballard 2010-08-30 14:40:35 EDT
Oh, if Heiko addressed this already, then I may just add a general "performance was tuned" thing to the release notes.