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



James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles