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@xxxxxxxxxxx> wrote: From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx> Subject: Re: How to clear UNDOTBS- Slightly OT To: rjoralist@xxxxxxxxxxxxxxxxxxxxx Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx> 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 > > > -- > //www.freelists.org/webpage/oracle-l > > -- //www.freelists.org/webpage/oracle-l