What are the Advantages and Disadvantages of SMALL BLOCK SIZE (4096) ?
What are the Advantages and Disadvantages of LARGE BLOCK SIZE (16384) ?
Printable View
What are the Advantages and Disadvantages of SMALL BLOCK SIZE (4096) ?
What are the Advantages and Disadvantages of LARGE BLOCK SIZE (16384) ?
Advantages of large block size:
1 Space wastage is very minimum.
2 Insert will be faster.
3 Fast Full table/index scan can be done in a less time.
Disadvantages of large block size:
1 Unwanted data/index will be cached in SGA.
Chances for row chaining is more in smaller block size than larger block size..
Thanigaivasan
You are also more likely to do a full table scan for small tables because the entire table might fit into one block. Which I suppose isn't entirely bad.
Which one block size 4096/8192/16384 is best option for 1GB physical Ram and 4 processors machine on oracle8i/oracle9i with windows 2000 Adv.Server
What KIND of database do you have, I mean: what kind of data are there, how many columns are usually in a table (aprox.), do you have large tables (no of rows etc.), how critical is your storage-space (do you can afford some wastage) ... ?
Obviously, the equilibrium is 4K, but your database is almost never "a typical one", no matter who you are... :)
At least tell us how large do you expect to be your database over 1 month, aver 6 months, and over 1 year. This could be the minimal condition to make a vague recomandation. Though not the most proffesional point of start... :D
Tamilselvan, I do NOT agree with all you did say there, you know?
Think taking some risks and imprecissions in tuning process! Think you could wrongly set PCTUSED and/or PCTFREE on a large tablespace with a large block size. Do you think it would be "minimum wastage of space" ? :D
hi,
If ur database is in OLTP environment, it is recommended to have small block size. Becoz the queries will extract only small amount of data and it is advisable to keep the data in small blocks. If u have a big block size, unnecessariy u will be reading unwanted data into the cache.
If u have a DSS environment(Data whearhousing), where ur database is query intensive, it is recommended to have big block size, so that oracle can read more amount of data into the cache in one shot.
And again if u r table contain objects of big size like graphics files, it is recommended to have a big block size
Keeping small blocks in this case would mean lot of I/O and CPU overheads.
As a DBA, it is very important to decide abt the block size when u create the database; since this directly influences the performance. Once u have created the database u cant change the size of the block unless u recreate the database.
Regards
Sonia
[Edited by sonia on 04-08-2002 at 12:37 AM]
Ya!! what sonia says is the perfect answer to decide the size of data block.
For OLTP - the small transactions are more in nos as compared to large blocks requested by queries. and for DSS system - no of transactions are less but the queries requesting the large data are more.
It is recommended that if you have a VLDB and a HYBRID system , then you should keep two databases on for OLTP ( Small data block size ) which is used for small transactions and other for DSS (Large data block size ) which is only for large requests, i mean only for reports used for MIS or so.
Even the rest of the answers are correct.
My database is an OLTP , how i set the other SGA parameteres including db_block_size ?
Tamilselvan was talking about space vastage because of a "fixed overhed" that each block has because of the block header! Block header (a part of a bock, used for its internal information, where you can not save your actual data) consumes much larger portion of space in 4K block compared to that in 16K block. Thats a wastage of space.Quote:
Originally posted by ovidius
Tamilselvan, I do NOT agree with all you did say there, you know?
Think taking some risks and imprecissions in tuning process! Think you could wrongly set PCTUSED and/or PCTFREE on a large tablespace with a large block size. Do you think it would be "minimum wastage of space" ? :D
And what you are talking about wrongly setting of PCTFREE/PCTUSED in large table with large block size is irrelevant. You will waste the same amount of space regardless of your block size with that! In fact, you will waste more of your "efficient" block space on smaller blocks if you set your PCTFREE/PCTUSED settings wrong....
Upgrade to 9i and use multiple block sizes in a single database:
http://www.oracle-base.com/Articles/...BlockSizes.asp
It's difficult to generalized but hybrid systems I've worked on seem to do pretty well on 8K.
Cheers
Yes, jmodic, you are right about pctfree/pctused on space wastage on wrong setting, based on the fact they are percentages.
But only theoretically. You are not taking acount of the histerezis on pctfree/pctused usage. This histerezis make the database behaviour more senzitive to some missusage than to others. Generally, a smaller pagesize gives the opportunity of a "finer" tunning of anything, including space wastage or something else. If you do not believe me, test it!
Generally, I do not recomend the usage of pagesize larger than 4K unless the database is an OLAP one. If is an OLAP databse, the fact is a little more complicated. Because you have to know exactly what you have there, and what you do there. Is not so simple to "play" with pagesizes between 8K and 32K. Thedifference between 8K and 32K is big. And there could be many situations in an OLAP environment. And we really do NOT know what is there, because the thread starter did not offered so much details. We cannot afford to give some empirical advices based on nothing. That was my point, in fact.
THNX!
Many OLTP systems take negative performance not because of large block size, but due to "READ AHEAD CAHCE' configured at disk controller level. As many of us know OLTP system needs a single block or two to update a row (two).
The disk controllers if configured with READ AHEAD CACHE option reads many blocks even though it was not asked to do, which are wasteful, because the next I/O request may not be the blocks that are in the disk controller cache.
Also, I have observed many times that OLTP system requires more index data in the SGA than table data. And Oracle always places the index data at the beginning of the cache (LRU LIST) so that they will not be aged out, where as the table data will be placed at the end of the LRU list where it is highly possible that may be aged out very soon.
So large data block size will definitely improve not only OLTP system but also DW system.
If your OS supports 32K block size, configure it to 32K.
Discard the very old myth, that OLTP needs only small block size.
That is not entirely true. Table block will be place at the end of the LRU list only when it is accessed by the full table scan. When it is accessed by the ROWID (as a result of the indexed access) it will be placed at the beginning of the LRU list (that is, at the MRU end of the list), the same way as the index blocks are. So in OLTP system table blocks are not tipicaly aged out of the cache soon after they are loaded, their behavior regarding LRU list is the same as for index blocks.Quote:
Originally posted by tamilselvan
Oracle always places the index data at the beginning of the cache (LRU LIST) so that they will not be aged out, where as the table data will be placed at the end of the LRU list where it is highly possible that may be aged out very soon.