Platform oracle 10.2.0.4 on windows 2003

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

On Tue, 20 Sep 2011 06:17:42 -0700, dba cjb wrote:

(su.blocks)

Chris, I don't understand why are you running this query every 5 minutes?
Also, I don't understand how can you manage a resource? You should be
managing application instead of trying to manage a resource. You wouldn't
be doing this if there wasn't a problem. The problem is usually in SQL
statements. Somebody wrote a SQL which devours your temporary tablespace.
You should locate the SQL and try rewriting it. The query you're using
for this purpose is fairly adequate and more sophisticated than the query
I'm using:
select s.inst_id
s.username,
ss.sid,
s.TABLESPACE,
round(sum(s.blocks*t.block_size)/1048576,2) MB
from gv$sort_usage s,dba_tablespaces t,gv$session ss
where s.tablespace=t.tablespace_name
and s.session_addr=ss.saddr
and s.inst_id=ss.inst_id
group by s.instance_id,s.username,ss.sid, s.tablespace
order by 4 desc

My goal is to locate the SQL consuming the TEMP tablespace and possibly
fix it. I will only acquiesce to increasing the tablespace if the SQL
cannot be fixed, which is sometimes the case. Quarterly financial reports
have to run and have to consume huge amount of resources, period. It's
usually a scheduling problem, more than anything else.
Also, I usually have two temporary tablespaces, called TEMP_BATCH and
TEMP_UI. That, of course, works only if batch and interactive jobs are
neatly separated in separate schemas, which they usually are, for
security reasons. Batch jobs usually run as schema owner and UI has its
own tightly controlled schema with views, login triggers and alike.
Also, if you are using file system, which on Windows is probably the
case, you should consider auto-extensible temp tablespaces. That has
prevented my cell phone from ringing many times. Also, consider having a
de-normalized reporting database. Doesn't have to be Oracle, if licenses
are an issue. MongoDB can sometimes be used to that end.
--
http://mgogala.byethost5.com

On Sep 20, 9:17*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
Chris, this seems like an unnecessary waste of time unless you are
having issues exhausting temp. I mean if you are having issues then
tracking back to the SQL running when problems occur would be
worthwhile, but otherswise why not manage temp just by looking at how
much temp you have allocated and how much of this Oracle is using?

IMHO -- Mark D Powell --

On Sep 21, 4:12*pm, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
Hi Mark

I was reacting to a situation where temp resource was exhausted on
a management information database / I wanted to advise the database
owner
of who was grabbing resource ie scheduled jobs or user reports
....we could then decide future course of action ...either restricting
user or even adding more temp


regards
Chris B