Re: KEEP pool and disk reads

From: <Rajesh.Rao_at_jpmchase.com>
Date: Sat, 26 Jun 2004 12:56:58 -0400
Message-ID: <OFEB4C3644.0C3EFF31-ON85256EBF.005B0184-85256EBF.005D1B51@chase.com>

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_at_sbcglobal.n       To:     oracle-l_at_freelists.org                                            
                    et>                       cc:                                                                       
                    Sent by:                  Subject:     Re: KEEP pool and disk reads                                 
                    oracle-l-bounce_at_fre                                                                                 
                    elists.org                                                                                          
                                                                                                                        
                                                                                                                        
                    06/26/2004 12:22 AM                                                                                 
                    Please respond to                                                                                   
                    oracle-l

On 06/26/2004 12:00:03 AM, Rajesh.Rao_at_jpmchase.com 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Jun 26 2004 - 11:53:57 CDT