Bug 535793 - (RHQ-2452) SQL Performance investigation
SQL Performance investigation
Status: CLOSED CURRENTRELEASE
Product: RHQ Project
Classification: Other
Component: No Component (Show other bugs)
1.2
All All
medium Severity medium (vote)
: ---
: ---
Assigned To: RHQ Project Maintainer
Mike Foley
http://jira.rhq-project.org/browse/RH...
: FutureFeature, Task
Depends On:
Blocks: jon24-perf rhq-perf
  Show dependency treegraph
 
Reported: 2009-10-04 15:06 EDT by Charles Crouch
Modified: 2015-02-01 18:25 EST (History)
6 users (show)

See Also:
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:


Attachments (Terms of Use)

  None (edit)
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:
INSERT INTO RHQ_MEASUREMENT_DATA_NUM_1H (
SELECT :1, ft.schedule_id, AVG(value), MIN(value), MAX(value)
FROM RHQ_MEAS_DATA_NUM_R14 ft
WHERE ft.time_stamp >= :2 AND ft.time_stamp < :3
GROUP BY ft.schedule_id)

Plan:
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)

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



2) Deleting from RHQ_AVAILABILITY
DELETE FROM RHQ_AVAILABILITY WHERE END_TIME < :1

Plan:
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)

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


3) Max timestamp from 1hour table:
SELECT MAX(time_stamp) FROM RHQ_MEASUREMENT_DATA_NUM_1H

Plan:
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)

Timings:
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.

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