Re: How to clear UNDOTBS- Slightly OT
Date: Mon, 18 Jan 2010 18:50:58 -0800 (PST)
Message-ID: <522863.26951.qm_at_web32005.mail.mud.yahoo.com>
I've just been reporting the sql_id with the user in my report- like this one that shows the query along with the rest of the improtant info, Kerry!
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, Kerry Osborne <kerry.osborne_at_enkitec.com> wrote:
From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Subject: Re: How to clear UNDOTBS- Slightly OT
To: rjoralist_at_society.servebeer.com
Cc: "ORACLE-L" <oracle-l_at_freelists.org>
Date: Monday, January 18, 2010, 1:43 PM
Hi Rich,
That's a pretty useful idea and script. I think the sql_id of v$sort_usage view reports the prev_sql_id and not the current sql_id though (at least it appears so in 10.2.0.4 and 11.2.0.1 in the quick test I did). Maybe that was intentional. Anyway, the following select gives me the sql statement causing the temp usage while it is happening.
SELECT sysdate "TIME_STAMP", vsu.username, vs.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)> 100 -- 80MB
GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
) "VSU",
v$sqltext vst,
v$session vs,
v$process vp
WHERE vs.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;
It's only slightly modified from yours by joining on and reporting vs.sql_id instead vsu.sql_id (which as I said appears to be the prev_sql_id).
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Jan 18, 2010, at 11:56 AM, Rich Jesse wrote:
> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 18 2010 - 20:50:58 CST