direct path write temp wait events in oracle

This wait event is similar to direct path write event with only difference that I/O performed are made to temporary files. This wait event signifies sort segment, hashes or bitmap opearation are not fitting in the PGA. This is the case of one pass scan or multi pass scan. This wait event may also occur for creation of global  temporary tables.

So Direct path write temp is an direct access path in which many Oracle blocks are written directly
to the temporary files by the Oracle Server process.  To know which tempfiles are affected query p1 column from v$session.

Note: since temp file numbers are found by adding 200(default value of db_files or whatever the value of db_files has been set, related this P1 information to v$tempfile accordingly.
causes:

• sorting(order by)
• sort megre joi
• hash join
• union
• distinct
• create index
• creating global temporary tables

action:

• use order by on indexes column
• when use hash join make sure hash table can fit into memory(statistics must be gathered accurately else oracle can use bigger table for hashing)
• avoid sort merge join by using hint no_use_merge
• if column data is already sorted then use no sort in creating indexes
• use UNION ALL in place of UNIION

This wait event implies work area size operations are not fitting in PGA so increase PGA_AGGREGATE_TARGET. Other reason for not using the PGA inspite of setting it large is Oracle generally uses 5% to 25% of PGA_AGGREGATE_TARGET value as upper bound in 11g. Other criteria of upper bound is oracle does not use PGA above _PGA_MAX_SIZE  and _shm_max_size hidden parameters which defaults to 200M. So increase _PGA_MAX_SIZE ,_SHM_MAX_SIZE and PGA_AGGREGATE_TARGET.


I increased both PGA parameters in my dataware house databases in 10gR2 and processing time reduced significantltly