hdrmon.sql

一个Oracle性能监视工具

set feedback off
set verify off
set serveroutput on
declare

t_number1 number :=0;
t_number2 number :=0;
t_number3 number :=0;
t_number4 number :=0;
t_number5 number :=0;
t_number6 number :=0;
t_number7 number :=0;
t_number8 number :=0;
t_number9 number :=0;

t_varchar1 varchar2(200);
t_varchar2 varchar2(200);

cursor c_sga is
select   replace(replace(name,'Size'),'Database','DB') name
        ,round(value/1024/1024,2) value
from     sys.v_$sga;

begin
 dbms_output.enable(100000);

 -- total sessions
 select name     into t_varchar1 from v$database;
 select count(*) into t_number2 from v$session;
 select count(*) into t_number3 from v$session where type = 'BACKGROUND';
 select count(*) into t_number4 from v$session where type = 'USER';
 select count(*) into t_number5 from v$session where type = 'USER' and status = 'ACTIVE';
 select count(*) into t_number6 from v$session where type = 'USER' and status = 'INACTIVE';
 dbms_output.put_line(lower(user)||'@'||t_varchar1|| '=>'||
                      t_number2||' processes: '||
                      t_number3||' background,'||
                      t_number4||' user('||
                      t_number5||' active,'||
                      t_number6||' inactive)');

 -- SGA details
 select round(sum(value)/1024/1024,2) into t_number1 from v$sga;
 t_varchar1 := 'SGA:'||t_number1||'Mb (';
 for r_sga in c_sga
 loop
   t_varchar1 := t_varchar1||','||rtrim(lower(replace(r_sga.name,' ')))||':'||
                             ltrim(to_char(r_sga.value,'9990.00'));
 end loop;
 t_varchar1 := replace(t_varchar1,'(,','(')||')';
 dbms_output.put_line(t_varchar1);

 -- Disk space
 select round(sum(bytes/1024/1024),2) into t_number1 from dba_data_files;
 select round(sum(bytes/1024/1024),2) into t_number2 from dba_free_space;
 t_number3:=t_number1 - t_number2;
 t_number4:=round(t_number3/t_number1 ,4) * 100;
 dbms_output.put_line('Disk Total:'      ||t_number1||'Mb'||
                      ' (free:'        ||t_number2||'Mb'||
                      ' used:'         ||t_number3||'Mb'||
                      ' percent used:' ||t_number4||'%)');

 -- Hitratios
 select round((sum(decode(name, 'consistent gets',value, 0)) + 
            sum(decode(name, 'db block gets',value, 0)) - 
            sum(decode(name, 'physical reads',value, 0))) / 
         (sum(decode(name, 'consistent gets',value, 0)) + 
         sum(decode(name, 'db block gets',value, 0))) * 100,2) getal
   into t_number1
   from v$sysstat;

 select round((sum(PINHITS) / sum(PINS)) * 100,2)
   into t_number2
   from v$librarycache;

 select round((sum(PINS) / (sum(PINS) + sum(RELOADS))) * 100,2)      
   into t_number3
   from v$librarycache;

 select round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
   into t_number4
   from v$rowcache;
 dbms_output.put_line('Hits Buffer:'           ||t_number1||'%'||
                      ' Library:'         ||t_number2||'%'||
                      ' Library misses:'  ||t_number3||'%'||
                      ' Data dictionary:' ||t_number4||'%');

 dbms_output.put_line(chr(27)); 
end;
/