Re: db block size, too big wasting buffer?

From: Don Burleson <don_at_burleson.cc>
Date: 30 Jun 2002 16:12:46 -0700
Message-ID: <998d28f7.0206301512.411700f9@posting.google.com>

Sure, blocksize matters a lot:

In a one-side-fits-all 8.1.7 install, you must choose the best overall blocksize based on several factors:

1 - The number of index accesses - Oracle index range scans love large blocksizes, and on my 9i systems, I place indexes in a 32k tablespace.

2 - The amount of random OLTP access - If you are randomly accessing 80-byte rows, it would be a huge waste of db_block_size to read-in unwanted row data with large blocksizes.

(BTW, this quandry was the main reason for the multi-blockdsize feature of 9i)!

On an 8.1.7 Data warehouse, the choice is between 8k and 16k. If your db is heavily indexes,, you might think about 16k. I use this query:

select sum(blocks) from dba_segments where segment_type = 'INDEX'; select sum(blocks) from dba_segments where segment_type = 'TABLE';

Hope this helps . . . . Received on Sun Jun 30 2002 - 18:12:46 CDT