ITPub博客

首页 > 应用开发 > IT综合 > something about initial parameter int LMT.

something about initial parameter int LMT.

原创 IT综合 作者:jametong 时间:2005-04-15 11:15:56 0 删除 编辑
initial and lmt - 8i  July 09, 2003
Reviewer:  M. Bento  from Lisbon, Portugal

When moving a table into a uniform LMT using alter table without the storage
clause the table's initial is not set to the LMT default initial parameter but
keeps its previous value ending up with tables with different initial extents
inside the same LM tablespace.
Should one set the initial along with the alter table in order to prevent
fragmentation?
Thanks.


Followup:  
you are confusing the initial extent sizes with actual extent sizes.

Rest assured, the EXTENTS actually allocated are all the same size in that
UNIFORM LMT -- use user_extents to verify that.

The INITIAL EXTENT (and next extent and pctincrease and minextents) are used
during the initial creation to figure out HOW MANY extents to create.  Consider:

ops$tkyte@ORA920> create tablespace testing
  2  extent management local
  3  uniform size 512k
  4  /

Tablespace created.

ops$tkyte@ORA920> compute sum of mbytes on report
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int )
  2  tablespace testing
  3  storage ( initial 1m );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select extent_id, bytes/1024/1024 mbytes
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     MBYTES
---------- ----------
         0         .5
         1         .5
           ----------
sum                 1

see, 2 extents of 512k -- for a total of 1m, because the 1m was requested..

ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int )
  2  tablespace testing
  3  storage ( initial 1m next 1m minextents 4 pctincrease 100 );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select extent_id, bytes/1024/1024 mbytes
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     MBYTES
---------- ----------
         0         .5
         1         .5
         2         .5
         3         .5
         4         .5
         5         .5
         6         .5
         7         .5
         8         .5
         9         .5
        10         .5
        11         .5
        12         .5
        13         .5
        14         .5
        15         .5
           ----------
sum                 8

16 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select 1 + 1 + 2 + 4 from dual;

   1+1+2+4
----------
         8

there, Oracle took the initial, next, minextents and pctincrease and computed
"hey, it would have taken 8m in a DMT, please grab 8m of space" and did so, 512k
at a time...


ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int )
  2  tablespace testing
  3  storage ( initial 64k minextents 10 );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select extent_id, bytes/1024/1024 mbytes
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     MBYTES
---------- ----------
         0         .5
         1         .5
         2         .5
         3         .5
         4         .5
         5         .5
         6         .5
         7         .5
         8         .5
         9         .5
           ----------
sum                 5

10 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920>

Here Oracle did 64k * 10 = 640k, but also saw the minextents 10 -- and said,
allocate at least 10 extents, as long as 10 extents of space is larger then
640k

But there is NO fragmentation here, they are all 512k extents.
 
 [@more@]

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

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

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    283381