Red Hat Bugzilla – Full Text Bug Listing
|Summary:||SQL Performance investigation|
|Product:||[Other] RHQ Project||Reporter:||Charles Crouch <ccrouch>|
|Component:||No Component||Assignee:||RHQ Project Maintainer <rhq-maint>|
|Status:||CLOSED CURRENTRELEASE||QA Contact:||Mike Foley <mfoley>|
|Version:||1.2||CC:||cwelton, dlackey, hbrock, hrupp, jshaughn, mazz|
|Target Milestone:||---||Keywords:||FutureFeature, Task|
|Fixed In Version:||Doc Type:||Enhancement|
|Doc Text:||Story Points:||---|
|Last Closed:||2014-05-09 11:59:21 EDT||Type:||---|
|oVirt Team:||---||RHEL 7.3 requirements from Atomic Host:|
|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: 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.