Re: "direct path write" wait event
From: <shweta.kaparwan_at_googlemail.com>
Date: Sat, 31 Jan 2009 16:16:34 -0800 (PST)
Message-ID: <dbe69149-da84-4bfd-b49a-690c3638002f_at_v5g2000pre.googlegroups.com>
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 -
Date: Sat, 31 Jan 2009 16:16:34 -0800 (PST)
Message-ID: <dbe69149-da84-4bfd-b49a-690c3638002f_at_v5g2000pre.googlegroups.com>
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. Received on Sat Jan 31 2009 - 18:16:34 CST