对于OLAP系统为何DB_BLOCK_SIZE越大越好?

jayli426

Yong Huang 发表于 2013-2-17 13:24
To solearn: You're correct.

To jayli426:

But a bigger db_block_size in Oracle saves the number of requests issued by Oracle?
why?

意思就是更大SIZE容纳的实际业务数据要多于更小SIZE的数据块的业务数据
即一个SIZE为32K数据块要比4个SIZE为8K的数据块要容纳更多的业务数据？

Yong Huang

> a bigger db_block_size in Oracle saves the number of requests issued by Oracle

I should say "saves in". Sorry. Here, I just want to say a bigger db_block_size allows fewer I/O requests than a smaller block size does if the same amount of data needs to be read.

wolfop

其实没必要，对于DW打开那个multblock read就可以了。非8K的block size遇到BUG机会更大。而且ORACLE没有DB2那种block/page size小了就放不了太宽的表的限制。

gszoracle

jayli426 发表于 2011-10-10 13:16
“
个人认为Oracle的IO处理最小单位是block，不论是物理IO还是逻辑IO。

就按您的理解，100条记录分散在100个块里的概率，32K的比8K的要小的多。
即使在存储层面IO次数是一样的，但在oracle层面，32K的数据库比8K的数据库调用多块读的次数要少

gszoracle

jayli426 发表于 2011-10-10 13:16
“
个人认为Oracle的IO处理最小单位是block，不论是物理IO还是逻辑IO。

还有索引，大块显然比小块要好的多

jayli426

gszoracle 发表于 2013-2-24 14:17
还有索引，大块显然比小块要好的多

这是个神马意思？

gszoracle

jayli426 发表于 2013-2-24 19:32
这是个神马意思？

大块可以比小块装更多的索引项，索引是有序的，不会像数据数据记录那样这块放一条，那块放一条。oracle可以减少IO，如果压缩，压缩率也更高

sqysl

os block<=8k的情况，db_block_size=8k和32k感觉没太大差别，差别就是32k在DB层面少了些IO调用，在CPU资源充足的情况下，这个一般不会是问题，实际测试中，32K也没发现比8K优越多少；在os block>8K的情况，db_block_size=32k优势可能更明显，但前提是OLAP系统，否则，会浪费掉很多IO资源，反而会导致系统性能的下降。

Yong Huang

> os block<=8k的情况，db_block_size=8k和32k感觉没太大差别

I saved URLs for two old performance tests comparing different db_block_size's.

http://www.mofeel.net/254-comp-d ... r/47458a555a-2.aspx
"the db_block_size was set to 4K (was 2k) as a first try.  All batch runs immediately sliced the runtime into half"

http://www.linuxjournal.com/article/5840#N0x850ca00.0x85d3a20
My words in summarizing the above test:
Bert Scalzo investigated the effect of db_block_size on performance running Oracle on Linux. It's understandable that going from 2k to 4k increased performance by 138%. But it's surprising that changing db_block_size from 4k to 8k (the maximum supported on Linux) reduced data load time by almost 32%. Since Linux ext2 filesystem has a 4k block size, this benchmark seems to contradict the common belief that performance is the best when db_block_size matches file system I/O size; instead doubling it may improve performance.