Before going to proceed it will be better if you have an idea about DB_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE. In my post B_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE I tried to give an idea.
The default database data block buffer size is specified by the parameter DB_BLOCK_SIZE. It is common to ask what will be the data block size of my oracle database? Should I increase or decrease data block size whenever there is performance issue of my database. There is common scenario that with one blocksize a query takes 30 minutes and with another query of a tablespace a query takes 2 or 3 minutes. So choosing an optimal data block size is very necessary task.
The general rule while choosing an optimal data block size is,
•For OLTP (Online Transaction Processing) systems use smaller block sizes. I saw Default or 8K block size does fit well. However if you have enough memory for SGA you might think about 16K blocksize.
•For DSS (Decision Support Systems) systems use larger block sizes.
This is because a larger block size in a heavy duty OLTP system with lots and lots of scattered reads all over the places which might be bad as no one is going after the same blocks i.e I fetched a block in the memory and no one else needs that block- the data is cached but no one using that data.
A larger block size in a query intensive system that is in DSS system it may be very good as I was going to read the blocks right around my data anyway. And - people are going to be needing that data again and again.
Whatever the size of the data, the goal is to minimize the number of reads required to retrieve the desired data.
•If the rows are small and access to the rows are random , then choose a smaller block size.
•If the rows are small and access is sequential, then choose a larger block size.
•If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.
•If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.
In my next topics I demonstrated on advantage and disadvantage of settings different types of blocks.
No comments:
Post a Comment