This tool loops a specified number of times, displaying memory
usage along with user process counts for a specific username.
--=================================================
--
-- proc_ora_monitor
--
-- parm1: username to count
-- parm2: number of loops, 5 sec duration
--
--
--=================================================
set serveroutput on ;
create or replace procedure
proc_ora_monitor ( user1 in varchar, reps1 in integer )
is
i number ;
usercount1 number ;
memory1 number ;
date1 varchar(20) ;
msg varchar(99) ;
begin
i := 0 ;
while ( i < reps1 )
loop
msg := '=> ' || to_char(SYSDATE, 'HH:MM:SS PM');
select count(1)
into usercount1
from sys.v_$session
where username = user1 ;
msg := msg || ', ' || user1 || ': ' || usercount1 ;
select round(sum(bytes)/1024/1024 ,2)
into memory1
from sys.v_$sgastat
where pool = 'shared pool' and
name = 'free memory' ;
msg := msg || ', free mb = ' || memory1 ;
select round(sum(bytes)/1024/1024 ,2)
into memory1
from sys.v_$sgastat
where pool = 'shared pool' and
name = 'processes' ;
msg := msg || ', processes mb = ' || memory1 ;
dbms_output.put_line(msg) ;
dbms_lock.sleep(5) ;
i := i + 1 ;
end loop ;
end;
/
show errors ;
execute proc_ora_monitor('SILVERUSER',2) ;
exit
|
|
