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
>
>
>
> Get your FREE personalized e-mail at
> http://www.canada.com
>
> --------
> 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
>
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnson Poovathummoottil
INET: joni_65_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 Mon Jul 09 2001 - 11:46:13 CDT