Chinaunix首页 | 论坛 | 博客
  • 博客访问: 6924123
  • 博文数量: 700
  • 博客积分: 10821
  • 博客等级: 上将
  • 技术积分: 12011
  • 用 户 组: 普通用户
  • 注册时间: 2005-12-02 10:41
个人简介

大数据、ML、AI、云计算openstack、Linux、SpringCloud。

文章分类

全部博文(700)

分类: Oracle

2013-10-21 19:29:15

dbms_space的常用过程: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

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

CREATE_TABLE_COST有两种用法,因此包内进行了overload,具体的语法如下:

dbms_space.CREATE_TABLE_COST (

  tablespace_name   IN VARCHAR2,

  avg_row_size      IN NUMBER,

  row_count         IN NUMBER,

  pct_free          IN NUMBER,

  used_bytes        OUT NUMBER,

  alloc_bytes       OUT NUMBER);

 

DBMS_SPACE.CREATE_TABLE_COST (

  tablespace_name   IN VARCHAR2,

  colinfos          IN CREATE_TABLE_COST_COLUMNS,

  row_count         IN NUMBER,

  pct_free          IN NUMBER,

  used_bytes        OUT NUMBER,

  alloc_bytes       OUT NUMBER);

 

CREATE TYPE create_table_cost_colinfo IS OBJECT (

  COL_TYPE  VARCHAR(200),

  COL_SIZE  NUMBER);

 

下面是关于CREATE_TABLE_COST的测试

1、测试创建一个表所需的存储大小,预计该表平均行长度为100字节,10000行数据

SQL> DECLARE

 2   V1 NUMBER;

 3   V2 NUMBER;

 4 BEGIN

 5   DBMS_SPACE.CREATE_TABLE_COST('USERS', 100, 10000, 10, V1, V2);

 6   DBMS_OUTPUT.PUT_LINE('V1: '||V1/1024/8||' V2: '||V2/1024/8);

 7 END;

 8 /

V1: 143 V2: 256         --估算出该表需要存储空间143块,所需分配空间256

PL/SQL过程已成功完成。

 

2、创建该表,并插入10000行数据

SQL> CREATE TABLE T1(C CHAR(96));         --96字节的char字段平均行长度为100字节

表已创建。

 

SQL> BEGIN

 2   FOR I IN 1..10000 LOOP

 3    INSERT INTO T1 VALUES(I);

 4   ENDLOOP;

 5   COMMIT;

 6 END;

 7 /

PL/SQL过程已成功完成。

 

3、分析表统计信息

SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

表已分析。

 

SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN

FROM USER_TABLES WHERE TABLE_NAME='T1';

   BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

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

      180          76        100

--经检查,高水平线之前的块数180块,高水平线之后的空块数76块,总存储空间为256块,与DBMS_SPACE.CREATE_INDEX_COST计算出的总需要存储空间大小相符。

 

4、通过dbms_space.space_usage过程,可以进一步看到表中各个块的使用情况

declare

 unf number;

 unfb number;

 fs1 number;

 fs1b number;

 fs2 number;

 fs2b number;

 fs3 number;

 fs3b number;

 fs4 number;

 fs4b number;

 full number;

 fullb number;

 own dba_tables.owner%type;

 tab dba_tables.table_name%type;

 yesno varchar2(3);

 type parts is table of dba_tab_partitions%rowtype;

 partlist parts;

 type cursor_ref is ref cursor;

 c_cur cursor_ref;

begin

 own:=upper('&owner');

 tab:=upper('&table_name');

 dbms_output.put_line('--------------------------------------------------------------------------------');

 open c_cur for select partitioned from dba_tables

where wner=own and table_name=tab;

 fetch c_cur into yesno;

 close c_cur;

 dbms_output.put_line('Owner:    '||own);

 dbms_output.put_line('Table:    '||tab);

 dbms_output.put_line('------------------------------------------------');

 if yesno='NO' then

   dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);

   dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);

 else

   open c_cur for select * from dba_tab_partitions

     where table_owner=own and table_name=tab;

   fetch c_cur bulk collect into partlist;

   close c_cur;   

   for i in partlist.first .. partlist.last   loop

     dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);

     dbms_output.put_line('Partition: '||partlist(i).partition_name);

     dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);

   end loop;

 end if;

 dbms_output.put_line('--------------------------------------------------------------------------------');

end;

/

 

输入owner的值: HR

原值  22:  own:=upper('&owner');

新值  22:  own:=upper('HR');

输入table_name的值: T1

原值  23:  tab:=upper('&table_name');

新值  23:  tab:=upper('T1');

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

Owner:    HR

Table:    T1

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

unf: 0 fs1: 1 fs2: 0 fs3: 0 fs4: 39 full: 140

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

PL/SQL过程已成功完成。

--经查看,发现该表写满数据的块有140块,3/4满的块有39块,1/4满的块有1块,该表存储空间没有有效利用,可以看到140+39+1=180,这些均为高水平线之下的块。但与DBMS_SPACE.CREATE_INDEX_COST计算出的数据需要143块不符。

 

5、对表进行空间整理并重新分析

SQL> ALTER TABLE T1 MOVE;

表已更改。

 

SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

表已分析。

 

SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN

FROM USER_TABLES WHERE TABLE_NAME='T1';

   BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

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

      155         101        100

--经检查,高水平线之前的块数155,高水平线之后的空块数101,平均行长度100字节

 

6、通过dbms_space.space_usage过程,可以进一步看到表中各个块的使用情况

declare

 unf number;

 unfb number;

 fs1 number;

 fs1b number;

 fs2 number;

 fs2b number;

 fs3 number;

 fs3b number;

 fs4 number;

 fs4b number;

 full number;

 fullb number;

 own dba_tables.owner%type;

 tab dba_tables.table_name%type;

 yesno varchar2(3);

 type parts is table of dba_tab_partitions%rowtype;

 partlist parts;

 type cursor_ref is ref cursor;

 c_cur cursor_ref;

begin

 own:=upper('&owner');

 tab:=upper('&table_name');

 dbms_output.put_line('--------------------------------------------------------------------------------');

 open c_cur for select partitioned from dba_tables

where wner=own and table_name=tab;

 fetch c_cur into yesno;

 close c_cur;

 dbms_output.put_line('Owner:    '||own);

 dbms_output.put_line('Table:    '||tab);

 dbms_output.put_line('------------------------------------------------');

 if yesno='NO' then

   dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);

   dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);

 else

   open c_cur for select * from dba_tab_partitions

     where table_owner=own and table_name=tab;

   fetch c_cur bulk collect into partlist;

   close c_cur;   

   for i in partlist.first .. partlist.last   loop

     dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);

     dbms_output.put_line('Partition: '||partlist(i).partition_name);

     dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);

   end loop;

 end if;

 dbms_output.put_line('--------------------------------------------------------------------------------');

end;

/

输入owner的值: HR

原值  22:  own:=upper('&owner');

新值  22:  own:=upper('HR');

输入table_name的值: T1

原值  23:  tab:=upper('&table_name');

新值  23:  tab:=upper('T1');

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

Owner:    HR

Table:    T1

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

unf: 0 fs1: 0 fs2: 0 fs3: 0 fs4: 0 full: 143

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

PL/SQL过程已成功完成。

--经查看,发现该表写满数据的块有143块,与DBMS_SPACE.CREATE_INDEX_COST计算出的数据需要块数完全相同

 

 

-- review the parameters

SELECT argument_name, data_type, type_owner, type_name

FROM all_arguments

WHERE object_name = 'CREATE_TABLE_COST'

AND verload = 2

 

-- examine the input parameter type

SELECT text

FROM dba_source

WHERE name = 'CREATE_TABLE_COST_COLUMNS';

 

-- drill down further into the input parameter type

SELECT text

FROM dba_source

WHERE name = 'create_table_cost_colinfo';

 

set serveroutput on 

DECLARE

 ub NUMBER;

 ab NUMBER;

 cl sys.create_table_cost_columns;

BEGIN

 cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),

       sys.create_table_cost_colinfo('VARCHAR2',30),

       sys.create_table_cost_colinfo('VARCHAR2',30),

       sys.create_table_cost_colinfo('DATE',NULL)); 

 DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab); 

 DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub));

 DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab));

END;

/


阅读(4474) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~