11g 表空间extent预分配特性

表空间extent预分配特性介绍

11g里新增了表空间Extent预分配的特性，即根据之前某个表空间的空间使用情况，在下一次真正使用空间之前预先分配可用空间，提高DML语句的执行效率，前提是数据文件的autoextend=on且隐含参数_ENABLE_SPACE_PREALLOCATION=3

 

相关进程及参数：

SMCO(Space Management Coordinator)：这是一个后台进程主要负责空间的分配与回收，其下会派生出W nnn子进程来具体执行分配及回收的工作

 

_kttext_warning：这个隐含参数表示了将要自动扩展空间占整个表空间的百分比，_kttext_warning对扩展的大小起到了一定的指导作用，扩展的空间一般大于等于_kttext_warning值

 

_ENABLE_SPACE_PREALLOCATION：3：开启预分配功能；0：关闭预分配功能

 

当表空间里的可用空间小于_kttext_warning指定的百分比时smco会对表空间进行自动预分配，可以通过占用表空间及修改_kttext_warning参数两种方法来测试预分配的行为

 

1、表空间占满后观察表空间的预分配特性

--创建表空间，指定autoextend=on的datafile

drop tablespace autotbs1 including contents and datafiles;

create tablespace autotbs1 datafile '/oradata06/auto1' size 64m extent management local uniform size 4m;

alter database datafile '/oradata06/auto1' autoextend on;

col file_name format a50

set linesize 120

select file_name,file_id,autoextensible from dba_data_files where file_name like '%auto1';

 

FILE_NAME                         FILE_ID AUT

------------------------------ ---------- ---

/oradata06/auto1                     1041 YES

 

select t1.tablespace_name,t2.bytes/1024/1024/1024 "总容量G",t1.bytes/1024/1024/1024 "空余容量G",t1.bytes/t2.bytes "百分比G" from sys.sm$ts_free t1,sys.sm$ts_avail t2 where t1.tablespace_name=t2.tablespace_name and t2.tablespace_name= 'AUTOTBS1' order by 4

TABLESPACE_NAME                   总容量G  空余容量G    百分比G

------------------------------ ---------- ---------- ----------

AUTOTBS1                            .0625  .05859375      .9375

 

col name format a50

set linesize 120

select df.name,bytes from v$datafile df,v$tablespace ts where ts.name='AUTOTBS1' and ts.ts#=df.ts#

 

NAME                                                    BYTES

-------------------------------------------------- ----------

/oradata06/auto1                                     67108864

 

SQL> show parameter _enable_space

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_enable_space_preallocation          integer     3

 

create table aabb tablespace autotbs1 as select * from dba_objects;

 

col segment_name format a50

set linesize 130

 

select owner,segment_name,bytes from dba_segments where segment_name='AABB';

OWNER                          SEGMENT_NAME                                            BYTES

------------------------------ -------------------------------------------------- ----------

SCOTT                          AABB                                                 20971520

 

insert into aabb select * from aabb;

insert into aabb select * from aabb;

insert into aabb select * from aabb;

commit;

 

SQL> select owner,segment_name,bytes from dba_segments where segment_name='AABB';

 

OWNER                          SEGMENT_NAME                                            BYTES

------------------------------ -------------------------------------------------- ----------

SCOTT                          AABB                                                167772160

 

--autotbs1表空间已经没有空余容量

select t1.tablespace_name,t2.bytes/1024/1024/1024 "总容量G",t1.bytes/1024/1024/1024 "空余容量G",t1.bytes/t2.bytes "百分比G" from sys.sm$ts_free t1,sys.sm$ts_avail t2 where t1.tablespace_name=t2.tablespace_name and t2.tablespace_name= 'AUTOTBS1' order by 4

 

no rows selected

 

--数据文件已经从67108864扩展到了171966464

col name format a50

set linesize 120

select df.name,bytes from v$datafile df,v$tablespace ts where ts.name='AUTOTBS1' and ts.ts#=df.ts#

 

NAME                                                    BYTES

-------------------------------------------------- ----------

/oradata06/auto1                                    171966464

 

--过了约10分钟观察，虽然这30分钟内没有新的数据插入，数据文件仍然从171966464bytes扩展到了184549376bytes，增加了约12M的空间，增长比率约为12.5%

SQL> select name,bytes from v$datafile where file#=1041;

 

NAME                                                    BYTES

-------------------------------------------------- ----------

/oradata06/auto1                                    184549376

 

--表空间的空闲率上升至6.8%，略大于_kttext_warning 所指定的5%

select t1.tablespace_name,t2.bytes/1024/1024/1024 "总容量G",t1.bytes/1024/1024/1024 "空余容量G",t1.bytes/t2.bytes "百分比G" from sys.sm$ts_free t1,sys.sm$ts_avail t2 where t1.tablespace_name=t2.tablespace_name and t2.tablespace_name= 'AUTOTBS1' order by 4;

TABLESPACE_NAME                   总容量G  空余容量G    百分比G

------------------------------ ---------- ---------- ----------

AUTOTBS1                          .171875  .01171875 .068181818

 

 

2、增加_kttext_warning值又会再一次触发表空间预分配

--_kttext_warning默认值为5%

SQL> select i.ksppinm name,i.ksppdesc description,cv.ksppstvl value from sys.x$ksppi i,sys.x$ksppcv cv where i.inst_id=userenv('Instance') and cv.inst_id=userenv('Instance') and i.indx=cv.indx and i.ksppinm like '/_%' escape '/' and i.ksppinm like '%&var%' order by replace(i.ksppinm,'_','');

Enter value for var: _kttext_warning

old   1: select i.ksppinm name,i.ksppdesc description,cv.ksppstvl value from sys.x$ksppi i,sys.x$ksppcv cv where i.inst_id=userenv('Instance') and cv.inst_id=userenv('Instance') and i.indx=cv.indx and i.ksppinm like '/_%' escape '/' and i.ksppinm like '%&var%' order by replace(i.ksppinm,'_','')

new   1: select i.ksppinm name,i.ksppdesc description,cv.ksppstvl value from sys.x$ksppi i,sys.x$ksppcv cv where i.inst_id=userenv('Instance') and cv.inst_id=userenv('Instance') and i.indx=cv.indx and i.ksppinm like '/_%' escape '/' and i.ksppinm like '%_kttext_warning%' order by replace(i.ksppinm,'_','')

 

NAME

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

_kttext_warning

tablespace pre-extension warning threshold in percentage

5

 

--当前表空间的空闲空间百分比为6.8%

 select t1.tablespace_name,t2.bytes/1024/1024/1024 "总容量G",t1.bytes/1024/1024/1024 "空余容量G",t1.bytes/t2.bytes "百分比G" from sys.sm$ts_free t1,sys.sm$ts_avail t2 where t1.tablespace_name=t2.tablespace_name and t1.tablespace_name='AUTOTBS1' order by 4

TABLESPACE_NAME                   总容量G  空余容量G    百分比G

------------------------------ ---------- ---------- ----------

AUTOTBS1                          .171875  .01171875 .068181818

 

--下面将_kttext_warning修改为较大的值，观察是否数据文件还会继续扩展

alter system set "_kttext_warning"=20 scope=memory;

 

SQL> show parameter _kttext_warning

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_kttext_warning                      integer     20

 

--过了大约20分钟，观察到数据文件从184549376增加到了209715200，增加了24M，增幅为13.6%，表空间的空闲率从6.8%上升到了18%，虽然没有达到20%，但基本上还是比较接近的

select t1.tablespace_name,t2.bytes/1024/1024/1024 "总容量G",t1.bytes/1024/1024/1024 "空余容量G",t1.bytes/t2.bytes "百分比G" from sys.sm$ts_free t1,sys.sm$ts_avail t2 where t1.tablespace_name=t2.tablespace_name and t1.tablespace_name='AUTOTBS1' order by 4

 

TABLESPACE_NAME                   总容量G  空余容量G    百分比G

------------------------------ ---------- ---------- ----------

AUTOTBS1                         .1953125  .03515625        .18

 

select name,bytes from v$datafile where file#=1041;

NAME                                                    BYTES

-------------------------------------------------- ----------

/oradata06/auto1                                    209715200

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

转载于:http://blog.itpub.net/53956/viewspace-1269337/