RE: db_file_multiblock_read_count causing full scans to takelonger?
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- Date: Wed, 20 Dec 2006 14:54:48 -0600
Well, almost. Here's what I get on 10.2.0.2.0 on AIX 5.3:
SELECT LEAST(dcs/(ses*dbs), 1048576/dbs) "CALC", dfmbrc
FROM
(
SELECT a.value "DCS", b.value "SES", c.value "DBS", d.value
"DFMBRC"
FROM v$parameter a, v$parameter b, v$parameter c, v$parameter d
WHERE a.name = 'db_cache_size'
AND b.name = 'sessions'
AND c.name = 'db_block_size'
AND d.name = 'db_file_multiblock_read_count'
);
CALC DFMBRC
---------- -------
68.2666667 66
At some point, I'll find out *why* this comes up this way.
Sorry for coming in late on the thread...just catching up...
Rich
p.s. I know I didn't need to do the subquery. I did it that way for
clarity. :)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Tuesday, December 19, 2006 2:52 PM
To: Allen, Brandon
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: db_file_multiblock_read_count causing full scans to
takelonger?
Brandon
> Am I missing something, or isn't it best to just leave this parameter
> unset (default) in 10.2, in which case Oracle will automatically
adjust
> it to the max io size of your OS?
Mhmm... I don't like that for the simplest reason that the maximum is
not the best value for performance! As the OP noticed...
> Along with computing system stats on a typical workload so you have an
> accurate mbrc, you should get the best of both worlds - accurate
costing
> and optimized sequential scans, right?
In some situations, as I just wrote, no.
> I have it unset on my 10.2 AIX 5.3 system and it has defaulted to 128
> with an 8k block size (1MB).
Notice that there are some limits... According to my tests the value of
db_file_multiblock_read_count is calculated with the following formula:
min( DbCacheSize/(Sessions*DbBlockSize) , 1048576/DbBlockSize )
Regards,
Chris
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
