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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
