sql_sql_id.sql 1.7

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.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s