| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Temporary tablespace growth
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
LAST_MEMORY_USED LAST_EXECUTION LAST_DEGREE
ACTIVE_TIME MAX_TEMPSEG_SIZE
 |
 |