很久没有更新博客了,主要是最近被博客上的垃圾评论整得心烦,眼不见心不烦,今天对showsql.sql脚本做了点更新,相信大家用起来更顺手,所以就发出来给大家做参考,本次改进减少了一个sql的调用,输出的结果就更准确了...

$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF
connect / as sysdba;

set serveroutput on size 1000000
set lines 200
set pages 1000
set feedback off
column username format a20
column sql_text format a98

declare
type tab_varchar2 is table of varchar2(128);
v_list tab_varchar2 := tab_varchar2();

procedure p (p_str in varchar2)
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line(substr(l_str, 1, 250));
l_str := substr(l_str, 251);
end loop;
end;
begin
for x in (select a.username||'('||a.sid||','||a.serial#||') ospid='||b.spid||
' hash_value='||to_char(a.sql_hash_value)||' execs='||to_char(s.executions)||
' els_time='||to_char(trunc(elapsed_time/1000000/decode(executions,0,null,executions),2)) username,
' program='||a.program program,a.sid,a.serial#,
' disk_reads='||to_char(trunc(disk_reads/decode(executions,0,null,executions),2)) disk_reads,
' buffer_gets='||to_char(trunc(buffer_gets/decode(executions,0,null,executions),2)) buffer_gets,sql_address,sql_hash_value
from v$session a,v$process b,v$sqlarea s
where a.status = 'ACTIVE' and s.hash_value=a.sql_hash_value
and a.paddr = b.addr and rawtohex(sql_address) <> '00' and a.username is not null
and sid <> (select sid from v$mystat where rownum = 1) order by last_call_et)
loop
dbms_output.put_line( '--------------------------------------------------------------------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.program || ' ' ||x.disk_reads || ' '|| x.buffer_gets);
v_list.extend;
v_list(v_list.count) := 'alter system kill session '''||to_char(x.sid)||','||to_char(x.serial#)||''';';
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p(y.sql_text);
end loop;

--output sql execution plan
dbms_output.put_line( '--------------------------------------------------------------------------------' );
for i in (select rpad('|'||substr(lpad(' ',1 * (depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,
object_name)||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',decode(sign(cardinality-1000),
-1, cardinality||' ',decode(sign(cardinality-1000000), -1,
trunc(cardinality/1000)||'K',decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',decode(sign(bytes-1073741824),
-1, trunc(bytes/1048576)||'M',trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as Explain_plan
from v$sql_plan
where hash_value = x.sql_hash_value
and child_number = (select max(child_number) from v$sql_plan where hash_value = x.sql_hash_value))
loop
dbms_output.put_line(i.explain_plan);
end loop;
end loop;

--output kill session script
dbms_output.put_line( '----------------------------alter system kill session---------------------------' );
dbms_output.put_line( '--------------------------------------------------------------------------------' );
for i in 1..v_list.count loop
dbms_output.put_line(v_list(i));
end loop;
end;
/
exit
EOF