Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 6, 2003

Explain Away Your Troubles - Page 2

By James Koopmann

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>) {
  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";

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.




Action of script sqlexp_put.sql




Load all new SQL found in the file sqlexp_put.lst.

This will also report on changes in explain plans found for all SQL processed.

Any SQL that are un-explainable will end up in file <utl_path>/sqlexp_err.lst




Process all SQL in table sqlexp_sqltext and re-run explain plans. Compare the new explain plan with the old explain plan stored in sqlexp_plan_table and report on the differences.




Show the SQL and explain plan for statement_id = <id>.

NOTE: this is NOT the explain plan stored in sqlexp_plan_table, it is a totally new explain plan generated with current statistics.




Show the SQL and explain plan for statement_id = <id>.

NOTE: this IS the explain plan stored in sqlexp_plan_table.




Process SQL where statement_id = <id> and report on differences if found.




Update sqlexp_plan_table with the NEW explain plan for statement_id = <id>.

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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM