Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: High mbrc in aux_stats$

Re: High mbrc in aux_stats$

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 10 Mar 2006 19:45:09 -0500
Message-Id: <1142037909l.4195l.0l@medo.noip.com>

On 03/10/2006 06:44:25 PM, Allen, Brandon wrote:
> Anybody seen this before?
>
>
> Notice the MBRC figure of 97 even though I have db_file_multiblock_read_count=16. I searched Metalink for bugs and known issues but no luck. I guess the next step is to peform a 10046 trace and check the p3 value for all the 'db file scattered reads', but I figured I'd check to see if any of you have already seen this before I go to that trouble.
>
> Thanks,
> Brandon

Brandon, this setting IS trouble. Oracle will use DB_MULTIBLOCK_READ_COUNT batches but CBO will set the price for full table scan as if it was possible to read 97 blocks at once. Price of full table scan will be very low, lower then the price of a second hand Kia at your local Kia sales event, which means that you will have a lot of them. CBO will not consider index path as a viable path, which is great if the only things you're running are reports from a DW or a data mart. If, on the other hand, the database in question is an OLTP database, then I, as a foreigner who speaks poor English, cannot describe the situation without using the f-word. I doubt that Steve Adams would have enough undersanding to allow me that liberty.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 10 2006 - 18:45:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US