RE: db_file_multiblock_read_count causing full scans to takelonger?

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_at_OneNeck.com] Sent: Wednesday, December 20, 2006 3:14 PM To: Jesse, Rich; oracle-l_at_freelists.org
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.

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