首页
登录 | 注册

dbms_space解析

dbms_space的常用过程:space_usage
另两个过程为:CREATE_INDEX_COST和CREATE_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行。 --11个64k的区,比计算出的大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、再次验证,16个64k的区和1个1024k的区,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;


相关文章

  • 注意,我并不会是百分之一百正确的.例如,一个系统很可能没有/proc文件系统支持,但是大多数系统肯定有.这里我假定你是"随大流的",并有一个典型配置的系统. 什么是符号(Symbols)? 内核符号表(Kernel Sy ...
  • Redis源码解析--NET
        关于Redis资料:     要看原滋原味的请点这里,要看有我参与的山寨货请点这里,当然我也不反对看这里.     在接下来的日子里,我会记录下我对Redis源码的一些认识,首先从Event driven programming l ...
  • 作者:gfree.wind@gmail.com 博客:linuxfocus.blog.chinaunix.net 微博:weibo.com/glinuxer QQ技术群:4367710 github:https://github.com/g ...
  • List Comprehensions 和 Generator Expressions解析 By Harrison Feng List Comprehensions和Generator Expressions是Python里十分常用的在复杂 ...
  • 本月起,青岛市民“一元钱”就可申请办公司.根据将于3月1日启动注册资本登记制度改革,新的制度放宽注册资本登记条件,市民“零首付”可办公司.同时,下月起,青岛市将启用新版营业执照.那么,新的<公司法>和原来比又有哪些重大变化呢?记 ...
  • Java解析excel工具easyexcel助你快速简单避免OOM[图]
    Java解析excel工具easyexcel助你快速简单避免OOM[图] Java解析.生成Excel比较有名的框架有Apache poi.jxl.但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决 ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.012 s.
苏ICP备12049786号-20