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.
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.
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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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?
Reply With Quote
Bookmarks