Managing temp resource by looking at past useage

Goal is to answer

Which users were using temp , with what sql & how much at a given time

I run the sql every 5 mins

SELECT
s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,s.sql_hash_value,s.sql_id,sum(su.blocks) FROM v$sort_usage su,v$session s,v$px_session ps WHERE s.sid=ps.sid(+)
AND s.saddr = su.session_addr
AND s.serial# = su.session_num
GROUP BY
s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqladdr,s.sql_hash_value,s.sql_id;

Following sample output is ok

213	200	DWH	svcl-qlikview	TEMPORARY	HASH	108052920	95n9ych371hds	17024
127	200	DWH	svcl-qlikview	TEMPORARY	HASH	108052920	95n9ych371hds	17024

However ..sometimes there is no sql_id to account for temp usage

I would like advice on:-

1. If i am using the right method but not executing properly ?
2. is there a better method for historically tying temp resource to sql?
3. Am I trying to achieve something that is not achievable?

Thoughts are welcome

regards
Chris B Received on Tue Sep 20 2011 - 08:17:42 CDT