Today, I spent a fair amount of time anaylizing a few statements. One of them was used to retrieve data for display on a webpage and it took almost 4 seconds to execute this statement. This is still acceptable but could be better. Tuning this will not be an easy task and is out of the scope of this blog post. But I had one question that was really nagging me all day: How long did this statement take in the past? I could use this information to assess if this is a new problem or not and also estimate/project if this will get even worse in the future.
After a while I remembered that Oracle already stores most of these statistics in the active session history so retrieving and analyzing becomes a very simple task. I decided to compare the weekly average execution time with this statement:
SELECT trunc(begin_interval_time, 'WW'), ROUND(sum(elapsed_time_delta)/sum(executions_delta)/1000) FROM dba_hist_sqlstat a JOIN dba_hist_snapshot b ON a.snap_id = b.snap_id WHERE sql_id = 'avaavbkx3017y' AND executions_delta>0 GROUP BY trunc(begin_interval_time, 'WW') ORDER by trunc(begin_interval_time, 'WW') DESC
The result was that this statement has always taken about 4 seconds for as long as I have ASH data. Neat.
In the process of all this I also found out that by saving a statement as a report you can even generate a nice chart directly from SQLDeveloper. The only catch is that you need to specify three columns when you do this. The error message “Invalid column index: getValidColumnIndex” did not help a whole lot in diagnosing this but eventually I found this blog post by Sue Harper. Too bad she left Oracle a few months ago.
I know that I am not a pioneer in this, so if you know of good resources, ASH scripts and such, please leave a comment.