Re: Oracle Tnuning---Block Size, How much difference?

> OR whether the change of block size is the problem making the new
> system slower.
>
> Any suggestions and advices are greatly appreciated.

Sean,

Blocks sizes can affect many things. Rows are stored in blocks, so if space requirement per-row is greater than the block size, it will have to chain blocks to fit a row. This is a performance hit.

In addition, if your data is frequently getting inserted/deleted, blocks need to be allocated/deallocated, which causes a performance hit. If you are inserting alot of data, blocks will fill faster, causing a performance hit. Blocks have two "volume" indicators - the max amount of data they can hold (like 80% full) and the minimum amount (like 10% full). The max amount of data is to allow updates to rows. Rows often have columns that allow variable amounts of data. Updating a row to increase the data increases the size of the row. So the max amount of data provides for this growth. The min amount is the minimum amount of data a block will hold before the block is de-allocated. The data from the de-allocated block gets put into another block.

Indexes are (usually) stored in b-trees, and each node is a block. If you are inserting data, blocks will fill up faster in the index, causing new blocks to be allocated, and the b-tree may need to be rebalanced.

All of this allocation/deallocation could be causing the slow-down.

David.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Apr 20 1999 - 15:13:30 CDT