ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBMS_SPACE包的使用(1)

DBMS_SPACE包的使用(1)

原创 Linux操作系统 作者:Ora-600 时间:2009-04-27 23:57:45 0 删除 编辑

最近有朋友问到了DBMS_SPACE包的使用,也看了一下,大部分是关于dbms_space.space_usage的使用,space_usage这个过程的例子已经很多了,我也就不再多说了,除了这个过程外,另外还有两个过程也有着特殊的用处,但使用的人不多,我们也来看看这两个过程有什么用。

这两个过程为:CREATE_INDEX_COSTCREATE_TABLE_COST,分别用户评估创建索引和创建表的存储开销(空间占用情况)。

 

CREATE_INDEX_COST的语法如下:

DBMS_SPACE.CREATE_INDEX_COST (

   ddl             IN    VARCHAR2,

   used_bytes      OUT   NUMBER,

   alloc_bytes     OUT   NUMBER,

   plan_table      IN    VARCHAR2 DEFAULT NULL);

 

下面是相关的测试代码:

1、准备相关表和数据

SQL> set serveroutput on

SQL> create table t(c char(100),d varchar2(200));

表已创建。

 

SQL> begin

  2    for i in 1..5000 loop

  3     insert into t values(i,i);

  4    end loop;

  5    commit;

  6  end;

  7  /

PL/SQL 过程已成功完成。

 

2、分析表,注意:没有统计信息,CREATE_INDEX_COST将无法计算索引的存储开销

SQL> analyze table t compute statistics;

表已分析。

 

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

488.28125  640    --计算出的索引将占用488K字节空间,为该索引需要分配640k存储空间

PL/SQL 过程已成功完成。

 

3、创建实际索引,确定索引存储空间是否与计算的结果相符

SQL> create index i on t(c);

索引已创建。

 

SQL> select count(*) from user_extents where segment_name='I';

  COUNT(*)

----------

        11

已选择1行。  --1164k的区,比计算出的大1个区

 

4、再次装载数据

SQL> begin

  2    for i in 1..5000 loop

  3     insert into t values(i,i);

  4    end loop;

  5    commit;

  6  end;

  7  /

PL/SQL 过程已成功完成。

 

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

488.28125  640           --没有分析之前,获得得仍然是根据以前分析结果计算的值

PL/SQL 过程已成功完成。

 

SQL> analyze table t compute statistics;

表已分析。

 

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

976.5625  2048                  --分析之后,得到新的结果

PL/SQL 过程已成功完成。       

 

5、再次验证,1664k的区和11024k的区,2048k,与估计值相同

SQL> select count(*) from user_extents where segment_name='I';

  COUNT(*)

----------

        17

 

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

6、换了一个字段进行测试

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

39.0625  192        --计算出的索引将占用39K字节空间,为该索引需要分配192k存储空间

PL/SQL 过程已成功完成。

 

7、创建索引,新建的索引比估算的值大1个区

SQL> create index i on t(d);

索引已创建。

 

SQL> select count(*) from user_extents where segment_name='I';

  COUNT(*)

----------

         4

 

SQL> drop index i;

 

8、再次装载数据并分析表

SQL> begin

  2    for i in 1..10000 loop

  3     insert into t values(i,i);

  4    end loop;

  5    commit;

  6  end;

  7  /

PL/SQL 过程已成功完成。

 

SQL> analyze table t compute statistics;

表已分析。

 

9、重新计算,得到新的估算值

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

78.125  320

 

PL/SQL 过程已成功完成。

 

10、创建索引,新建的索引比估计的大2个区

SQL> create index i on t(d);

索引已创建。

 

SQL> select count(*) from user_extents where segment_name='I';

  COUNT(*)

----------

         7

 

11、顺便测试shink space的效果

SQL> select count(*) from t;

  COUNT(*)

----------

     20000

 

SQL> delete t where rownum<=15000;

已删除15000行。

 

SQL> commit;

提交完成。

 

SQL> alter table t enable row movement;

表已更改。

 

12、在删掉15000行数据后,没有整理空间之前进行统计信息收集

SQL> analyze table t compute statistics;

表已分析。

 

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

24.4140625  128          --基于新收集的统计信息计算,估算的索引需要分配128k存储空间

PL/SQL 过程已成功完成。

 

13、收缩表,释放占用的存储空间

SQL> alter table t shrink space;          

表已更改。

 

SQL> analyze table t compute statistics;     

表已分析。

 

SQL> declare

  2    v1 number;

  3    v2 number;

  4  begin

  5    DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);

  6    dbms_output.put_line(v1/1024||'  '||v2/1024);

  7  end;

  8  /

24.4140625  128   --收缩后重新收集统计信息,与原统计信息一样,因此计算出的大小一样

PL/SQL 过程已成功完成。

 

SQL> select count(*) from user_extents where segment_name='I';  

  COUNT(*)

----------

         7

--现有索引并没有收缩,仅仅是表空间进行了收缩,因此现有索引仍保持原大小

 

14、重建索引,对比新的索引大小与计算出的索引大小一样大

SQL> alter index i rebuild;

索引已更改。

 

SQL> select count(*) from user_extents where segment_name='I';

 

  COUNT(*)

----------

         2

--重建索引后新的索引占用空间与计算出的空间一样大

 

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

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

注册时间:2008-05-11

  • 博文量
    31
  • 访问量
    43918