Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Won't use INLIST ITERATOR in simple case
Jonathan Lewis wrote:
\
> You probably mean optimizer_index_cost_adj,
>
> It isn't a parameter for "index vs. tablescan", it's
> a parameter that affects the relatively costing of
> a single block read taking place through an indexed
> access path with the cost of a multi-block read.
-- -- Thanks, I knew it had a long winded definition!Received on Wed Nov 08 2006 - 17:15:55 CST
>
> Since you're on 9.2, you should be working out
> how to use system statistics (CPU costing) which
> allows Oracle to use a timing strategy to compare
> ALL single block reads with multiblock reads,
> rather than relying on a hack that was introduced
> in Oracle 8.
-- Sounds like a difficult subject. With my application it seems once I have tables over a few thousand rows, almost any tablespace scan is BAD. A poor use of indexes is one or two orders of magnitude faster. I still need to double check what the default setting for ptimizer_index_cost_adj on the Oracle instance(s) that I'm using for ptimizer_index_cost_adj. I really wish there was an option to define certain tables as "DO_NOT_TABLESPACE_SCAN" and raise an error if an attempt is made. It would be a huge help in my development process. I'll order your book, it is obvious I need some more education on Oracle performance.
>
> See also:
> http://jonathanlewis.wordpress.com/2006/10/24/optimizer_index_cost_adj/
>
>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html