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

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

Fwd: db_file_multiblock_read_count + db file scattered read +

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 12 Mar 2003 22:18:47 -0800
Message-ID: <F001.0056886A.20030312221847@fatcity.com>


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.

>Date: Wed, 12 Mar 2003 20:08:48 -0800
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "Erroba, Ildefonso N" <Erroba.Ildefonso_at_emeryworld.com>
>Sender: root_at_fatcity.com
>Reply-To: ORACLE-L_at_fatcity.com
>From: "Erroba, Ildefonso N" <Erroba.Ildefonso_at_emeryworld.com>
>Subject: db_file_multiblock_read_count + db file scattered read + truss
>Organization: Fat City Network Services, San Diego, California
>
>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).
Received on Thu Mar 13 2003 - 00:18:47 CST

Original text of this message

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