RE: db_file_multiblock_read_count + db file scattered read + truss

[Cary Millsap]

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, 

Re: db_file_multiblock_read_count + db file scattered read + truss

[Jonathan Lewis]

Purely a guess, but looking at the figures
do you think that Oracle is using readv for
values up to 128K, and pread for values
larger than 128K ?


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
 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

 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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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).