Re: direct path write wait

From: "blr_dba" <deepak_blr@xxxxxxxxxxx>

To: "Paul Drake" <bdbafh@xxxxxxxxx>

Date: Wed, 27 Dec 2006 21:43:48 +0530

Hi,
It is a DWH query, which has many outer joins. And has "order by" clause as
well.
The wait is because of sorts only but cant avoid sorting....
----- Original Message -----
From: Paul Drake
To: deepak_blr@xxxxxxxxxxx
Cc: ORACLE-L
Sent: Wednesday, December 27, 2006 9:32 PM
Subject: Re: direct path write wait
On 12/27/06, blr_dba <deepak_blr@xxxxxxxxxxx> wrote:
Dear Gurus,
Am having huge amount of waits on "direct path write event". This event has
98% of the total wait time. Now we want to tune it.
Perhaps you might first want to tune the SQL statements that are causing the
writes to tempfiles (sorts, hash_joins, GTT usage)?
Pd
We are using Solaris 9 and Oracle 9.2.0.7 .
After examining the system we found that logging is enabled on the mounted
UFS file systems.
Need your expert advice on the following...
> How to know whether the UFS is enabled for asynchronous IO or direct IO?
> Which one is better for DWH environments? Pros and cons of each.
> Is filesystem cache is involved in both of the above methods?
Please suggest the best solution for my environment.
Looking forward to your ever valuable feedback.
Deepak
--
http://www.freelists.org/webpage/oracle-l
--
-- ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 4';