Shared publicly  - 
 
+Kellyn Pot'vin shares her awesome SH/SQL*Plus script for monitoring Oracle TEMP and UNDO usage
1
Martin Berger's profile photoJeff Smith's profile photoLeighton Nelson's profile photoKellyn Pot'vin's profile photo
8 comments
 
take care, it's not complete! (seems some > and < where interpreted as HTML-Tags)
 
Can you check it again? I edited her post with <pre></pre> tags to remove any HTML formatting, but I don't know if it got screwed up as she posted it... +Kellyn Pot'vin
 
Nope, it's still off... It appears to have pasted incorrectly. Here's what it should read:

#!/usr/bin/ksh
#############################################################################
# chk_tmp_undo.ksh
# Kellyn Pot'Vin
# Usage: ./chk_tmp_undo.ksh <ORACLE_SID> <tmp % Threshold> <Undo % Threshold>
# Good Base for Thresholds is 50% of Temp/Undo tablespace size. That will give you the warning when something large is running
# Threshold is in MB- not GB!!
#############################################################################

#----------------------------------------------------------------------------
# Verify that the ORACLE_SID has been specified on the UNIX command-line...
#----------------------------------------------------------------------------
if (( $# != 3 ))
then
echo "usage: $0 SID tmp_pct undo_pct"
exit 1
fi
#
#----------------------------------------------------------------------------
# Set up Oracle environment...
#----------------------------------------------------------------------------

export ORACLE_SID=$1
export TMP_THRSHLD=$2
export UNDO_THRSHLD=$3
echo "Oracle SID: "${ORACLE_SID}
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=`grep -i ^"$ORACLE_SID" /etc/oratab | awk -F: '{print $2}'`
export PATH=$PATH:${ORACLE_HOME}/bin


export EXEC_DIR=/common/admin
export SQL_DIR=/common/sql
export LOG_DIR=/common/logs
export ML_LIST="<EMAIL_ADDRESSES>"

export UNDO_FL=${LOG_DIR}/${ORACLE_SID}_undo.log
export TMP_FL=${LOG_DIR}/${ORACLE_SID}_tmp.log
export OU_FL=${LOG_DIR}/${ORACLE_SID}_u.out
export OT_FL=${LOG_DIR}/${ORACLE_SID}_t.out

echo "UNDO_Threshold: $UNDO_THRSHLD"
sqlplus -s << EOF | read GET_UNDO
/ as sysdba
set feedback off
set head off
set pagesize 0
select sum(round((vu.undoblks*32768)/(1024*1024))) "MB of UNDO"
from v\$undostat vu
where ((vu.undoblks*32768)/(1024*1024)) > 0;
EOF
export $GET_UNDO

sqlplus -s << EOF > $OU_FL
/ as sysdba
set head on
set feedback on
set serveroutput on
select distinct vt.sql_text, vs.sid,vs. osuser, vs.sql_id, sum(round((vu.undoblks*32768)/(1024*1024)))MB
from v\$sqltext vt, v\$undostat vu, v\$session vs
where vu.maxqueryid in vs.sql_id
and vs.sql_id=vt.sql_id
and vt.piece=0
and ((vu.undoblks*32768)/(1024*1024)) > 0
group by vt.sql_text,vs.sid,vs. osuser,vs.sql_id, vu.undoblks
order by MB desc;

select sum(round((vu.undoblks*32768)/(1024*1024))) "MB of UNDO"
from v\$undostat vu
where ((vu.undoblks*32768)/(1024*1024)) > 0;

EOF

if [[ $GET_UNDO -gt $UNDO_THRSHLD ]]
then
echo|mail -s "${ORACLE_SID}: $UNDO_THRSHLD MB Threshold of Undo Usage Surpassed" $ML_LIST < $OU_FL
fi

echo "TEMP_Threshold: $TMP_THRSHLD"
sqlplus -s << EOF | read GET_TMP
/ as sysdba
set head off
set pagesize 0
select sum(trunc(swa.tempseg_size/1024/1024))"TEMP TOTAL MB"
from v\$sql_workarea_active swa;
EOF
export $GET_TMP

sqlplus -s << EOF > $OT_FL
/ as sysdba
set head on
set serveroutput on
col sid format 9999990 heading 'SID'
col osuser format 999,999,999,999 heading 'OSUSER'
col tmpseg_size format 999999990 heading 'TEMP TOTAL MB'
col sql_id format 999,999,999,999,999,999 heading 'Active SQL_ID'
select swa.sid, vs.process, vs.osuser, vs.machine,vst.sql_text, vs.sql_id "Session SQL_ID",
swa.sql_id "Active SQL_ID", trunc(swa.tempseg_size/1024/1024)"TEMP TOTAL MB"
from v\$sql_workarea_active swa, v\$session vs, v\$sqltext vst
where swa.sid=vs.sid
and vs.sql_id=vst.sql_id
and piece=0
and swa.tempseg_size is not null
order by "TEMP TOTAL MB" desc;

select sum(trunc(swa.tempseg_size/1024/1024))"TEMP TOTAL MB"
from v\$sql_workarea_active swa;
EOF

if [[ $GET_TMP -gt $TMP_THRSHLD ]]
then
echo|mail -s "${ORACLE_SID}: $TMP_THRSHLD MB Threshold of TEMP Usage Surpassed" $ML_LIST < $OT_FL
fi

rm -f $OU_FL
rm -f $OT_FL
exit 0
 
Good script.couldn't you do the same thing using enterprise manager?
 
Yes, you can... Depending on the size of DB's monitoring, demands on OEM, a shell script is a good way of avoiding some of the waits for this monitoring and have it performed outside of OEM. I am a strong believer in a redundant monitoring system. Separate monitoring in a way that is best for the environment and have a separate script server monitoring OEM to ensure OMS uptime and agent uploading.
 
I'm with you on this. We use EM as our primary DB monitoring solution but scripts like this complement it for critical metrics or one off tasks. I must admit that I'm somewhat script averse while not being completely ignorant. Efficiency and effectiveness are the goals. How you achieve it is secondary. 
 
+Kellyn Pot'vin Do you only have 32k blocksize on all DBs, and therefore hardcode it here, or is it too expensive to join this value also from data dictionary?
 
All 32k...big block size for these ones, so its just hardcoded...
Add a comment...