DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, March 31, 2016

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

$

1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.