10046 trace producing a new wait that is not normally there
- From: ryan_gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 22 Aug 2006 17:09:49 +0000
We have an update statement that runs in 3 minutes with no trace or a level 1
trace, but ran for 2 hours and we killed it with a level 12 trace.
we got 20,000 'direct path read temp ' waits, but ONLY with a level 12 trace
and ONLY on this update. We have not tested it in multiple environments. This
is on 10.2 on solaris.
We didn't see this wait in v$active_session_history when we do not have a level
12 trace going. I did not see anything about this on metalink. Does this strike
you as one of those bugs where its one thing in one version in one environment,
etc...? I have gotten those occasionally?
10.3.5 direct path read and direct path read temp
When a session is reading buffers from disk directly into the PGA (opposed to
the buffer cache in SGA), it waits on this event. If the I/O subsystem does not
support asynchronous I/Os, then each wait corresponds to a physical read
request.
If the I/O subsystem supports asynchronous I/O, then the process is able to
overlap issuing read requests with processing the blocks already existing in
the PGA. When the process attempts to access a block in the PGA that has not
yet been read from disk, it then issues a wait call and updates the statistics
for this event. Hence, the number of waits is not necessarily the same as the
number of read requests (unlike db file scattered read and db file sequential
read).
Check the following V$SESSION_WAIT parameter columns:
P1 - File_id for the read call
P2 - Start block_id for the read call
P3 - Number of blocks in the read call
10.3.5.1 Causes
This happens in the following situations:
The sorts are too large to fit in memory and some of the sort data is written
out directly to disk. This data is later read back in, using direct reads.
Parallel slaves are used for scanning data.
The server process is processing buffers faster than the I/O system can return
the buffers. This can indicate an overloaded I/O system.
10.3.5.2 Actions
The file_id shows if the reads are for an object in TEMP tablespace (sorts to
disk) or full table scans by parallel slaves. This is the biggest wait for
large data warehouse sites. However, if the workload is not a DSS workload,
then examine why this is happening.
10.3.5.2.1 Sorts to Disk
Examine the SQL statement currently being run by the session experiencing waits
to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL
statement that is generating the sort. Also query the statistics from V$SESSTAT
for the session to determine the size of the sort. See if it is possible to
reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is
MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the
sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is
AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET. See "PGA
Memory Management".
10.3.5.2.2 Full Table Scans
If tables are defined with a high degree of parallelism, then this could skew
the optimizer to use full table scans with parallel slaves. Check the object
being read into using the direct path reads. If the full table scans are a
valid part of the workload, then ensure that the I/O subsystem is configured
adequately for the degree of parallelism. Consider using disk striping if you
are not already using it or Automatic Storage Management (ASM).
10.3.5.2.3 Hash Area Size
For query plans that call for a hash join, excessive I/O could result from
having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then
consider increasing the HASH_AREA_SIZE for the system or for individual
processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to
increase PGA_AGGREGATE_TARGET.
Other related posts:
