| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to improve the performance of "Direct path Write"?
Consider that direct path event are the ones involve in f.e. sort_direct_writes=TRUE in 8.0.X or 7.3.X. In 8i doesn't exist as a parameter. However it is feature included in the engine with default values for their buffers. Also insert /*+ APPEND */ or sql*loader direct=y generates that kind of wait. And I don't think is bad. Although you should take a look of the paging. Take into account that this wait doesn't use the buffer cache.
On the other hand as you said increasing the sort area size doesn't imply an improvement in sorts performance. You can say it is a matter of paging. However it is also a matter of the way Oracle resolves sorting and the merging with the temp tablespace. As a renmark if you can avoid all the sorting increasing the sort area you could feel the improvement.
And in the case of the merging with temp tablespace refer to the sort_multiblock_read_count 8i parameter
Regards.-
--- Johnson Poovathummoottil <joni_65_at_yahoo.com>
wrote:
> We too had a similar problem and thought that we
> should do something to make the sorts faster. But
> investigating a little deeper found the actual
> amount
> of time waited in direct path read/write was very
> little although there are a number waits attibuted
> to
> direct path read and write while sorting. We also
> tried increasing the sort_area_size, but it gave
> adverse performance.
> Of the total time spend for the query cpu time was
> always above 90 percent and wait time was less than
> 10
> %. we executed the same queries on faster cpu
> machines
> and got better results. Also parallelizing the
> queries
> helped.
>
> --- michaelnorbert_at_hotmail.com wrote:
> > How many extents are being written to your
> temporary
> > tablespace?
> > You could increase your sort_area_size if you have
> > the memory.
> >
> > I have seen 1 gig of temp tablespace being chewed
> up
> > reduced to 0 by tuning the sql.
> >
> > You may not always realize that your program
> > statements invoke a sort. Sorting is performed by
> > the following statements:
> >
> > In my case I put an index on the columns in the
> > order by and that took care of the sorts to disk.
> >
> > w CREATE INDEX w DISTINCT
> > w GROUP BY w ORDER BY
> > w INTERSECT w MINUS
> > w UNION w Unindexed table joins
> > w Some correlated subqueries
> >
> > Depending on your system, you can put your
> temporary
> > files on a Raid 1 layout.
> >
> > HTH
> >
> > Mike
> > michaelnorbert_at_hotmail.com
> > On Mon, 09 July 2001, WinterSun_Zhao wrote:
> >
> > >
> > > Hi, DBAs:
> > > I find a process occupy about 50% Cpu. I
> checked
> > the wait event, it was waiting for the event of
> > "direct path write".
> > > I know it is because it is writing to the
> > temporary tablespace. The extent size of the
> > temporary tablespace is 5M, it is temporary.
> > > Would you please tell me how to improve the
> > performance of "Direct path write"? How to
> decrease
> > its cpu occuption.
> > > Thanks!
> > >
> > > WinterSun Zhao
> > > Oracle DBA ( 2 yr+)
> > > Unix SysAdmin ( 4 yr)
> > > zhaohl_at_pconline.com.cn
> > > WinterSun_Zhao_at_21cn.com
> > > www.pconline.com.cn
> > >
> > >
> > >
> > >
> > > --------
> > > Oracle documentation is here:
> >
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> > > To unsubscribe: send a blank email to
> > oracledba-unsubscribe_at_LAZYDBA.com
> > > To subscribe: send a blank email to
> > oracledba-subscribe_at_LAZYDBA.com
> > > Visit the list archive:
> > http://www.LAZYDBA.com/odbareadmail.pl
> > > Tell yer mates about http://www.farAwayJobs.com
> > > By using this list you agree to these
> > terms:http://www.lazydba.com/legal.html
> >
> >
> >
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: ctrassens_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jul 10 2001 - 01:40:34 CDT
 |
 |