oracle9i之前，数据库的blocksize是固定的，一旦数据库创建之后就不可改变，也不能创建不同blocksize的表空间.

9i之后，可以对非系统表空间设置不同db_block_size的值.那么blocksize不同会对SQL造成什么影响呢?

我简单做了一个实验进行对比(这个实验还会再继续深入分析下).

数据库版本10.2.0.3，系统默认的db_block_size为8K,创建一个新的表空间设置blocksize为16K

SQL> alter system set db_16k_cache_size=4M scope=memory;

系统已更改。

SQL> create tablespace test16 datafile 'c:datafile_test16.dbf' size 100M blocksize 16k;

表空间已创建。

SQL> select file_name,bytes/blocks from dba_data_files;

FILE_NAME BYTES/BLOCKS
--------------------------------------------------- ------------
C:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 8192
C:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 8192
C:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 8192
C:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 8192
C:DATAFILE_TEST16.DBF 16384

然后在USERS和TEST16表空间上创建同样的表

SQL> create table test1 tablespace users as (select * from all_objects);

表已创建。

SQL> insert into test1 (select * from test1);

已创建49435行。

SQL> insert into test1 (select * from test1);

已创建98870行。

SQL> insert into test1 (select * from test1);

已创建197740行。

SQL> insert into test1 (select * from test1);

已创建395480行。

SQL> commit;

提交完成。

SQL> create table test2 tablespace test16 as (select * from test1);

表已创建。

SQL> select count(1) from test1;

COUNT(1)
----------
790960

SQL> select count(1) from test2;

COUNT(1)
----------
790960

SQL> select table_name,tablespace_name from dba_tables where table_name like'TEST_';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST1 USERS
TEST2 TEST16

对两个表进行全表扫描并查看执行计划和统计信息：

SQL> select * from test1;

已选择790960行。

执行计划
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 719K| 87M| 2502 (3)| 00:00:31 |
| 1 | TABLE ACCESS FULL| TEST1 | 719K| 87M| 2502 (3)| 00:00:31 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
63007 consistent gets
0 physical reads
0 redo size
39126273 bytes sent via SQL*Net to client
580430 bytes received via SQL*Net from client
52732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
790960 rows processed

SQL>
SQL>
SQL> select * from test2;

已选择790960行。

执行计划
----------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 937K| 114M| 1548 (5)| 00:00:19 |
| 1 | TABLE ACCESS FULL| TEST2 | 937K| 114M| 1548 (5)| 00:00:19 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
267 recursive calls
0 db block gets
57820 consistent gets
5312 physical reads
0 redo size
39126273 bytes sent via SQL*Net to client
580430 bytes received via SQL*Net from client
52732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
790960 rows processed

结果可以看出，对于在blocksize=16K的表空间上，查询时相对blocksize=8k表空间上的表，有较少的consistent gets，但是存在 5312的physical reads.不知道是什么原因，有达人分析一下.

[@more@]