We have a database query that is taking an unusually long time to complete. To make matters worse, it is not scaling linearly (a year ago: 6 hours, now: 23 hours), and we expect more records to populate the tables over the next few months which will compound the issue.
The approach we've taken is to isolate the query and have it execute on a subset. So far, we've executed this twice and I have AWR reports as well as process details from OVPA. I've been told the DBAs have already optimized the tables and system as best as possible, and that the developer has optimized the query.
So my question is this: as a performance tester, what can I do with the AWR report and the OVPA process details to help guide us toward solving the performance issues? Any correlations or measures that should stand out as being the low hanging fruit to go after when trying to identify potential risk areas to investigate?
I realize that database tuning is a science in and of itself, and I don't expect exhaustive responses. So even pointers to research areas would be helpful.
I'd suggest that you make sure that it's been optimised for the correct version of Oracle - or the correct version that your oracle db is set up to use (optimisation is way different in 9 as opposed to 10, although you can set 10 to use 9 optimisation, if you ant to)
not familiar with the OVPA, but I am used to looking at AWR reports - but mainly only at the worst executing SQL bits
how big is the DB?
how many records were in the table(s) the query is accessing last year, how many this year?