Re: db_file_multiblock_read_count and 10g

From: Greg Rahn <greg_at_structureddata.org>
Date: Sun, 25 Jan 2009 13:15:18 -0800
Message-ID: <a9c093440901251315k7638f013tab322f45ae584193_at_mail.gmail.com>

On Sun, Jan 25, 2009 at 1:15 AM, Nuno Souto <dbvision_at_iinet.net.au> wrote:
> I'm still not sure that is the case. The specific parameter doco says that
> if not specified, it is set to a default value. It might be "blahblah" for
> others but it isn't of necessity auto-tuned.

It may be a poor choice of wording in the docs. I would personally call it auto-chosen vs auto-tuned.

> *IF* there are system stats and *IF* the param is not specified, it might
> indeed be auto-set. But as Syed Jaffar Hussein pointed out and I have also
> observed a number of times, it is anything but "tuned". In fact in many
> cases of auto-setting, it is just plain wrong.

In cases were db_file_multiblock_read_count is unset what are the values for _db_file_exec_read_count
_db_file_optimizer_read_count

IIRC _db_file_exec_read_count=8 and
_db_file_optimizer_read_count=((max I/O size)/DB_BLOCK_SIZE)), so usually 1MB I/O size.

> So far, I've been getting same or better performance
> setting it to the SAN's stripe block size rather than letting it set itself.
> 10.2.0.3 here.

I'm assuming I/O efficiencies (elapsed times), but are the execution plans the same?

What performance differences do you see in a 64KB net MBRC (your strip size) setting vs a 1MB MBRC I/O size?

I've some seen cases on some SANs where smaller I/O sizes actually yield better overall I/O times. This has been attributed to I/O prefetch algorithms on the SAN But on the other hand I've seen cases where it doesn't matter either way.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 25 2009 - 15:15:18 CST