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@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23850820/viewspace-1035519/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23850820/viewspace-1035519/