Re: db block size, too big wasting buffer?

Alas, you CAN "waste data buffers" by changing block sizes. Fortunately, you probably have to be short on memory, have to have a special case application to do so, and then have to have snotty users who complain about minor fluctuations in performance.

To engineer a scenario, imagine you have a system where a large fraction of the activity is based on dense queries of recent data, and a smaller fraction is based on scattered queries of historic data.

All the recent data is focused in 1,000 blocks near one end of a large table, and recent queries keep hitting that 1,000 blocks. Historic queries pick up about 1,000 ROWS scattered widely across the rest of the table at about one row per block.

Your db_block_buffer is 1,000 blocks.

HIGHLY SIMPLIFIED thought experiment:
Every time you run a historic query, you tend to knock (a larger fraction of) the 1,000 blocks from the buffer that everyone else has been using for recent queries. Every historic query is slow, but more significantly causes a performance glitch for everyone else.

Change the db_block_size down to 25% of what it was: You now have 4,000 blocks buffered
Recent queries are focused on the top-end 4,000 blocks. Historic queries still hit only 1,000 blocks because the data scatter is still one row per block. A historic query now knocks out at most 25% of the buffered blocks that the recent queries Historic queries go no faster, but the impact on recent queries is significantly reduced.

NOTE - obviously I've stripped this description to a minimum to indicate the flavour of the problem.

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

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Nuno Souto wrote in message
<3d1818ae$0$28008$afc38c87_at_news.optusnet.com.au>...


>

>No. It is most definitely not true.  You cannot "waste" data buffer by

>changing block sizes.  The two things are not related.

>

>What you can have is a situation where you may be doing I/O in bigger

>"chunks" (buffers) than you really need.  That's all.

>

>But even then, you are not "wasting data buffers".  No way.