Re: KEEP pool and disk reads
- From: Rajesh.Rao@xxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 26 Jun 2004 12:56:58 -0400
Jonathan / Mladen,
I did perform a 10046 trace, and as expected saw no sequential or scattered
read waits. Instead saw some direct path read and direct path write waits
in the trace file, as under :
WAIT #3: nam='direct path write' ela= 13255 p1=201 p2=6793 p3=125
WAIT #3: nam='direct path write' ela= 12945 p1=201 p2=6665 p3=125
WAIT #3: nam='direct path write' ela= 14130 p1=201 p2=6537 p3=125
WAIT #3: nam='direct path write' ela= 15772 p1=201 p2=6409 p3=125
WAIT #3: nam='direct path write' ela= 12162 p1=201 p2=6281 p3=116
Given that db_files is set to 200, the p1 value of 201 points to the
datafile belonging to my TEMP tablespace. I was not able to correlate the
628 disk reads shown in the autotrace, with the p3 values in the trace
files.
What are these "direct path write" waits? For, autotrace shows me all sorts
are happening in memory. Then, what are these writes on the TEMP
tablespace?
As per Jonathan's suggestion, I performed a 10032 trace, which showed the
number of blocks written with direct write was indeed 628. So says
autotrace.
---- Sort Statistics ------------------------------
Initial runs 1
Input records 62316
Output records 62316
Disk blocks 1st pass 628
Total disk blocks used 630
Total number of comparisons performed 62326
Comparisons performed by in-memory sort 62326
Temp segments allocated 1
Extents allocated 5
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 2
Block pins (for run directory) 1
Block repins (for run directory) 1
---- Direct Write Statistics -----
Write slot size 24576
Write slots used during in-memory sort 2
Number of direct writes 211
Num blocks written (with direct write) 628
Block pins (for sort records) 628
Cached block repins (for sort records) 11
Waits for async writes 196
---- Direct Read Statistics ------
Size of read slots for output 303104
Number of read slots for output 2
Number of direct sync reads 19
Number of blocks read synchronously 55
Number of direct async reads 19
Number of blocks read asynchronously 573
Mladen Gogala
<gogala@xxxxxxxxxxx To: oracle-l@xxxxxxxxxxxxx
et> cc:
Sent by: Subject: Re: KEEP pool and
disk reads
oracle-l-bounce@fre
elists.org
06/26/2004 12:22 AM
Please respond to
oracle-l
On 06/26/2004 12:00:03 AM, Rajesh.Rao@xxxxxxxxxxxx wrote:
> Now, how do I verify where the disk reads are coming from?
The event is 10046, level 10. Your physical reads are not caused by sort,
because statistics says that it was in-memory sort, which means that
it was quicksort in memory. You can turn 10046 on by using DBMS_SUPPORT
or DBMS_MONITOR. The event 10046 will show you what are you waiting for.
If you are waiting on event like db_block_sequential read, or
db_block_scattered_read, it is possible that you are waiting on disk I/O.
These wait events have arguments which will give you block which oracle
is waiting for. That should tell you where the blocks are and what are you
waiting for. My guess is, since you say that everything is in the SGA,
that you are waiting for the UNDO block.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
