Re: limit temp usage before error or out of space condition?
Date: Tue, 22 Oct 2013 23:35:58 -0700 (PDT)
Message-ID: <1382510158.82191.YahooMailNeo_at_web120603.mail.ne1.yahoo.com>
you can catch the culprit with keeping a thershold like below..and put the below procedure in script which shoots you email when temp is used heavily by any user/process.... CREATE OR REPLACE procedure monitor_temp_usage is
temp_total number; temp_used number; temp_pct float;
timestamp varchar2(30);
cutoff_threshold number :_at_;
cursor cur_temp_tbs is
select tablespace_name tbs
from dba_tablespaces
where CONTENTS = 'TEMPORARY';
cursor sort_usage(p_temp_tbs in varchar2) is
select s.action
,s.machine
,s.process
,s.module
,s.username "USER"
,s.sid
,s.serial#
,s.osuser
,s.program
,p.spid
,last_call_et
,to_char(logon_time,'MON-DD-YYYY:HH24.MI.SS') logon_time
,status
,u.tablespace "TS"
,sum(u.blocks) * 8192 bytes
,p.program sprogram
,u.tablespace temp_ts
from sys.v_$session s
,sys.v_$sort_usage u
,sys.v_$process p
where s.saddr = u.session_addr
and s.paddr = p.addr
and u.tablespace = p_temp_tbs
group by s.action,s.machine,p.program,s.process,s.sid, s.serial#,s.username,
osuser, module,s.program,p.spid,tablespace,last_call_et,logon_time,status;
begin
select to_char(sysdate,'MON/DD/YYYY:HH24MI') into timestamp from dual;
for x in cur_temp_tbs
loop
select sum(bytes) into temp_total from dba_temp_files where tablespace_name = x.tbs;
for i in sort_usage(x.tbs)
loop
temp_pct:=(i.bytes*100)/temp_total;
if temp_pct > cutoff_threshold
then
dbms_output.put_line('=================================================================');
dbms_output.put_line(rpad('SID,SERIAL#',20)||rpad(':',5)||i.sid||','||i.serial#);
dbms_output.put_line(rpad('DB USER',20)||rpad(':',5)||i.user);
dbms_output.put_line(rpad('OS USER',20)||rpad(':',5)||i.osuser);
dbms_output.put_line(rpad('SERVER PROCESS ID',20)||rpad(':',5)||i.spid);
dbms_output.put_line(rpad('SERVER PROGRAM ',20)||rpad(':',5)||i.sprogram);
dbms_output.put_line(rpad('CLIENT PROCESS ID',20)||rpad(':',5)||i.process);
dbms_output.put_line(rpad('CLIENT PROGRAM ',20)||rpad(':',5)||i.program);
dbms_output.put_line(rpad('CLIENT MACHINE ',20)||rpad(':',5)||i.machine);
dbms_output.put_line(rpad('MODULE ',20)||rpad(':',5)||i.module);
dbms_output.put_line(rpad('ACTION ',20)||rpad(':',5)||i.action);
dbms_output.put_line(rpad('LOGON TIME ',20)||rpad(':',5)||i.logon_time);
dbms_output.put_line(rpad('LAST CALL ET',20)||rpad(':',5)||i.last_call_et);
dbms_output.put_line(rpad('SESSION STATUS',20)||rpad(':',5)||i.status);
dbms_output.put_line(rpad('TEMP USED in MB',20)||rpad(':',5)||i.bytes/1048576);
dbms_output.put_line(rpad('TEMP USED PCT',20)||rpad(':',5)||round(temp_pct,2));
dbms_output.put_line(rpad('TEMP TABLESPACE NAME',20)||rpad(':',5)||i.temp_ts);
end if;
end loop;
end loop;
end;
/
It helped me in many times and Hope this helps you
thanks,
Edwin.K
On Wednesday, October 23, 2013 2:52 AM, Kellyn Pot'vin <kellyn.potvin_at_ymail.com> wrote:
You can easily set this up as a metric extension, but to be honest, I used to do it as shell script. Here is the script and you can steal what you need from it:
function mail_page {
echo|mailx -s "${HOST} resumable transactions found in ${ORACLE_SID} at ${DATE}" ${MAIL_LIST}
\rm -f ${OUT_FILE}
}
export ORAENV_ASK=NO
. oraenv > /dev/null
export ORAENV_ASK=YES
DATE=`date '+%m/%d/%y %H:%M:%S'`
MAIL_LIST="<email_add>"
HOST=`hostname`
WORKING_DIR=~/scripts
OUT_FILE=${WORKING_DIR}/`basename $0`_${ORACLE_SID}.lst
let ERR_CNT=0
ERR_CNT=$(sqlplus -s << EOF
/ as sysdba
set feedback off
set pagesize 0
set timing off
select count(*) from dba_resumable where suspend_time is not null and name not like 'User SYSTEM(5)%';
EOF)
echo "error count is: " ${ERR_CNT}
if [ ${ERR_CNT} -ne 0 ]
then
mail_page
exit 2
else
echo "everything OK"
fi
exit 0
Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days Conference
~Tombez sept fois, se relever huit!
On Tuesday, October 22, 2013 2:56 PM, Josh Collier <Josh.Collier_at_banfield.net> wrote:
Yes, I use resumable already. Just wanted to see if I could catch them before they suspend. I have lowered the warning threshold on the full condition for the tablespace. It doesn't look like OEM has anything to monitor temp usage by query tho. I could write something in perl but I was hoping to avoid having to build a wheel.
From: Kellyn Pot'vin [mailto:kellyn.potvin_at_ymail.com]
Sent: Tuesday, October 22, 2013 1:47 PM
To: oracle-l_at_freelists.org; Josh Collier
Subject: Re: limit temp usage before error or out of space condition?
Look into setting up resumable in your database. .. your life will be so much better for it. ...:)
Sent from Yahoo Mail on Android<http://overview.mail.yahoo.com/mobile/?.src=Android>
From: Josh Collier <Josh.Collier_at_banfield.net<mailto:Josh.Collier_at_banfield.net>>; To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>;
Subject: limit temp usage before error or out of space condition? Sent: Tue, Oct 22, 2013 8:38:02 PM
Does anyone have any ideas how to limit temp usage before the query at hand fills up the temp tablespace and then suspends everyone else until the condition is cleared? Is there a way to monitor queries that are using too much temp and send an email? Maybe in OEM? Thanks for your thoughts,
Josh C.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 23 2013 - 08:35:58 CEST