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: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erroba, Ildefonso N INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).