Identifying Top SQL using statspack

So you don’t have diagnostic or the tuning pack for your databases, but you have started collecting information using Oracle Statspack that i mentioned in one of my previous posts. As well as running normal reports, you can also run sql directly against the tables to gather information on SQL Performance.   If you log into your DB using the user collecting the stats and run the following sql.

You will see a list like this:

Ideally you want to import this in Excel, then filter.  This allow you to order on any of the columns such as the sql that took the longest, to what sql was being executed repeatedly and taking time.  Thereafter find the sql using the sql hash value to see the full sql.  You can amend the sql to choose how many days you want to go back, the above sql looks back 24 hours.