I am designing a database, my DB is going to be used in Banking purpose, typical Banking applications like FOREX,LD,LC,CREDIT CARDS will be used in the DB.
What could be best size of DB_BLOCK_SIZE ?
Thanks in Advance,
04-19-2002, 06:54 AM
8k should be fine.
04-19-2002, 07:51 AM
Vijay is absolutely correct... I am working in Banking DB.. Our setup is with also 8KB. Banking is really with OLTP..
04-19-2002, 12:54 PM
32 K is the right choice.
04-19-2002, 01:02 PM
wont 32K waste you data block buffer too much?
04-19-2002, 01:09 PM
It doesn't really matter in 9i. You can have multiple block sizes.
04-21-2002, 04:57 PM
I think that 32k is too much for an OLTP system. The reason for this is that you would waste space in the buffer cache. For random access to data, like in an OLP system, I would recommand a smaller block size like 8k.
It's correct that you can have multiple block sizes in Oracle9i but the block size for the keep buffer pool and recycle buffer pool can only be defined for the default block size.
04-21-2002, 05:26 PM
You can find pros and cons for everything. You mentioned wasting space in buffer cache with 32K block size. But at the same time we are talking about banking application where speed is of the primary concern. Well, I don't think any bank will have a problem investing few hundred or thousand $ in buying enough RAM if it realy wants fast responces from its database. And 32K blocks realy can improve performance compared to 8K because of much less I/O. Data is much more tightly packed in table blocks, and what is even more important - indexes will be much "flatter" with 32K, resulting in saving I/O with each and every query in OLTP system.
Bottom line: I don't find "wasting space in buffer cache" to be a realy valid argument against using larger block sizes. Simply invest few bucks and have larger buffer cache, and the problem is gone. But reducing I/O is a valid argument in favor of using larger block size. Larger blocks simply mean fewer I/O, particulary in OLTP where practicaly everything is accessed via indexes.
04-21-2002, 05:43 PM
I do agree with jurij.Even oracle recommends a large block size be it an oltp or an olap database.
04-21-2002, 08:44 PM
in oltp small amount of rows are fetched, if let's sayin a query has to returns 10 rows in 10 blocks then instead of 80K Oracle have to read 320K into memory
Has anyone benchmark this? Or this just theory you peeps are talking about?