RE: db_file_multiblock_read_count causing full scans to takelonger?
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Dec 2006 15:26:56 -0600
Exactly the same. At least on this DB. On another DB in another LPAR,
the query (queries) return "128 128".
I'm comparing the init.ora parameters now. It's been awhile since I've
messed with DB_FILE_MULTIBLOCK_READ_COUNT, but I didn't think it was
dynamically changed during the life of the instance, just at instance
startup. And I can't find any evidence in the docs about that.
I was worried that the 128 value would lead to more FTSs than I'd care
for in this OLTP system, but according to
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/opti
mops.htm#sthref1228 :
DB_FILE_MULTIBLOCK_READ_COUNT
This parameter specifies the number of blocks that are read in a single
I/O during a full table scan or index fast full scan. The optimizer uses
the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and
index fast full scans. Larger values result in a cheaper cost for full
table scans and can result in the optimizer choosing a full table scan
over an index scan. If this parameter is not set explicitly (or is set
is 0), the optimizer will use a default value of 8 when costing full
table scans and index fast full scans.
..which I'm happy to see. And yes, this is on 10.2.0.2.0...
Rich
-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx]
Sent: Wednesday, December 20, 2006 3:14 PM
To: Jesse, Rich; oracle-l@xxxxxxxxxxxxx
Subject: RE: db_file_multiblock_read_count causing full scans to
takelonger?
Do you have sga_target set? If so, then db_cache_size is just a
minimum. What are the results if you run this instead:
SELECT LEAST(dcs/(ses*dbs), 1048576/dbs) "CALC", dfmbrc FROM (
SELECT 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'),
(SELECT bytes "DCS" from v$sgastat where name = 'buffer_cache');
Thanks,
Brandon
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do
not consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not relate to
the official business of this company shall be understood as neither
given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
