Explain Away Your Troubles

March 6, 2003

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; 
/