This is an old revision of the document!

Quick and Dirty Monitoring

I was getting CPU alerts in the middle of the night from AWS. Cloudwatch wasn't showing anything with their graphs and SAR showed 95% idle.

Save the following into a file and run in the background

nohup ./foo.sh &

What are we monitoring ?

CPU/Load Average/Top 5 processes via TOP and PS

<code> !/bin/bash

typeset -i looper=1 spoolFile=“/tmp/tmpSnooper.log”

ORACLE_BASE=“/opt/oracle” ORACLE_HOME=“${ORACLE_BASE}/product/12.1.0/client_1” TNS_ADMIN=$ORACLE_HOME/network/admin PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin export PATH ORACLE_BASE ORACLE_HOME TNS_ADMIN

PLUS=“sqlplus -s xxxxx/xxxxx@xxxx”

while ¹⁾ do

 echo "-------------------------------------------------" >> ${spoolFile}
 date >> ${spoolFile}
 echo "-------------------------------------------------" >> ${spoolFile}

 top -b | head -n 12 | tail -n 6 >> ${spoolFile}
 uptime >> ${spoolFile}
 ps -Ao user,uid,comm,pid,pcpu,tty --sort=-pcpu | head -n 6 >> ${spoolFile}

 ${PLUS} << EOF >> ${spoolFile}
 set pagesize 100
 set linesize 300
 set echo off
 set feedback off
 column sql_text format a120
 column username format a15
 column osuser format a10
 column os_pid format a10
 column machine format a10

SELECT

  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.osuser,
  s.machine,
  s.program,
  p.spid              AS os_pid,
  s.sql_id,
  sa.executions,
  sa.disk_reads,
  sa.buffer_gets,
  sa.rows_processed,
  ROUND(sa.elapsed_time/1e6,2) AS elapsed_sec,
  ROUND(sa.cpu_time/1e6,2)     AS cpu_sec,
  sa.sql_text

FROM v\$session s JOIN v\$process p ON s.paddr = p.addr LEFT JOIN v\$sqlarea sa

   ON s.sql_id = sa.sql_id

WHERE s.status = 'ACTIVE'

AND s.username IS NOT NULL
AND s.type = 'USER'

ORDER BY sa.elapsed_time DESC NULLS LAST; exit

EOF

sleep 60

done

¹⁾

$looper