Re: How to clear UNDOTBS- Slightly OT
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
- Date: Mon, 18 Jan 2010 14:43:03 -0600
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
Other related posts:
