在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
