RE: Sorting High Water Mark anywhere?
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <rich@xxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 25 Jan 2013 09:57:53 -0600
Thanks Jesse - that's definitely a very large piece of the puzzle. Monitoring
at intervals hadn't really occurred to me as I was going down the road of
historical information. I should probably leverage both - thanks for sharing!
Chris
-----Original Message-----
From: Rich Jesse [mailto:rich@xxxxxxxxxxxxxxxxxxxxx]
Sent: Friday, January 25, 2013 9:55 AM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Sorting High Water Mark anywhere?
Hey Chris,
> I'd like to be able to determine, say over 24 hours, how much sorting
> is being done in memory, and how much is begin done on disk.
I needed to monitor large temp usage, so I threw together this:
SELECT /* RULE */ 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
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;
Caveats: blocksize is hardcoded, the HAVING clause only looks for large usage,
it doesn't consider CTAS usage (see MOS 67534.1), and the RULE hint was needed
for performance in 10.1.
The view needs to be polled at regular intervals, so it's not a true HWM.
It's just something to possibly get you started down the manual path of
monitoring TEMP.
HTH! GL!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
