"direct path write" wait event

From: <shweta.kaparwan_at_googlemail.com>
Date: Sat, 31 Jan 2009 02:10:27 -0800 (PST)
Message-ID: <f6c8e5d8-5a14-42f7-8fe2-5220e5f68a48_at_e6g2000vbe.googlegroups.com>

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- ---------
-------------------
Begin Snap:        58 31-Dec-08 09:18:02  #######        .0
  End Snap:        59 31-Dec-08 09:31:19  #######        .0
   Elapsed:               13.28 (mins)

Cache Sizes (end)

---

               Buffer Cache: 512M Std Block Size: 8K

           Shared Pool Size: 752M Log Buffer: 512K

Load Profile

~~~~~~~~~~~~                            Per Second       Per
Transaction
                                   ---------------
---------------
                  Redo size:             16,819.64
5,054.77
              Logical reads:             28,533.04
8,574.97
              Block changes:                 37.31
11.21
             Physical reads:              2,291.61
688.69
            Physical writes:              4,898.05
1,472.00
                 User calls:                173.51
52.14
                     Parses:                 63.73
19.15
                Hard parses:                  0.61
0.18
                      Sorts:                  1.61
0.48
                     Logons:                  0.06
0.02
                   Executes:                 64.97
19.52
               Transactions:                  3.33

Top 5 Timed Events

---

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
direct path write                                 978,716
2,475    41.56
direct path read                                  645,975

2,002 33.63
CPU time
1,222 20.52

control file sequential read                       25,617
49      .82
db file scattered read                              6,728
47      .78
 
-------------------------------------------------------------



                                                     CPU      Elapsd

  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- ---------

---

      7,573,010 303 24,993.4 72.8 428.23 1186.73 2843313473
Module: java_q4p_at_<host> (TNS V1-V3)
SELECT col1,code,col3,...col41,col42 FROM T1 WHERE CODE IN ( :P16 , :P17 , :P18 , :P19 )
ORDER BY 41 DESC Execution plan of the above query shows:

Operation	                             Object Name	Rows	Bytes	Cost

SELECT STATEMENT Optimizer Mode=FIRST_ROWS		        57 K	 	11376
  SORT ORDER BY		                                        57 K	4 M

11376

    INLIST ITERATOR

      TABLE ACCESS BY INDEX ROWID	      T1	        57 K	4 M	10194
        INDEX RANGE SCAN	              CODE	        57 K	 	195

table T1 has 1500 K rows.
T1 has non unique index on code
T1 has non unique index on col41

My observations:

• direct path write wait event is due to sorting ( writing temp segment) in above sql
• sorts per sec 1.61
• Temp Free Space shows: select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V $TEMP_SPACE_HEADER;

   TABLESPACE_NAME, BYTES_USED, BYTES_FREE     TEMP ,2147483648, 0 No free space in temp.

also we have

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
sort_area_size                       integer     524288

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
pga_aggregate_target                 big integer 209715200

Kindly , help how to reduce direct path write waits?

Regards

Shweta Received on Sat Jan 31 2009 - 04:10:27 CST