Re: Managing temp resource by looking at past useage

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 20 Sep 2011 14:53:55 +0000 (UTC)
Message-ID: <pan.2011.09.20.14.53.54_at_gmail.com>

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

> 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

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

Received on Tue Sep 20 2011 - 09:53:55 CDT