Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Temporary tablespace growth

Re: Temporary tablespace growth

From: hgha <houman_at_globe-tekcorp.com>
Date: 7 Dec 2006 08:21:05 -0800
Message-ID: <1165508465.902826.295580@16g2000cwy.googlegroups.com>

EscVector wrote:
> hpuxrac wrote:
> > hgha wrote:
> > > my temporary tablespace has grown to it's maximum (32GB) in two
> > > separate incidents. is there
> > > any way I can find out what query has been responsible for this growth?
> > > V$sort_usage doesn't tell me much.
> > >
> > > thanks
> >
> > My experience is that you need to catch the "culprits" while they are
> > still running and chewing up TEMP tablespace instead of after the fact.
> > Depending on what version you are running etc some of the new GUI
> > based reporting might be able to find this information after the fact.
> >
> > Hmm ... wonder if new OEM in 10g does automatic alerting for any
> > queries over some threshold of space in TEMP usage.
> >
> > I have had a couple of instances ( prior jobs ) where I had to have
> > something monitor periodically on any queries using more than a given
> > percent of TEMP. ( It's usually someone trying to do ad hoc reporting
> > and throwing in bad untested sql and/or cartesian joins in an OLTP
> > environment ).
> >
> > Then it can get political depending on who it is running the stuff.
> > Just because you can see it occurring doesn't mean that you can kill it
> > automatically sometimes.
> >
> > If you want me to dig up the old script that I had reply to my email
> > and I will see if I can hunt it down.
>
> Grid can also give these stats, but here the old school way...
>
> Dig up the sql that has the greatest MAX_TEMPSEG_SIZE.
>
> desc v$sql_workarea;
>
> Name
> ------------------------
> ADDRESS
> HASH_VALUE
> SQL_ID
> CHILD_NUMBER
> WORKAREA_ADDRESS
> OPERATION_TYPE
> OPERATION_ID
> POLICY
> ESTIMATED_OPTIMAL_SIZE
> ESTIMATED_ONEPASS_SIZE
> LAST_MEMORY_USED
> LAST_EXECUTION
> LAST_DEGREE
> TOTAL_EXECUTIONS
> OPTIMAL_EXECUTIONS
> ONEPASS_EXECUTIONS
> MULTIPASSES_EXECUTIONS
> ACTIVE_TIME
> MAX_TEMPSEG_SIZE
> LAST_TEMPSEG_SIZE
Somehow MAX_TEMPSEG_SIZE is always null everywhere! The database version is 10g. Received on Thu Dec 07 2006 - 10:21:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US