col AVG_ACTIVE_USR format 999999.99
col AVG_TOT_USR format 9999.99
col AVG_USR_CPU format 9999.99
col AVG_SYS_CPU format 99.99
col AVG_IDLE_CPU format 99.99
col AVG_WAIT_CPU format 99.99
col TOT_ODR format 99999999
col AVG_ACT_USR format 999999.99
col RUNQ format 999.99
col CSW format 99999.99
set lines 120 pages 2000
accept start1 prompt ‘Enter Start Dare in DD-MON-RRRR:HH24:MI format : ‘
accept stop1 prompt ‘Enter Stop Dare in DD-MON-RRRR:HH24:MI format : ‘
set echo off
set feedback off
set verify off
select to_char(measured_date,’DD-MON-RRRR:HH24′) “Hour”, min(a.total_users) “Min Tot Usr”,
max(a.total_users) “Max Tot Usr”, avg(a.total_users) AVG_TOT_USR,
min(b.user_cpu+system_CPU) “MIN USG CPU”, max((b.user_cpu+system_CPU)) “Max USG CPU”,
avg((b.user_cpu+system_CPU)) “Avg Usg CPU”
from stats$totalusers a,stats$vmstat2 b
where a.measured_date=b.start_date
and a.measured_date between to_date(‘&start1’, ‘DD-MON-RRRR:HH24:MI’) and to_date(‘&stop1’, ‘DD-MON-RRRR:HH24:MI’)
group by to_char(measured_date,’DD-MON-RRRR:HH24′)
/
select to_char(b1.start_date, ‘DD-MON-RRRR:HH24’) peak_hr, (b1.user_cpu+b1.system_CPU) peak_cpu
from stats$vmstat2 b1
where (b1.user_cpu+b1.system_CPU) = (select max((b.user_cpu+system_CPU)) max_cpu
from stats$vmstat2 b
where b.start_date between to_date(‘&start1’, ‘DD-MON-RRRR:HH24:MI’)
and to_date(‘&stop1’, ‘DD-MON-RRRR:HH24:MI’))
and b1.start_date between to_date(‘&start1’, ‘DD-MON-RRRR:HH24:MI’)
and to_date(‘&stop1’, ‘DD-MON-RRRR:HH24:MI’) ;
select avg(XXX.avg_cpu) “Average-CPU”
from
(select to_char(measured_date,’DD-MON-RRRR:HH24′) “Hour”, avg((b.user_cpu+system_CPU)) avg_cpu
from stats$totalusers a,stats$vmstat2 b
where a.measured_date=b.start_date
and a.measured_date between to_date(‘&start1’, ‘DD-MON-RRRR:HH24:MI’) and to_date(‘&stop1’, ‘DD-MON-RRRR:HH24:MI’)
group by to_char(measured_date,’DD-MON-RRRR:HH24′)) XXX
/