Oracle中关于8k表空间与16k表空间IO性能对比

Oracle中，当执行FTS时，会触发多块读。而在这个情况下，如果单个oracle块越大装载的数据就越多。

由此，对比一下8k与16k的性能差别。

--设置db_16k_cache_size

alter system set db_16k_cache_size=100M;

--创建16k表空间

create tablespace tb1 datafile'/u01/app/oracle/oradata/hxydb2/tb01.dbf' size 1g blocksize 16k;

--创建新用户使其默认表空间为16k表空间

create user test16 identified by test16 default tablespace  tb1;

grant sysdba to test16;

grant create session to test16;

grant resource to test16;

--创建测试表，收集统计信息

create table dba_object as select * from sys.dba_objects;

declare

begin

  dbms_stats.gather_table_stats(user,tabname =>'dba_object' );

  end;

--查看16K下全表扫描的执行计划

select * from dba_object;

--查看8k下全表扫描的执行计划

select * from dba_object;

可以明显发现在16k环境下，全表扫描的io cost明显下降了。

在数仓环境，有很多需要FTS的大表，是否可以考虑将其放入到16k表空间中，降低系统的IO负载。

不同的块大小的优点和缺点

Block Size Advantages andDisadvantages

Block Size	Advantages	Disadvantages
Smaller	Good for small rows with lots of random access. Reduces block contention.	Has a relatively large space overhead due to metadata (that is, block header). Not recommended for large rows. There might only be a few rows stored for each block, or worse, row chaining if a single row does not fit into a block,
Larger	Has a lower overhead, so there is more room to store data. Permits reading several rows into the buffer cache with a singleI/O (depending on row size and block size). Good for sequential access or very large rows (such as LOBdata).	Wastes space in the buffer cache, if you are doing random access to small rows and have a large block size. For example, with an 8KB block size and 50-byte row size, you waste 7,950 bytes in the buffer cache when doing random access. Not good for index blocks used in an OLTP environment, because they increase block contention on the index leaf blocks.