表空間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;

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

11g 表空間extent預分配特性

相關文章