Re: How to clear UNDOTBS- Slightly OT
Date: Mon, 18 Jan 2010 18:50:58 -0800 (PST)
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!
"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
Cc: "ORACLE-L" <oracle-l_at_freelists.org> Date: Monday, January 18, 2010, 1:43 PM
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 220.127.116.11 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
SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
HAVING SUM(blocks)> 100 -- 80MB
GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
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).
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 > >Received on Mon Jan 18 2010 - 20:50:58 CST