Re: "direct path write" wait event
From: <shweta.kaparwan_at_googlemail.com>
Date: Sat, 31 Jan 2009 09:54:44 -0800 (PST)
Message-ID: <067f9762-f65e-4ee6-93ed-7de6c71f9682_at_i20g2000prf.googlegroups.com>
On Jan 31, 3:22 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Jan 31, 5:10 am, shweta.kapar..._at_googlemail.com wrote:
>
> snip
>
> > No free space in temp.
>
> snip
>
> > Kindly , help how to reduce direct path write waits?
>
> If you run a query that uses up all your temp space it means that
> something in the query is causing a sort ( order by probably ).
>
> Your best approach in getting the quickest results from a query that
> uses up all your temp space is the Cary Millsap approach ... just
> don't do it.
>
> There may be ( probably is ) something wrong with the query ... can it
> be rewritten? Otherwise you are going to have to perhaps increase
> your available temp space.
>
> Many people have procedures put in place on OLTP systems ( by various
> ways ) to stop/prevent/kill sessions that exceed some amount of temp
> space usage ( more than 1 gig of temp space ... more than 256 meg of
> temp space ... more than 5 gig of temp space ... opinions vary ).
Date: Sat, 31 Jan 2009 09:54:44 -0800 (PST)
Message-ID: <067f9762-f65e-4ee6-93ed-7de6c71f9682_at_i20g2000prf.googlegroups.com>
On Jan 31, 3:22 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Jan 31, 5:10 am, shweta.kapar..._at_googlemail.com wrote:
>
> snip
>
> > No free space in temp.
>
> snip
>
> > Kindly , help how to reduce direct path write waits?
>
> If you run a query that uses up all your temp space it means that
> something in the query is causing a sort ( order by probably ).
>
> Your best approach in getting the quickest results from a query that
> uses up all your temp space is the Cary Millsap approach ... just
> don't do it.
>
> There may be ( probably is ) something wrong with the query ... can it
> be rewritten? Otherwise you are going to have to perhaps increase
> your available temp space.
>
> Many people have procedures put in place on OLTP systems ( by various
> ways ) to stop/prevent/kill sessions that exceed some amount of temp
> space usage ( more than 1 gig of temp space ... more than 256 meg of
> temp space ... more than 5 gig of temp space ... opinions vary ).
Hi Thanks for the help.
Also i want to know how to rrelase the free space in Temporary
tablespace.
a) Making it offline/online.
b) killing the session, who is causing this.
since all these java session originates from application layer, implementing (b) above would be difficult.
regards
shweta
what is that Received on Sat Jan 31 2009 - 11:54:44 CST