Re: "direct path write" wait event
From: <shweta.kaparwan_at_googlemail.com>
Date: Sat, 31 Jan 2009 18:22:17 -0800 (PST)
Message-ID: <165522d3-7715-4867-99e0-67aabbce3048_at_w1g2000prk.googlegroups.com>
On Feb 1, 12:16 am, shweta.kapar..._at_googlemail.com wrote:
> On Jan 31, 10:33 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 31, 12:54 pm, shweta.kapar..._at_googlemail.com wrote:
>
> > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > Once allocated to the single sort segment that exists per instance
> > space is not released from the segment for as long as the instance
> > remains running though the not in use extents in the segment are
> > available for allocation to sessions that need sort space.
>
> > This assumes your temporary tablespace was created as either create
> > tablespace temp temporary or better yet create temporary tablespace
> > temp tempfile '...' and not the older create tablespace temp
> > [permanent] since all three forms should still be possible under 9.2
> > though nobody should be using the older form which I listed last or
> > the newer but not obsoleted form I listed first. But some upgraded
> > systems might not have been changed.
>
> > See the v$sort_segment and v$sort_usage views to see the allocated
> > temp space and who is using temp and what for: sort, temporary table,
> > and temporary LOB segment, respectively.
>
> > Anyway Charles provided the important information.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks mark.- Hide quoted text -
>
> - Show quoted text -
DEDICATED SQL> Received on Sat Jan 31 2009 - 20:22:17 CST
Date: Sat, 31 Jan 2009 18:22:17 -0800 (PST)
Message-ID: <165522d3-7715-4867-99e0-67aabbce3048_at_w1g2000prk.googlegroups.com>
On Feb 1, 12:16 am, shweta.kapar..._at_googlemail.com wrote:
> On Jan 31, 10:33 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 31, 12:54 pm, shweta.kapar..._at_googlemail.com wrote:
>
> > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > Once allocated to the single sort segment that exists per instance
> > space is not released from the segment for as long as the instance
> > remains running though the not in use extents in the segment are
> > available for allocation to sessions that need sort space.
>
> > This assumes your temporary tablespace was created as either create
> > tablespace temp temporary or better yet create temporary tablespace
> > temp tempfile '...' and not the older create tablespace temp
> > [permanent] since all three forms should still be possible under 9.2
> > though nobody should be using the older form which I listed last or
> > the newer but not obsoleted form I listed first. But some upgraded
> > systems might not have been changed.
>
> > See the v$sort_segment and v$sort_usage views to see the allocated
> > temp space and who is using temp and what for: sort, temporary table,
> > and temporary LOB segment, respectively.
>
> > Anyway Charles provided the important information.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks mark.- Hide quoted text -
>
> - Show quoted text -
Also we use "dedicated server" :
SQL> select distinct(server) from v$session;
SERVER
DEDICATED SQL> Received on Sat Jan 31 2009 - 20:22:17 CST