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


It is a DWH query, which has many outer joins. And has "order by" clause as 

The wait is because of sorts only but cant avoid sorting....

  ----- Original Message ----- 
  From: Paul Drake 
  To: deepak_blr@xxxxxxxxxxx 
  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)? 


    We are using Solaris 9 and Oracle .

    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. 



  -- ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 4'; 

Other related posts: