Explain Away Your Troubles

Determining when things change
in a database is the first step in zeroing in on problems. One of the prime
culprits to change is the very SQL that we attempt to run every day. Here is a
no-frills method to help sniff out those changes that occur.

Quite a few years ago when I switched from DB2 to Oracle
there was one component of DB2 I missed. With DB2 you could bind an
application to a database. Among other things, the bind process would
determine the access path for each SQL statement that was in your application.
Then, no matter when you ran the application it would always use the same
access path. This was quite a relief with DB2 because you always knew that
once you tuned an application, it would stay tuned. This is not the case with
earlier versions of Oracle, or where outlines have not been implemented or
maintained. Every time you run an application, the SQL must go through the
optimizer, where possibly a new access path will be chosen than the last time.
I would like to emphasize that this is not going to happen very much if your
data distribution remains the same. Unfortunately, we all live in hostile
environments where the data and data distribution can change quite often over
time. Not only do we have to watch out for the data changing on us but also
developers changing applications or the many adhoc queries that infiltrate your
database. I would venture to say that it is this second flavor of hostility
that can pose the greatest threat to your database.

The next best thing to having a bound access path for every
application we run, is to be able to determine when that access path has
changed for each and every SQL statement that gets executed. To do this I have
come up with a set of scripts that will allow you to capture, store, and report
on changes in explain plans for all of your SQL statements.

The first thing to do is create a few structures to store
the SQL and explain plans within your Oracle database. Figure 1 shows these
structures. The sequence number is just an indexing mechanism. The table sqlexp_sqltext
will store the sequence number assigned to the SQL, the date it was added /
updated within the table, and the actual SQL statement. The next table sqlexp_plan_table
is an exact replica of the plan_table that would be created if you were to run
the utlxplan.sql script. Check Oracle’s plan_table against this one and feel
free to modify any of the scripts here to be compliant. This table will store
the explain plans for all SQL captured. The statement_id column will be the
sequence number assigned to the SQL statement in question.

Figure 1.

–# sqlexp_create.sql
–#
–# create objects needed to monitor sql
–#
CREATE SEQUENCE sqlexp_seq
      INCREMENT BY 1 START WITH 1;
CREATE TABLE sqlexp_sqltext (
             TEXT_SEQ        NUMERIC,
             TEXT_DATE       DATE,
             SQL_TEXT        VARCHAR2(1000))
             TABLESPACE TOOLS;
CREATE TABLE sqlexp_plan_table (
             statement_id    VARCHAR2(30),
             timestamp       DATE,
             remarks         VARCHAR2(80),
             operation       VARCHAR2(30),
             options         VARCHAR2(30),
             object_node     VARCHAR2(128),
             object_owner    VARCHAR2(30),
             object_name     VARCHAR2(30),
             object_instance NUMERIC,
             object_type     VARCHAR2(30),
             optimizer       VARCHAR2(255),
             search_columns  NUMERIC,
             id              NUMERIC,
             parent_id       NUMERIC,
             position        NUMERIC,
             other           LONG)
             TABLESPACE TOOLS;

The next task is to extract all of the SQL from your
system. To do this I have two scripts that I use. The first script sqlexp_pull.sql
(Figure 2.), is a PL/SQL script that does nothing more then query v$sqlarea and
write out each and every select statement to a flat file defined by ‘utl_path’
and with a file name of ‘sqlexp_put.lst’. You will need to change the ‘utl_path’
variable to a valid directory on your system that has been set up for the UTL_FILE
package.

–#————————————————————————–#
–# sqlexp_pull.sql
–#
set serverout on
set echo off
set verify off
set linesize 132
set pagesize 40
set long 9999
DECLARE 
CURSOR c0 IS
          select sql_text
            from v$sqlarea
           where upper(sql_text) like ‘SELECT%’
           order by 1;
utl_file_handle         UTL_FILE.FILE_TYPE;
utl_path                VARCHAR2(255) := ‘<valid utl_path>’;
utl_file_name           VARCHAR2(255) := ‘sqlexp_pull.lst’;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  BEGIN
    utl_file_handle := UTL_FILE.FOPEN(utl_path, utl_file_name, ‘w’);
  EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
      dbms_output.put_line
    (‘File location or filename was invalid. ‘||substr(sqlerrm,1,30));
      dbms_output.put_line(‘    SQLCODE:  ‘||sqlcode);
      RETURN;
    WHEN UTL_FILE.INVALID_MODE THEN
      dbms_output.put_line
    (‘The open_mode parameter in FOPEN was invalid. ‘||substr(sqlerrm,1,30));
      dbms_output.put_line(‘    SQLCODE:  ‘||sqlcode);
      RETURN;
    WHEN UTL_FILE.INVALID_OPERATION THEN
      dbms_output.put_line
    (‘The file could not be opened or operated on as requested. ‘||substr(sqlerrm,1,30));
      dbms_output.put_line(‘    SQLCODE:  ‘||sqlcode);
      RETURN;
  END;
    FOR r0 IN c0 LOOP
      UTL_FILE.PUT_LINE(utl_file_handle,r0.sql_text);
      UTL_FILE.FFLUSH(utl_file_handle);
    END LOOP;
    UTL_FILE.FCLOSE(utl_file_handle);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(‘No data found in sqlarea’);
END;
/