Re: Managing temp resource by looking at past useage

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 21 Sep 2011 08:12:03 -0700 (PDT)
Message-ID: <9b5bab35-c0b6-493b-b4db-3c2cce7db2df_at_b10g2000vbz.googlegroups.com>

On Sep 20, 9:17 am, dba cjb <chris.br..._at_providentinsurance.co.uk> 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, 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 -- Received on Wed Sep 21 2011 - 10:12:03 CDT