Re: Optimizer and block size changes = big problem.

Well, I've kicked this one about a bit, and can't come up with anything that convinces me.

However, by halving the block size, you will have affected the index stats to the effect that typically:

    average data blocks per key will have doubled     average index blocks per key will have doubled     clustering factor may have increased

If tablescans are then costed according to Oracle's perception of the O/S max i/o size this might be enough to push Oracle to infer that index paths are now twice as costly as they used to be.

Sample test -

    create a large table with big extents     set multiblock read count to

        4, 8, 16, 32 in turn
    explain plan for

        select count(*) from table
    and check the variation in COST
    with size of multiblock read count

I wonder how much impact this will have on the

    'what is the best size of a data block' argument ?

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Nuno Souto wrote in message <3af916d1.4989094_at_news-server>...


>Another spiky one.  Situation is this:

>

>V8.0.6.0, HP/UX 11, Peoplesoft Financials 7.52(heck, does it sound

>like I'm involved with this mob? <g>)

>

>Block size in DB was 16K.  Totally inappropriate for the type of

>database this is, with huge memory use and horrendous rollback segment

>size overhead and performance hit.  DB_FILE_MULTIBLOCK_READS at 8.

>Optimizer mode was CHOOSE, all tables/indexes analyzed. Yeah I know,

>this is the same as ALL_ROWS.  Stay with me, it's not that simple!

>

>But the explain plans of critical join SQL was OK: using NL because

>the result set was small, as opposed to using hash joins.  This was

>surprising, given the well known penchant of the optimizer to use hash

>joins all over the place when ALL_ROWS is active.  But I let it go at

>that.

>