Script to chek Number of connections and CPU load at Peak time in database


$ cat metrix_connections.sh

/home/oracle/gangai/metrix_op.log
a=`cat $1`; export a

for i in $a

do

sqlplus -S username/passwd@$i << EOF
spool metrix_op.log append

@/home/oracle/gangai/Metrix/metrix_connections.sql

exit;
EOF

done

$ cat metrix_connections.sql

set echo off;
set heading off;
set feedback off;
set verify off;
set verify off;
set trimspool off;
set lines 150;
set colsep ”;
set space 0;
set pagesize 0;
select wm_concat(distinct(to_char(pvm.measured_date, ‘DD-MON-RRRR:HH24’))) “Peak Hr”,round(pvm.total_users) “Total Users”,
round((select avg(a.total_users)
from stats$totalusers a
where a.measured_date between to_date(’11-dec-2015:10:00′, ‘DD-MON-RRRR:HH24:MI’) and to_date(’11-dec-2015:13:00′, ‘DD-MON-RRRR:HH24:MI’))) “Avg Users”
from stats$totalusers pvm
where pvm.total_users =
(select max((pvm1.total_users)) highest_total_users
from stats$totalusers pvm1
where pvm1.measured_date between to_date(’11-dec-2015:10:00′, ‘DD-MON-RRRR:HH24:MI’)
and to_date(’11-dec-2015:13:00′, ‘DD-MON-RRRR:HH24:MI’))
and pvm.measured_date between to_date(’11-dec-2015:10:00′, ‘DD-MON-RRRR:HH24:MI’)
and to_date(’11-dec-2015:13:00′, ‘DD-MON-RRRR:HH24:MI’)
group by (pvm.total_users);

$ cat metrix_cpu.sh
a=`cat $1`; export a

for i in $a

do

sqlplus -S username/passwd@$i << EOF
spool metrix_op.log append

@/home/oracle/gangai/Metrix/metrix_cpu.sql

exit;
EOF

done

$cat metrix_cpu.sql

set echo off;
set heading off;
set feedback off;
set verify off;
set verify off;
set trimspool off;
set lines 150;
set colsep ”;
set space 0;
set pagesize 0;
select wm_concat(distinct(to_char(b1.start_date, ‘DD-MON-RRRR:HH24’))) “Peak_Hr”, round((b1.user_cpu+b1.system_CPU+b1.wait_cpu)) “Max_CPU”,
round((select avg(b2.user_cpu+b2.system_CPU+wait_cpu)  from stats$vmstat2 b2
where
b2.start_date between to_date(’18-dec-2015:01:00′, ‘DD-MON-RRRR:HH24:MI’) and to_date(’18-dec-2015:13:00′, ‘DD-MON-RRRR:HH24:MI’))) “Avg_CPU”
from stats$vmstat2 b1
where (b1.user_cpu+b1.system_CPU+b1.wait_cpu) =
(select max((b3.user_cpu+system_CPU+wait_cpu)) max_cpu
from stats$vmstat2 b3
where b3.start_date between to_date(’18-dec-2015:01:00′, ‘DD-MON-RRRR:HH24:MI’)
and to_date(’18-dec-2015:13:00′, ‘DD-MON-RRRR:HH24:MI’))
and b1.start_date between to_date(’18-dec-2015:01:00′, ‘DD-MON-RRRR:HH24:MI’) and to_date(’18-dec-2015:13:00′, ‘DD-MON-RRRR:HH24:MI’)
group by (b1.user_cpu+b1.system_CPU+b1.wait_cpu);

$cat list1.log

db1
db2
db3
db4

Execution steps:

Modify the date as per your requirement.

$metrix_connections.sh list1.log

It will dispaly message

$metrix_cpu.sh list1.log

It will dispaly message

$

  • March 30, 2016 | 15 views