sesmon.sql

一个Oracle性能监视工具

set feedback off
set verify off
set serveroutput on
declare

t_number1 number :=0;
t_lines   number :=&1;

cursor c_session is
select ses.sid
      ,decode(ses.username,null,'internal',ses.username) username
      ,decode(ses.osuser,null,'internal',ses.osuser) osuser
      ,substr(ses.program,1,26) program
      ,sio.physical_reads IO
      ,sta.value/100 CPU
      ,ses.status
from  v$session ses
     ,v$sess_io sio
     ,v$sesstat sta
     ,v$statname stn
where ses.SID = sio.SID
  and sta.STATISTIC# = stn.STATISTIC#
  and sta.SID = ses.SID
  and stn.name like '%CPU used by this session%'
order by 7,&2 desc, 1;

begin
 dbms_output.enable(100000);

 -- detailed sessions
 dbms_output.put_line('SID USERNAME  OSUSER      PROGRAM                     DISK I/O     CPU    STATUS');
 t_number1 := 0;
 for r_session in c_session 
 loop
   dbms_output.put_line(rpad(ltrim(r_session.sid),4)                             ||
                        rpad(ltrim(r_session.username),10)                       ||
                        rpad(ltrim(r_session.osuser),12)                         ||
                        rpad(ltrim(r_session.program),26)                        ||
                        lpad(ltrim(r_session.IO),10)                             ||
                        lpad(ltrim(to_char(r_session.CPU,'9990.00')),8)          ||
                        lpad(ltrim(r_session.status),10));
   t_number1 := t_number1 + 1;
   exit when t_number1 = t_lines;
 end loop;
end;
/