| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: db_file_multiblock_read_count + db file scattered read + truss
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
$ 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-LReceived on Thu Mar 13 2003 - 01:53:48 CST
(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).
 |
 |