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