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
This bug was previously known as http://jira.rhq-project.org/browse/RHQ-2452
mass move off the qa triage list. These are tasks for dev.
Mazz -- what would it take to fix this? Deon -- can/has the workaround been doc'd?
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?
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.
Oh, if Heiko addressed this already, then I may just add a general "performance was tuned" thing to the release notes.