ITPub博客

首页 > 数据库 > Oracle > Tablespace and Table 的存储属性设置的实验与理解

Tablespace and Table 的存储属性设置的实验与理解

原创 Oracle 作者:nathanzhn 时间:2014-04-09 15:38:33 0 删除 编辑
实验目的:为了进一步搞清楚表空间、表的存储参数设定以及限制等,并观察空间分配的结果,观察DATA BLOCKS的数量和类型

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--create some types of tablespaces below and watch what would happen to initial and extend the storage space;
-- 1. totally created as default setup by Oracle
SYS@PROD>create tablespace test1 datafile '/s01/app/oracle/oradata/PROD/disk1/test1.dbf' size 10M;
Tablespace created.
-- 2. mssm & extent allocate
SYS@PROD>create tablespace test2 datafile '/s01/app/oracle/oradata/PROD/disk1/test2.dbf' size 10M autoextend on next 2M
  2 extent management local
  3 segment space management manual;
Tablespace created.
-- 3. assm & extent uniform
SYS@PROD>create tablespace test3 datafile '/s01/app/oracle/oradata/PROD/disk1/test3.dbf' size 10M autoextend on next 2M
  2 extent management local uniform size 512k
  3 segment space management auto;
Tablespace created.

ZN@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name in ('TEST1','TEST2','TEST3');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST1 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM AUTO
TEST2 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM MANUAL
TEST3 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
ZN@PROD>show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

可以猜想,当向表test1、test2、test3分别插入一条记录时,每个表的segment均申请one extent的空间,其中test1、test2分配了8 blocks=65536(64k),下面测试一下test3是不是会分配64 blocks=524288(512k)呢?
ZN@PROD>create table test1(X INT) tablespace test1;
Table created.
ZN@PROD>create table test2(X INT) tablespace test2;
Table created.
ZN@PROD>create table test3(X INT) tablespace test3;
Table created. 
ZN@PROD>insert into test1 values(1);
1 row created.
ZN@PROD>insert into test2 values(2);
1 row created.
ZN@PROD>insert into test3 values(3);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('TEST1','TEST2','TEST3');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST3 TEST3 10 524288 524288 1 2147483645
TEST2 TEST2 10 65536 1048576 1 2147483645
TEST1 TEST1 10 65536 1048576 1 2147483645
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST1');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST2');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST3');
PL/SQL procedure successfully completed.

ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('TEST1','TEST2','TEST3');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST3 TEST3 10 524288 524288 1 2147483645 26 0
TEST2 TEST2 10 65536 1048576 1 2147483645 1 0
TEST1 TEST1 10 65536 1048576 1 2147483645 5 0

-- 从上面的查询看到,TEST1初始分配了5个DATA BLOCKS,与之前的实验结果吻合,TEST2初始分配了1个DATA BLOCK,也与之前的实验结果吻合。

ZN@PROD>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME BUFFERS LEVEL 1';
Session altered.
ZN@PROD>SELECT * FROM V$DIAG_INFO where name ='Default Trace File';
   INST_ID NAME VALUE
---------- ------------------ ------------------------------------------------------------
         1 Default Trace File /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_3108.trc
SYS@PROD>select obj#,owner#,name from obj$ where name in ('TEST1','TEST2','TEST3');
      OBJ# OWNER# NAME
---------- ---------- ------------------
     13594 32 TEST1
     13595 32 TEST2
     13596 32 TEST3
SYS@PROD>select segment_name,header_block,header_file,blocks from dba_segments where segment_name='TEST2';
SEGME HEADER_BLOCK HEADER_FILE BLOCKS
----- ------------ ----------- ----------
TEST2 128 7 8
SYS@PROD>select segment_name,header_block,header_file,blocks from dba_segments where segment_name='TEST1';
SEGME HEADER_BLOCK HEADER_FILE BLOCKS
----- ------------ ----------- ----------
TEST1 130 6 8
--下面重点看一下TEST3 ,看看DUMP出来的BH数量中,类型为DATA BLOCK的是否有26个
SYS@PROD>select segment_name,header_block,header_file,blocks from dba_segments where segment_name='TEST3';
SEGME HEADER_BLOCK HEADER_FILE BLOCKS
----- ------------ ----------- ----------
TEST3 133 8 64
vi trace file
find: obj: 13596-TABLE TEST3:
class=8: 4
class=9: 1
class=4: 1
BH (0x757d91e8) file#: 8 rdba: 0x02000080 (8/128) class: 8 ba: 0x75420000
BH (0x757e23f8) file#: 8 rdba: 0x02000081 (8/129) class: 8 ba: 0x75516000
BH (0x757ebf88) file#: 8 rdba: 0x02000082 (8/130) class: 8 ba: 0x7561c000
BH (0x757f6368) file#: 8 rdba: 0x02000083 (8/131) class: 8 ba: 0x75730000
BH (0x757f6498) file#: 8 rdba: 0x02000084 (8/132) class: 9 ba: 0x75732000
BH (0x757ec0b8) file#: 8 rdba: 0x02000085 (8/133) class: 4 ba: 0x7561e000
 
class=1: 16
BH (0x757e2198) file#: 8 rdba: 0x02000090 (8/144) class: 1 ba: 0x75512000
BH (0x757ebd28) file#: 8 rdba: 0x02000091 (8/145) class: 1 ba: 0x75618000
BH (0x757f6238) file#: 8 rdba: 0x02000092 (8/146) class: 1 ba: 0x7572e000
BH (0x757d8f88) file#: 8 rdba: 0x02000093 (8/147) class: 1 ba: 0x7541c000
BH (0x757e2068) file#: 8 rdba: 0x02000094 (8/148) class: 1 ba: 0x75510000
BH (0x757ebbf8) file#: 8 rdba: 0x02000095 (8/149) class: 1 ba: 0x75616000
BH (0x757f6108) file#: 8 rdba: 0x02000096 (8/150) class: 1 ba: 0x7572c000
BH (0x757d8e58) file#: 8 rdba: 0x02000097 (8/151) class: 1 ba: 0x7541a000
BH (0x757e1f38) file#: 8 rdba: 0x02000098 (8/152) class: 1 ba: 0x7550e000
BH (0x757ebac8) file#: 8 rdba: 0x02000099 (8/153) class: 1 ba: 0x75614000
BH (0x757f5fd8) file#: 8 rdba: 0x0200009a (8/154) class: 1 ba: 0x7572a000
BH (0x757d8d28) file#: 8 rdba: 0x0200009b (8/155) class: 1 ba: 0x75418000
BH (0x757e1e08) file#: 8 rdba: 0x0200009c (8/156) class: 1 ba: 0x7550c000
BH (0x757eb998) file#: 8 rdba: 0x0200009d (8/157) class: 1 ba: 0x75612000
BH (0x757f5ea8) file#: 8 rdba: 0x0200009e (8/158) class: 1 ba: 0x75728000
BH (0x757d8bf8) file#: 8 rdba: 0x0200009f (8/159) class: 1 ba: 0x75416000
-- TEST3的数据块一共找到22个,其中DATA BLOCK有16个,比查询出来的26个还少了10个,为什么? ?

-- 接下来看一下8号文件,即TEST3表空间,还分配了哪些块
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST3 TEST3 10 524288 524288 1 2147483645 26 0
还发现其他的8号文件的块:
-- class=13:'file header block'
BH (0x757d9318) file#: 8 rdba: 0x02000002 (8/2) class: 13 ba: 0x75422000
-- class=12:'bitmap index block'
BH (0x757e2528) file#: 8 rdba: 0x02000003 (8/3) class: 12 ba: 0x75518000


-- 下面测试了其他几种指定表空间、表的STORAGE属性的写法,看看对表空间、表的创建有什么影响。

SYS@PROD>create tablespace test4 datafile '/s01/app/oracle/oradata/PROD/disk1/test4.dbf' size 10M  
  2 default storage(initial 1024k next 512k minextents 2);
Tablespace created.
SYS@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name in ('TEST4');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST4 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM AUTO
SYS@PROD>create tablespace test5 datafile '/s01/app/oracle/oradata/PROD/disk1/test5.dbf' size 10M
  2 default storage(initial 2048k next 2048k minextents 5);
Tablespace created.
SYS@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name in ('TEST5');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST5 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM AUTO
SYS@PROD>create tablespace test6 datafile '/s01/app/oracle/oradata/PROD/disk1/test6.dbf' size 10M uniform size 2M;
Tablespace created.
SYS@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name in ('TEST6');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST6 8192 2097152 2097152 1 2147483645 2147483645 LOCAL UNIFORM AUTO

--可以看到上面用STORAGE指定INITIAL的表空间TEST4,TEST5均没生效,依旧使用的是默认的64k作为INITIAL_EXTENT参数,且NEXT_INITIAL为空。只有表空间TEST6使用了UNIFORM方式,才看到INITIAL_EXTENT, NEXT_INITIAL等于了指定的2M。

ZN@PROD>create table test4(x int) tablespace test4;
Table created.

ZN@PROD>create table test5(x int) tablespace test5;
Table created.

ZN@PROD>create table test6(x int) tablespace test6;
Table created.

ZN@PROD>insert into test4 values(4);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>insert into test5 values(5);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>insert into test6 values(6);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST4');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST5');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST6');
PL/SQL procedure successfully completed.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('TEST4','TEST5','TEST6');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST6 TEST6 10 2097152 2097152 1 2147483645 58 0
TEST5 TEST5 10 65536 1048576 1 2147483645 5 0
TEST4 TEST4 10 65536 1048576 1 2147483645 5 0
ZN@PROD>create table t66 (x int) tablespace test6 storage(initial 32k next 32k minextents 2);
Table created.
ZN@PROD>insert into t66 values(66);
1 row created.
ZN@PROD>commit;
Commit complete.

ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T66');
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('T66');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
T66 TEST6 10 65536 32768 1 2147483645 58 0
-- 其实我们发现,上面所创建的一系列表空间和表,除了TEST6表的BLOCKS数量=58外,其他属性都还基本可以理解。从表T66可以看出,建表语句中指定的STORAGE会覆盖所在表空间的STORAGE属性。INITIAL_EXTENT的值等于STORAGE中指定的INITIAL*MINEXTENTS。

至于TEST6表的BLOCKS数量为什么=58,和之前的TEST4的26又有不同,可能与INITIAL参数的指定有关。
下面还可以继续做一个实验,修改表的INITIAL参数,看看BLOCKS有什么变化:

ZN@PROD>create table t666 (x int) tablespace test6 storage(initial 64k next 64k minextents 2);
Table created.

ZN@PROD>insert into t666 values(666);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T666');
PL/SQL procedure successfully completed.

ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('T666');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
T666 TEST6 10 131072 65536 1 2147483645 58 0

-- 发现BLOCKS还是等于58. 说明BLOCS数量与表的STORAGE属性无关,与表空间的分配属性有关。

-- 再建两个表空间,一个的unifor size=512k,一个=1M,和表空间TEST3, TEST6做一个横向比较:

ZN@PROD>create tablespace test7 datafile '/s01/app/oracle/oradata/PROD/disk1/test7.dbf' size 10M uniform size 1M;
Tablespace created.
ZN@PROD>create tablespace test8 datafile '/s01/app/oracle/oradata/PROD/disk1/test8.dbf' size 10M uniform size 512k;
Tablespace created.

ZN@PROD>create table t777(X INT) tablespace test7;
Table created.
ZN@PROD>create table t888(X INT) tablespace test8;
Table created.
ZN@PROD>insert into t777 values(777);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>insert into t888 values(888);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T777');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T888');
PL/SQL procedure successfully completed.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('T777','T888','TEST3','T666');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
T666 TEST6 10 131072 65536 1 2147483645 58 0
T777 TEST7 10 1048576 1048576 1 2147483645 60 0
T888 TEST8 10 524288 524288 1 2147483645 26 0
TEST3 TEST3 10 524288 524288 1 2147483645 26 0
ZN@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name in ('TEST7','TEST8','TEST3');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST3 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST7 8192 1048576 1048576 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST8 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
ZN@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name in ('TEST6','TEST7','TEST8','TEST3');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST3 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST6 8192 2097152 2097152 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST7 8192 1048576 1048576 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST8 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO

-- 自己看吧

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26521853/viewspace-1138673/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    263443