Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: db_file_multiblock_read_count + db file scattered read +

RE: db_file_multiblock_read_count + db file scattered read +

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 13 Mar 2003 11:29:21 -0800
Message-ID: <F001.005696FE.20030313112921@fatcity.com>


I would expect the pread64() calls to map to "db file sequential read" and the readv() calls to map to "db file scattered read". Jeff's "Why are Oracle's Read Events 'Named Backwards'?" explains (www.hotsos.com/catalog).

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101, Mar 25-27 Oxford
- Hotsos Clinic 101, Apr 8-10 Chicago

-----Original Message-----
Ildefonso N
Sent: Thursday, March 13, 2003 12:44 PM
To: Multiple recipients of list ORACLE-L

Thanks Wolfgang, Cary for taking the time to answer my first question. After taking a second look on the hotsos doc, it did in fact address my problem, specifically the third bullet point. I simply missed to apply the pattern, shown in the example for the third point, to my situation.

As for pread64/readv, after looking at the whole truss output, I did not find a single read using readv() that exceeded 128K, although, I found some pread64() that were less than 128K, which I am guessing is due to "db file sequential reads".

-----Original Message-----
Sent: Wednesday, March 12, 2003 10:14 PM To: Multiple recipients of list ORACLE-L

My explanation is that blocks 1473, 2566, 2580, 2590, 2617, 2628, 2648, 2681, 2695, 2702, 2714, 2719, 2748, 2760, 2773, 2794, 2798, 2811, and 2819 were already in the buffer pool and the FTS reads "around" them.

At 08:08 PM 3/12/2003 -0800, you wrote:
>I have been wondering how to set the optimal value of dfmbc
>(db_file_multiblock_read_count), filesystem pagesize/blocksize, db blk
>size, in an effort to optimize FTS.
>
>I have done testing using event 10046 and truss to find the p3 value on the
>db file scattered then comparing it with the truss output. It seems that
the
>value in trace file corelates to the truss value (pread64/readv), but I am
>having trouble trying to explain why lines 12 and 21 on both output files
>are not using the dfmbc setting of 128 (1M) on the fetch? Also, why is
truss
>showing pread64 and then readv on lines 24..32? I have read the hotsos doc
>"Predicting Multi-Block Read Call Sizes" (by Jeff Holt) to get some
>understanding on how dfmbc affects database I/O. It did not answer cover,
>though my situation, because the object does not cross extent boundary.
>
>Any answers would be highly appreciated. Your collective knowledge would
>surely help. Any info to other docs or links would also be valuable.
>
>Here is the output of event 10046, truss and other info of the test env.
>
> 1 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=10 p3=128
> 2 WAIT #3: nam='db file scattered read' ela= 9 p1=10 p2=138 p3=128
> 3 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=266 p3=128
> 4 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=394 p3=128
> 5 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=522 p3=128
> 6 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=650 p3=128
> 7 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=778 p3=128
> 8 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=906 p3=128
> 9 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=1034 p3=128
> 10 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1162 p3=128
> 11 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=1290 p3=128
> 12 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=1418 p3=55
> 13 WAIT #3: nam='db file scattered read' ela= 6 p1=10 p2=1474 p3=128
> 14 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1602 p3=128
> 15 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=1730 p3=128
> 16 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1858 p3=128
> 17 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=1986 p3=128
> 18 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=2114 p3=128
> 19 WAIT #3: nam='db file scattered read' ela= 4 p1=10 p2=2242 p3=128
> 20 WAIT #3: nam='db file scattered read' ela= 3 p1=10 p2=2370 p3=128
> 21 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2498 p3=68
> 22 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2567 p3=13
> 23 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2581 p3=9
> 24 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2591 p3=26
> 25 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2618 p3=10
> 26 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2629 p3=19
> 27 WAIT #3: nam='db file scattered read' ela= 9 p1=10 p2=2649 p3=32
> 28 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2682 p3=13
> 29 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2696 p3=6
> 30 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2703 p3=11
> 31 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2715 p3=4
> 32 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2720 p3=28
> 33 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2749 p3=11
> 34 WAIT #3: nam='db file scattered read' ela= 1 p1=10 p2=2761 p3=12
> 35 WAIT #3: nam='db file scattered read' ela= 0 p1=10 p2=2774 p3=20
> 36 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2795 p3=3
> 37 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2799 p3=12
> 38 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2812 p3=7
> 39 WAIT #3: nam='db file scattered read' ela= 5 p1=10 p2=2820 p3=128
> 40 WAIT #3: nam='db file scattered read' ela= 2 p1=10 p2=2948 p3=128

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Erroba, Ildefonso N
  INET: Erroba.Ildefonso_at_emeryworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Mar 13 2003 - 13:29:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US