Re: Managing temp resource by looking at past useage
From: dba cjb <chris.brown_at_providentinsurance.co.uk>
Date: Thu, 22 Sep 2011 06:31:19 -0700 (PDT)
Message-ID: <8b0c7edb-a0db-4289-bb09-0c9bb456e120_at_g30g2000yqc.googlegroups.com>
On Sep 21, 4:12 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 22 Sep 2011 06:31:19 -0700 (PDT)
Message-ID: <8b0c7edb-a0db-4289-bb09-0c9bb456e120_at_g30g2000yqc.googlegroups.com>
On Sep 21, 4:12 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
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
Received on Thu Sep 22 2011 - 08:31:19 CDT