Here is thing with SQL tuning: at a certain point you encounter an inefficient SQL statement. You look at it with for example Active Session History. So you find out it does a full table scan on a big table. Three weeks and one unrelated time intensive project later, you are in a meeting. A collegue asks you about that performance issue you looked at. Wasn’t there a SQL you found? “Yes indeed”, you reply, while you try to find on the SSD disk in your head what that was about again. “There was a full table scan .. and… it was on this very big table… 200 million records I think it was. What was that name again??”
Quickly after the meeting you search AWR, only to find out that there is only one week worth of snapshots there. Because, well, everybody agreed that would be enough and hard drive data is so expensive these days.
This is one of the reasons why I created sql_sql_id.sql. It looks up a lot of information about a SQL statement (based on a SQL_ID and child_number) and saves it to a .txt file. Information like:
- Different childs of the statement
- The full text of the SQL statement
- The execution plan (of course)
- Executions, numbers of rows gotten, memory and disk reads.
- SQL plan baseline info
- Bind mismatches
- Examples of bind data (including handy SQL*Plus commands for bind variables, for replay).
What information doesn’t it gather these days? I’ve put in there everything I could think of.
It helped me quite a lot. Maybe it helps you too.