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 + truss

RE: db_file_multiblock_read_count + db file scattered read + truss

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 12 Mar 2003 23:53:48 -0800
Message-ID: <F001.00568927.20030312235348@fatcity.com>


Jun,

Wolfgang is correct, and it is covered in Jeff's document. It's the third point (with example) listed at the bottom of page 1.

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: Wednesday, March 12, 2003 10:09 PM To: Multiple recipients of list ORACLE-L

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

     1  pread64(409, "0602\0\00280\0\nDEF5 6 [".., 1048576, 81920) = 1048576
     2  pread64(409, "0602\0\00280\08ADEF5 6 [".., 1048576, 1130496) =
1048576
     3  pread64(409, "0602\0\0028001\nDEF5 6 [".., 1048576, 2179072) =
1048576
     4  pread64(409, "0602\0\00280018ADEDE tC3".., 1048576, 3227648) =
1048576
     5  pread64(409, "0602\0\0028002\nDEF5 6 [".., 1048576, 4276224) =
1048576
     6  pread64(409, "0602\0\00280028ADEF5 6 [".., 1048576, 5324800) =
1048576
     7  pread64(409, "0602\0\0028003\nDEF5 6 [".., 1048576, 6373376) =
1048576
     8  pread64(409, "0602\0\00280038ADEF5 6 [".., 1048576, 7421952) =
1048576
     9  pread64(409, "0602\0\0028004\nDEF5 6 [".., 1048576, 8470528) =
1048576

    10 pread64(409, "0602\0\00280048ADEDE y >".., 1048576, 9519104) = 1048576

    11 pread64(409, "0602\0\0028005\nDEF5 6 [".., 1048576, 10567680) = 1048576

    12 pread64(409, "0602\0\00280058ADEF5 6 [".., 450560, 11616256) = 450560

    13 pread64(409, "0602\0\0028005C2DEF5 6E2".., 1048576, 12075008) = 1048576

    14 pread64(409, "0602\0\0028006 BDEF5 6E2".., 1048576, 13123584) = 1048576

    15 pread64(409, "0602\0\0028006C2DEF5 6E2".., 1048576, 14172160) = 1048576

    16 pread64(409, "0602\0\0028007 BDEF5 6E2".., 1048576, 15220736) = 1048576

    17 pread64(409, "0602\0\0028007C2DEDE7FEB".., 1048576, 16269312) = 1048576

    18 pread64(409, "0602\0\00280\b BDEF5 6E2".., 1048576, 0x01084000) = 1048576

    19 pread64(409, "0602\0\00280\bC2DEF5 6E2".., 1048576, 0x01184000) = 1048576

    20 pread64(409, "0602\0\00280\t BDEF5 6E2".., 1048576, 0x01284000) = 1048576

    21 pread64(409, "0602\0\00280\tC2DEF5 6E2".., 557056, 0x01384000) = 557056

    22  readv(409, 0xFFBE927C, 13)          = 106496
    23  readv(409, 0xFFBE927C, 9)           = 73728
    24 pread64(409, "0602\0\00280\n1FDEF5 6E2".., 212992, 0x0143E000) = 212992

    25 readv(409, 0xFFBE927C, 10) = 81920     26 pread64(409, "0602\0\00280\n EDEF5 6E2".., 155648, 0x0148A000) = 155648

    27 pread64(409, "0602\0\00280\n YDEF5 6E2".., 262144, 0x014B2000) = 262144

    28  readv(409, 0xFFBE927C, 13)          = 106496
    29  readv(409, 0xFFBE927C, 6)           = 49152
    30  readv(409, 0xFFBE927C, 11)          = 90112
    31  readv(409, 0xFFBE927C, 4)           = 32768
    32 pread64(409, "0602\0\00280\nA0DEF5 6E2".., 229376, 0x01540000) = 229376
    33  readv(409, 0xFFBE927C, 11)          = 90112
    34  readv(409, 0xFFBE927C, 12)          = 98304
    35 pread64(409, "0602\0\00280\nD6DEF5 6E2".., 163840, 0x015AC000) = 163840
    36  readv(409, 0xFFBE927C, 3)           = 24576
    37  readv(409, 0xFFBE927C, 12)          = 98304
    38  readv(409, 0xFFBE927C, 7)           = 57344
    39 pread64(409, "0602\0\00280\v04DEF5 6E2".., 1048576, 0x01608000) = 1048576

    40 pread64(409, "0602\0\00280\v84DEF5 6E2".., 1048576, 0x01708000) = 1048576

NAME                           VALUE  
------------------------------ -------
db_file_multiblock_read_count  128    
db_block_size                  8192   

File File
  ID Name                                                  
---- ------------------------------------------------------
  10 /ora/ljcbs503/u40/oradata/pdim/shipment_fact_data01.dbf

[ljcbs503:pdim]/ora/ljcbs503/u40/oradata/pdim > df -g . /ora/ljcbs503/u40 (/dev/vx/dsk/503ora/u40): 8192 block size 8192 frag size
88391680 total blocks 10882576 free blocks 10797680 available 170080 total files

  170037 free files     62976566 filesys id  
    vxfs fstype       0x00000004 flag             255 filename length

DB Information



Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production

$ uname -X
System = SunOS
Node = xxxxxx
Release = 5.8
KernelID = Generic_108528-17
Machine = sun4u
BusType = <unknown>
Serial = <unknown>
Users = <unknown>
OEM# = 0
Origin# = 1
NumCPU = 8

Jun Erroba
Oracle DBA
Menlo Worldwide Technologies
Ph: (503) 450-5771
Email: erroba.ildefonso_at_menloworldwide.com

-- 
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 - 01:53:48 CST

Original text of this message

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