Oracle 817中抓捕執行很慢的SQL

在Oracle 9i,10g中,我們可以通過  v$sql中的 SQL_FULLTEXT得到完整的SQL,
v$session_longops中可以查到執行超過6秒的sql address,
用下面的sql可以得到執行超過6秒的SQL.
select b.sql_fulltext,a.* from  v$session_longops a, v$sql b
where  a.sql_address =b.address and a.sql_hash_value=b.hash_value
and  username  not in ('SYS','SYSTEM')
order by a.totalwork desc;

以上的方法在8i中有些問題.
v$sql字典中沒有sql_fulltxt數據欄位, v$sql_text又只有1000位,數據不完整.
必須查v$sqltext才能得到完整sql.

create or replace function getfullsql_clob(vaddress raw, vhash_value number) return clob is
clob_loc CLOB;
sql_line varchar2(64);
dest_offset NUMBER :=1;
src_offset NUMBER:=1;
amount INTEGER := dbms_lob.lobmaxsize;
warning INTEGER;
cursor sql_cur is
  select sql_text from v$sqltext
    where ADDRESS = vaddress
    and HASH_VALUE= vhash_value
    order by PIECE ;

BEGIN

    DBMS_LOB.CREATETEMPORARY(lob_loc=>clob_loc, cache=>false , dur=>dbms_lob.SESSION);
    open sql_cur ;
    FETCH sql_cur INTO sql_line ;
    WHILE sql_cur%FOUND LOOP
       DBMS_LOB.WRITEAPPEND(clob_loc, length(sql_line), sql_line ) ;
       FETCH sql_cur INTO sql_line ;
    end loop ;

-- DBMS_LOB.FREETEMPORARY(clob_loc) ;

    return clob_loc ;
END getfullsql_clob;
/


然後利用下面的sql得到執行慢的sql

select sys.getfullsql_clob( SQL_ADDRESS ,SQL_HASH_VALUE) sql_fulltext,a.*
from V$session_longops a, V$sql b
where a.sql_address =b.address and a.sql_hash_value=b.hash_value
and username <>'SYS'
order by totalwork ;


阿飛

2009/05/18