RE: db_file_multiblock_read_count and performance

Actually, if you'll forgive me for putting words into your mouth, putting words into Ryan's mouth, I *think* the question Ryan is trying to ask is:

"I've been playing around with different settings for db_file_multiblock_read_count,
and I can see from the 10046 traces that I've taken that it can and does have a
real effect on the number of blocks actually read, as I can observe that in the
p3 values of the 'db file sequential read' events recorded in the 10046 traces.
However, while I *HAVE* been able to see real differences in the number of blocks
per read, I have been unable to detect any measurable difference in response time.
If that's the case, then is there any *real*, *measurable* benefit to setting
dbfmbrc>1?"

Now, for the answer:

Since dbfmbrc controls the number of blocks read per read call, then if the total
number of blocks read is fixed, increasing dbfmbrc should decrease the number of
read calls, and decreasing the dbfmbrc should increase the number of read calls.

So, on a system w/ dbfmbrc=3D1, the system will have to (theoretically) = do
128x=20
more read calls than a system w/ dbfmbrc=3D128. Now, each read call translates
to an actual read() system call. So, each read is a context switch between
user and kernel space. This is a real, measurable, quantifiable effect.

Since you claim to not see any differences, I'd really like to see your=20 test case definition.

Note that in the Oracle statistics, context switching information is collected
under STAT#s 268 and 269, "OS Voluntary context switches" and "OS Involuntary
context switches". Note also that these stats return 0 unless statistics_level=3Dall.

Hope that helps,

-Mark

Ethan Post said:
> I think what he is trying to prove is that given two full table scans
> one should perform faster by adjusting the size of the
> multiblock reads.
> The right questions might be....
>
> 1) Is DBMRC informative in nature only (tells Oracle what the actual
> read count size is for OS)?
>
> or
>
> 2) Does DBMRC actually influence the size of read count at IO level?
> =3D20
>

Wolfgang Breitling said:
> Yes, I certainly have. I used it in conversion programs where
> I knew=3D20
> that a lot of full scans were performed.
>
> ryan_gaffuri_at_comcast.net wrote:
>
> > has anyone see a response time improvement from this parameter
> anywhere?

--
http://www.freelists.org/webpage/oracle-l