Re: db block size, too big wasting buffer?

In article <998d28f7.0206301512.411700f9_at_posting.google.com>, don_at_burleson.cc says...
>
>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)!

Hate to contradict you on this one Don, but HJR already has so I might as well join in! ;)

Multiple block sizes were TOTALLY put in to support transporting tablespaces from a database on one block size to a database of another block size. I spoke to the developer who wrote the code, and they swear there was never any idea of doing it for performance reasons. Now, I (and many others too) thought this would be great from a performance perspective, but the testing that has been done both internally and by knowledgeable sorts of folk like Anjo Kolk has yet to show anything except very minor improvements by doing this.

Going back to the original reason the code was written. I've seen it mentioned in many places as the easiest way to move data from your OLTP system to a data warehouse, where these often have different block sizes (won't go into the arguments for and against this here as HJR has done this to death recently). I find this somewhat problematic, or at least a bit marketing speak, because you don't normally just MOVE data between the OLTP system and the data warehouse. You tend to SUMMARIZE it first, so where this really comes into its own to my way of thinking is moving stuff to a staging database BEFORE you move it to the data warehouse. I've seen plenty of sites that do that, so I guess it's worthwhile.
>
>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 . . . .

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Mon Jul 01 2002 - 11:37:42 CDT