Re: optimizer_index_cost_adj and optimizer_index_caching

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@xxxxxxxxxxxxxx>

: 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: