Re: "direct path write" wait event
From: joel garry <joel-garry_at_home.com>
Date: Mon, 2 Feb 2009 10:43:07 -0800 (PST)
Message-ID: <4e0438ac-0a91-47a7-b5da-7fb3a2721b84_at_l33g2000pri.googlegroups.com>
On Jan 31, 6:22 pm, shweta.kapar..._at_googlemail.com wrote:
> 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>
You might want to look into the PGA advisor. I no longer have 9206, but as I recall, there was an OEM advisor screen that would show how many multipasses would be necessary at different sized PGA's. IIRC, for my production situation, changing the pga target from 200M to 800M reduced multipasses to almost nothing. I'm guessing (and following Charles' advice would show for sure) that if you get rid of the multipasses, you'll get rid of the direct write waits, which will ease I/O for everything. If you don't have OEM, there's an advisor table.
Date: Mon, 2 Feb 2009 10:43:07 -0800 (PST)
Message-ID: <4e0438ac-0a91-47a7-b5da-7fb3a2721b84_at_l33g2000pri.googlegroups.com>
On Jan 31, 6:22 pm, shweta.kapar..._at_googlemail.com wrote:
> 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>
You might want to look into the PGA advisor. I no longer have 9206, but as I recall, there was an OEM advisor screen that would show how many multipasses would be necessary at different sized PGA's. IIRC, for my production situation, changing the pga target from 200M to 800M reduced multipasses to almost nothing. I'm guessing (and following Charles' advice would show for sure) that if you get rid of the multipasses, you'll get rid of the direct write waits, which will ease I/O for everything. If you don't have OEM, there's an advisor table.
As Mark pointed out, it's normal for temp to appear full.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/jan/31/1b31blue221015-tech-world-sings-blues-praises/?uniontribReceived on Mon Feb 02 2009 - 12:43:07 CST