Oracle-L: CLOBS AND Semicolons

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 31 May 2002 08:23:42 -0800
Message-ID: <F001.00471008.20020531082342@fatcity.com>

Most of our Oracle databases are not that busy. I decided a week ago to start capturing individual SQL statements. I run a korn shell script every minute to do so. The script invokes the following SQL

insert into oracle.statement_info       (SID, USERNAME, STATUS,
OSUSER, MACHINE, RUNTIME, ADDRESS,        HASH_VALUE, SQL_TEXT, PIECE,
RUN#, COMMAND_TYPE)          SELECT A.SID, A.USERNAME, A.STATUS,
A.OSUSER, A.MACHINE, SYSDATE,          B.ADDRESS, B.HASH_VALUE,
B.SQL_TEXT, B.PIECE, :V_RUN#, B.COMMAND_TYPE          FROM V$SESSION
A, V$SQLTEXT B          WHERE A.SQL_ADDRESS = B.ADDRESS          AND
A.SQL_HASH_VALUE = B.HASH_VALUE          and command_type <= 7

AND (A.LAST_CALL_ET < 120 or a.status = 'ACTIVE')

Once an hour I call a package which gloms the pieces of sql_text together.

create or replace package slac_stats_pkg is

procedure glom_statement;
end slac_stats_pkg;
/
create or replace package body slac_stats_pkg as

procedure glom_statement is

      psid number;
      prun# number(10,0);
      paddress raw(4);
      phash_value number;
      pstatus varchar2(8);
      posuser varchar2(30);
      pmachine varchar2(64);
      pruntime date;
      pcommand_type number;
      ppiece number;
      pusername varchar2(30);
      statement_line varchar2(64);
      statement_buffer varchar2(32760);
      statement_buffer_length number;
      psql_text clob;
      offset number;
      maxrun# number(10,0);
      cursor get_statement is 
         select piece, sql_text 
         from statement_info
         where 
             address = paddress
         and hash_value = phash_value
         and run# = prun#
         order by  run#, address, hash_value, piece;
     cursor get_statement_metadata is
          select  distinct sid, username, status, osuser, machine, runtime,
          address, hash_value, run#, command_type
          from oracle.statement_info
          where run# <= maxrun#
          order by  run#, address, hash_value;
    begin
     select max(run#) into maxrun# from statement_info;
     open get_statement_metadata;
     loop
          fetch get_statement_metadata into
          psid, pusername, pstatus, posuser, pmachine, pruntime,
          paddress, phash_value, prun#, pcommand_type;
     exit when get_statement_metadata%notfound;
          open get_statement;
          loop
             fetch get_statement into ppiece, statement_line;
          exit when get_statement%notfound;
             statement_buffer := concat(statement_buffer, statement_line);
          end loop;
          close get_statement;
          statement_buffer := concat(statement_buffer,';'); 
          statement_buffer_length := length(statement_buffer);
          offset := 1;
          insert into statement_info_temp
             values (slac_statement_seq.nextval, psid, pusername, pstatus, posuser,
                     pmachine, pruntime,paddress, phash_value, prun#, pcommand_type,
                     empty_clob())
             return sql_text into psql_text;
          dbms_lob.write(psql_text, statement_buffer_length, 
                         offset, statement_buffer);
          commit;
          statement_buffer := null;
        end loop;
        close get_statement_metadata;

   delete from statement_info where run# <= maxrun#;    commit;
   end glom_statement;
end slac_stats_pkg;
/

Note that I append a semicolon to the end of the statement. I do this because I'm going to build explain statements from them. The above procedure is called by the script below

SQL> host cat statements_to_explain.sql SET PAGESIZE 0
COLUMN STANZA FORMAT A79 WORD_WRAPPED;
SET TERMOUT OFF
SET FEEDBACK OFF
set scan off
set verify off
set arraysize 3
whenever sqlerror continue
exec slac_stats_pkg.glom_statement;
set long 16384
set arraysize 3
SPOOL explainthem.sql
Select

'alter session set current_schema = ' ||nvl(username, 'SYS') ||';' ||CHR(10) ||
'EXPLAIN PLAN' ||CHR(10) ||
'SET STATEMENT_ID = '''||to_char(statement_id)||'''' ||chr(10)

||'FOR' ||CHR(10) ||
DBMS_LOB.SUBSTR(SQL_TEXT, DBMS_LOB.GETLENGTH(SQL_TEXT), 1) STANZA FROM STATEMENT_INFO_TEMP
/
spool off
set arraysize 20
set long 80
truncate table plan_table
/
@@explainthem
alter session set current_schema = ORACLE; @@populate_slac_plan_table
@@populate_statement_info_perm
exit

The result of running the sql statment above is ...

alter session set current_schema = SYS;
EXPLAIN PLAN
SET STATEMENT_ID = '168361'
FOR
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 ;

.
.
.
  


----------------------------------------------------------------------------------------------------------------

My question is simply why does DBMS_LOB.SUBSTR(SQL_TEXT, DBMS_LOB.GETLENGTH(SQL_TEXT), 1) return the statement with the ending semicolon, but when I simply select "SQL_TEXT" from the table the terminating semicolon is not seen; i.e.,

select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0

The system is still in development. The code is currently undergoing tests. It is included here only to aid someone in answering the question. FYI, populate_slac_plan_table.sql copies data from the default plan_table to one where it will be stored along with additional information. "populate_statement_info_perm.sql" copies the data from statement_info_temp which is a global temporary table built with "on commit" preserve rows to a permanent location.

Ian MacGregor
Stanford Linear Acclerator Center
ian_at_SLAC.Stanford.edu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri May 31 2002 - 11:23:42 CDT