Printable View
Hi All,
DB: Oracle9i on IBM-AIX 4.3.
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,
gandhi
OCP-DBA.
Hi,
8k should be fine.
Vijay.
Hi
Vijay is absolutely correct... I am working in Banking DB.. Our setup is with also 8KB. Banking is really with OLTP..
Regards
32 K is the right choice.
wont 32K waste you data block buffer too much?
It doesn't really matter in 9i. You can have multiple block sizes.
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.
I do agree with jurij.Even oracle recommends a large block size be it an oltp or an olap database.
not agreed
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?
Jurij, if I recreate my DBs (in an investment bank) with block size of 32K, I will have so much false pinging that speed will slow down really a lot.Quote:
Originally posted by jmodic
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.
My opinion: 32K is for DSSs, not for banking systems.
Sure, Julian, but this is only true in OPS/RAC environment.
I think we are talking about single instance environment here. However it si good you brought this up, because in OPS this definitely is one of the most important factors when deciding the database block size (of course if applications are not/can't be partitioned in a great extent). But for single instance databases I still don't see any realy good reason why OLTP should not have big block size.
You are right. One more thing: there are banks and banks and some banks have much more data than others. If the bank's DB is really a megabase, then I would also go for 32K. And as you (or someone else) mentioned: you should have enough RAM for 32K blozk size.Quote:
Originally posted by jmodic
Sure, Julian, but this is only true in OPS/RAC environment.
I think we are talking about single instance environment here. However it si good you brought this up, because in OPS this definitely is one of the most important factors when deciding the database block size (of course if applications are not/can't be partitioned in a great extent). But for single instance databases I still don't see any realy good reason why OLTP should not have big block size.
yes , anybody has answers for this question ??????Quote:
Originally posted by Sweetie
not agreed
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?
It is not so simple. You could have some loss in performance if the page is too big. There is no universal solution just to make a 32K block simply because is "a regular banking database/application"... :D What is a "regular" anything in this domain???
So, let's get real! Nobody but you could take a corect decission! But we could recomend a way. So, start with 8K and test. You could be surprised to see the best results you will obtain with, let's say, 16K, and nobody here recommended you such a thing!!! ... :D
Posted by Sweetie
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?
This is not correct.
Take an example
8K BLOCK SIZE:
A table has 200 rows. Its block size is 8k. Assume that 20 rows can fit into one block. So, the total number of blocks needed is 10 blocks.
The total memory needed to bring the data is 10*8 = 80K.
32K BLOCK SIZE:
With 32K block size, the same table needs only 3 blocks, because each block stores 80 rows.
The total memory needed to bring the data is 3*32 = 96K.
I agree there is a small wastage in the memory if the block size is set to 32K, this wastage can be offset with the real benefits reap out in the disk space management. However, Oracle SGA is designed to keep the buffer cache as long as possible, until it ages out based on the LRU algorithm.
One can argue that what if the 10 rows come from 10 different blocks of 32K size, then there is a more memory wastage than that of memory wastage with 8K size?
It is very difficult to answer those type of question.
In my experience, I have not seen any true OLTP system in the world. Even if you say OLTP, many real time reports are running against the OLTP system only. OLTP performance takes dive when the real time reports are running. So it is always a hybrid system.
That is why I recommend 32K block size.
8K BLOCK SIZE:
A table has 200 rows. Its block size is 8k. Assume that 20 rows can fit into one block. So, the total number of blocks needed is 10 blocks.
The total memory needed to bring the data is 10*8 = 80K.
That would be a perfect world if you asume your query is going to fetch rows from a single block which is almost never true, so this is not an argument it is just a fact. Plus in OLTP since all reads are almost indexed we are even reading more blocks into memory!
It is correct that many OLTP systems are hybrid but that cannot justify that a big block size is good, enough big is enough
Plus even we are making the block big and we say that minimize the I/O, but hey we are telling ourselves that I/O is minimized at Oracle level, is this true at OS level? Are we minimizing at OS level? If not what is the use of minimizing at Oracle level. At end of the day Oracle runs on an OS and it is limited by OS limitations.
Assuming most UNIX/NT I/O are 64K I do agree that at some point I/O is minimized because we make Oracle to do less work by just reading 2 blocks if we have a block size of 32K and reading 8 blocks if block size were 8K, but can anyone tell me what is the difference between system calls of 2 oracle data block reads and 8 oracle data block reads? I would try it but I am sorry because I dont have any 32K block sizes databases, since the moderators are recommending this size they mush have vast experience with these 32k block sizes database and they can probably give it a shot and try it then post some benchmark results?
I think we should run strace at OS level to benchmark the system calls with different block sizes. And SAR the system I/O. Dont you think so?
I agree most of your points. I will post my bench mark results very soon. Trust me.
Wrong, we are reading less blocks into memory, hence drectly less I/O! With index reads you are typicaly spending 3-4 I/O operation just to traverse index from root to leaf node with 4K or 8K block size (on typicaly large OLTP table). The same index on 32K block size will tipicaly be one level flattener, thus requiring one I/O operation less. With each typical I/O query you are saving one index block read per index read so if there are two indexes involved you'll typicaly save two I/Os per typical OLTP query. And this is direct I/O saving, no matter if you look at it from Oracle's perspective or from OS's. If it's logical read you are saving a lot, if it is physical read you are saving even much more.Quote:
Originally posted by Sweetie
That would be a perfect world if you asume your query is going to fetch rows from a single block which is almost never true, so this is not an argument it is just a fact. Plus in OLTP since all reads are almost indexed we are even reading more blocks into memory!
This is totaly wrong assumption for OLTP systems. Oracle can take advantage of 64K OS I/O "bandwidth" only when performing multiblock reads (DB_FILE_MULTIBLOCK_READ_COUNT), which you'll very rearly see in an OLTP system (I guess you don't have many full table scans or fast full index scans in OLTP). Typical OLTP query is executed by INDEX RANGE SCAN followed by TABLE ACCESS BY ROWID. Both those operations are allways performed one oracle block per I/O.Quote:
Assuming most UNIX/NT I/O are 64K I do agree that at some point I/O is minimized because we make Oracle to do less work by just reading 2 blocks if we have a block size of 32K and reading 8 blocks if block size were 8K .... SNIP....
TJI:
Consider the multiblock read count and your physical i/o buffers.
Chances are you are going to read in 64/128/256K (or more) chunks into the physical buffers before they get passed to Oracle. The block size and multi-block read count are going to be the factors. You can do 8 blocks of 32K or 32 blocks of 8k or 16 blocks of 16k or whatever. The difference will be extremely small (I won't say non-existant) if your drive controller does 256K at a time.
Then consider the SGA buffer pool. If it is large enough (key ri tables pinned) physical io is less of an issue.
In the old days, the block size helped determine the number of data files and the database size. It also made a difference with the old, 'small', controllers and drives when RAM was expensive.
RAM is cheap. Blow out your SGA.
Hi Friends,
Thanks for the lot of input. Banking is a OLTP application and mydatabase is going to be 9i, will it make any differences in the blocksize.
Since, 9i supports Multiple Block Sizes.
Thanks in Advance
gandhi
Quote:
Originally posted by marist89
It doesn't really matter in 9i. You can have multiple block sizes.
Marist, do you recomend using multiple block size?
Maybe the risk of bad tunning is tempering me, but I'm not so happy about multiple page size...
When used appropriately, multiple block sizes are an excellent solution to a mixed use database.