usrmon.sql

一个Oracle性能监视工具

set feedback off
set verify off
set serveroutput on
declare

t_number1 number :=0;
t_lines   number :=&1;
t_sid     number :=&2;
t_string  varchar2(255) := upper('&3');

cursor c_usrstat is
 select sta.name
       ,ses.value
   from v$sesstat ses
       ,v$statname sta
  where sta.statistic# = ses.statistic#
    and ses.sid = t_sid
    and upper(sta.name) like '%'||t_string||'%'
    and value != 0
    and rownum <= t_lines
  order by 2 desc;

begin
 dbms_output.enable(100000);

 -- detailed sessions
 dbms_output.put_line('STATISTIC                                            VALUE');
 for r_usrstat in c_usrstat 
 loop
   dbms_output.put_line(rpad(ltrim(r_usrstat.name),50)                           ||
                        lpad(ltrim(to_char(r_usrstat.value,'9999990')),8) );
 end loop;
end;
/