Re: How to clear UNDOTBS- Slightly OT

Very cooll, Rich!  I have a shell script that monitors temp and undo usage in the main systems and if it hits a certain threshold I've set for that database, it sends an email alert to the appropriate folks.  The developers on that list do NOT like to see their names on the email alert, as it means they may be impacting production and it means our boss may be coming over to their desk in a short period of time to visit... :)

 

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com
 

"Go away before I replace you with a very small and efficient shell script..."

• On Mon, 1/18/10, Rich Jesse <rjoralist_at_society.servebeer.com> wrote:

From: Rich Jesse <rjoralist_at_society.servebeer.com> Subject: Re: How to clear UNDOTBS- Slightly OT To: "ORACLE-L" <oracle-l_at_freelists.org> Date: Monday, January 18, 2010, 10:56 AM

Hi Kellyn,

> How many DBA's inspect what is utilizing temp and undo?  How many DBA's see > a large temp and/or UNDO tablespace as a red flag in a database? 

I run this snapshot procedure via the Scheduler way more frequently than I'd like, but the output (when graphed via Toad's FastReports) presents a clear indicator of either issues or the need for more TEMP (or both!) for our ERP system on 10.1.0.5:

CREATE OR REPLACE procedure TEMP_MONITOR AS BEGIN
INSERT INTO whos_using_temp
(
    SELECT sysdate "TIME_STAMP", vsu.username, vsu.sql_id, vsu.tablespace,
vsu.usage_mb, vst.sql_text, vp.spid
    FROM
    (
        SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
sum(blocks)*8192/1024/1024 "USAGE_MB"
        FROM v$sort_usage
        HAVING SUM(blocks)> 10000 -- 80MB
        GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
    ) "VSU",
    v$sqltext vst,
    v$session vs,
    v$process vp

WHERE vsu.sql_id = vst.sql_id
    AND vsu.sqladdr = vst.address
    AND vsu.sqlhash = vst.hash_value
    AND vsu.session_addr = vs.saddr
    AND vs.paddr = vp.addr
    AND vst.piece = 0

);
COMMIT;
END TEMP_MONITOR;
/

I hope most should be able to figure out the one-time SQL needed to create the output table, as well as the necessary explicit privs required to run this procedure.

As far as UNDO goes, I'm "lucky" that it's been 10GB since before I was here and has not needed to be increased at all in three years, despite a substantial growth in the footprint of the DB.  "Lucky" in my case is backhanded in that the DB-agnostic ERP commits after every DML statement. But no 1555s that I can recall.

Rich

--
http://www.freelists.org/webpage/oracle-l





      
--
http://www.freelists.org/webpage/oracle-l