Explain Away Your Troubles - Page 2

Just after capture of the SQL, the next step is to run all of this SQL through a Perl script (Figure 3) which will massage the statements some. What this script does is to replace all strings and numbers to bind variables and to filter out any SQL statement that references a 'v$' table. The reason for replacing strings and numbers with bind variables is because if you didn't, you would run the risk of having thousands of the same SQL statement where the only difference was the string or number that was in the 'WHERE' clause. I strip out the statements that reference 'v$' tables because 99.9% of these are internal to Oracle and are of no interest to us. This simple script converts 98% of all my SQL statements properly. Please feel free to modify this script so you can achieve a higher percentage. When you run this script against the output from sqlexp_pull.sql you should pipe the output to another file; I use sqlexp_tmp.lst. I then get rid of all of the duplicate SQL statements in the sqlexp_tmp.lst by using the UNIX sort command and pipe it to another file sqlexp_put.lst. This file will now be used for loading the sqlexp_sqltext and sqlexp_plan_table tables.

Command to sort and get rid of duplicates:

sort   -u   $UTL_PATH/sqlexp_put.lst    $UTL_PATH/sqlexp_tmp.lst

Figure 3.

#! /usr/local/bin/perl
#
open(SQLEXPLST, "<valid utl_path>/sqlexp_pull.lst");
while ($sqltext = <SQLEXPLST>) {
  chop($sqltext);
  if ($sqltext =~ /^ *select\b/i) {
    ($dummy,$orderby) = split(/^ *select *.* *from *.* *order *by */i,$sqltext);
    if ( $orderby ne '' ) {
      $orderby = "order by ".$orderby;
    }
    $sqltext =~ s/,/ , /g;
    $sqltext =~ s/'/ ' /g;
    $sqltext =~ s/=/ = /g;
    $sqltext =~ s/\|\|/ , /g;
    $sqltext =~ s/\(/ \( /g;
    $sqltext =~ s/\)/ \) /g;
    $sqltext =~ s/'[^']*'/:string/g;
    $sqltext =~ s/[^:]\b\d+\b/ :num/g;
    $sqltext =~ s/order *by *.*/$orderby/i;
    if ($sqltext =~ / *.* *from *.*\$+.*/i) {
      $sqltext = "";
    }
  }
  if ($sqltext ne '') {
    print "$sqltext\n";
  }
}
close(SQLEXPLST);

At this point, I would like to state that you do not have to go through this process of extracting and parsing SQL statements from your database if you know what all your SQL statements are. Just put the SQL statements you want to track into the flat file 'sqlexp_put.lst' in your utl_path and proceed on from this point. The format for this file is such that each SQL statement must be on one line, don't worry about wrapping, this is what the load process wants.

The next step and true meat of this article is the script sqlexp_put.sql. This script allows you to do maintenance and reporting on the SQL statements either captured using the method above or already loaded into the 'sqlexp' tables. This script allows for a wide variety of actions to be performed. The most common actions, that I use myself, have been outlined in the following table. For all flags and values, please see the documentation at the top of the script.

Download sqlexp_put.sql

If you work in a hostile environment, or just want a neat little tool to track the SQL that is going against your databases, this is the tool for you. You will now have a way to proactively determine when an explain plan has changed, a tool to tell you what the explain plan use to be, and hopefully a mechanism that you can tailor to suit your needs. I will typically stagger the running of these scripts to try and capture the different SQL that is run during the day (flag1=l, flag2=n, flag3=%). I will also run a report daily which will tell me of any explain plans that have changed (flag1=n, flag2=o, flag3=%).

Please feel free to modify the above scripts for your installation. I have only trapped select statements; you may want to trap updates, deletes, and inserts. Your may also want to modify the scripts to store a history of changes instead of just the last explain plan. A few cautions if you plan to change any of the scripts. Beware of whom you run these scripts as, the user must have full access to all the objects referenced in the SQL (you may need to issue grants and create synonyms). If you change the parsing for the script sqlexp.pl, be aware that you may now be doing substitutions differently then the way you initially captured and parsed SQL before. This will snowball into loading SQL that you have loaded before. In any event, I hope I have given you a small window into looking at all the SQL in your database.

» See All Articles by Columnist James Koopmann