Re: optimizer_index_cost_adj and optimizer_index

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 6 Mar 2004 07:18:14 -0000
Message-ID: <036001c4034b$324e7690$7002a8c0@Primary>

Note in line.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Joze Senegacnik" <JozeS_at_hermes-plus.si>

: According to the Metalink note you can even achieve 8.1.7 behavior when
you
: set MREADTIM = 1.2 * SREADTIM and MBRC=8, but I haven't tested this. And
: finally I haven't heard for a negative feedback till now and this counts
as
: well. But as usual happens in real life also the system stats is not
solving
: all problems.

:

My guess is that this was an observation taken from an experiment. It's close to true if the parameter db_file_multiblock_read_count that is used as the comparison was set to 8. The time factor has to change if the two MBRCs are set to a different value.

The old costing model would adjust 8 to 6.59 (working from memory here) before dividing into the table's number of blocks below HWM. Surprise, surprise: 6.59 * 1.2 = 7.9.
Why does it work - because, as the formula in the manual points out, Oracle effectively treats a multiblock read cost as

mreadtim/sreadtim * single block read cost.

There will be a little inaccuracy, though, because of the "plus 1" that oracle adds to tablescans in 9.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Mar 06 2004 - 01:29:09 CST

Re: optimizer_index_cost_adj and optimizer_index_caching