注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

分析表的碎片情况  

2012-10-15 09:44:19|  分类: 优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

如果表有很多碎片,我们很容易处理,shrink、move、导出/导入都可以,但问题是怎么知道表的碎片程度呢?

 

该文足够长,最后结论见结尾。

先做试验:
建立测试表t:
SQL> create table t as select * from dba_objects where rownum<=50000;
 
Table created

收集表的统计信息:
SQL> exec dbms_stats.gather_table_stats(user,'t')
 
PL/SQL procedure successfully completed

查看统计信息:
SQL> select table_name,blocks,avg_row_len,num_rows from user_tables where table_name='T';
 
TABLE_NAME                         BLOCKS AVG_ROW_LEN   NUM_ROWS
------------------------------ ---------- ----------- ----------
T                                     732    97       50000

另一种收集统计信息的方式:
SQL> analyze table t compute statistics;
 
Table analyzed
 
SQL> select table_name,blocks,avg_row_len,num_rows from user_tables where table_name='T';
 
TABLE_NAME                         BLOCKS AVG_ROW_LEN   NUM_ROWS
------------------------------ ---------- ----------- ----------
T                                     732    100       50000

可以看到两种收集统计信息的收集到的AVG_ROW_LEN是有一些差距的,使用analyze table t compute statistics收集的数据稍大。

删数据产生碎片:
SQL> delete t where rownum<=40000;
 
40000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> exec dbms_stats.gather_table_stats(user,'t')
 
PL/SQL procedure successfully completed
 
 
SQL> select table_name,blocks,avg_row_len,num_rows from user_tables where table_name='T';
 
TABLE_NAME                         BLOCKS AVG_ROW_LEN   NUM_ROWS
------------------------------ ---------- ----------- ----------
T                                     732          98      10000
 
SQL> analyze table t compute statistics;
 
Table analyzed
 
SQL> select table_name,blocks,avg_row_len,num_rows from user_tables where table_name='T';
 
TABLE_NAME                         BLOCKS AVG_ROW_LEN   NUM_ROWS
------------------------------ ---------- ----------- ----------
T                                     732         103      10000
 
原表有50000行数据,删除40000行,还剩下10000,可以粗略估计该表表空间的利用率是10000/50000=20%
根据上面最新统计出来的平均行长*行数可以得出表数据大概需要多少存储空间,这个值除以高水位以下的空间,就可以得出表的空间的利用率。
 
 
SQL> select 98*10000/1024/8/732*100 from dual;
 
98*10000/1024/8/732*100
-----------------------
       16.3427467554645
 
SQL> select 103*10000/1024/8/732*100 from dual;
 
103*10000/1024/8/732*100
------------------------
        17.1765603654372

可以看到无论哪种统计信息方式计算出的表的空间的利用率都与估算的20%很近似,所以我们查找并处理松散的表可以根据以下步骤进行:
1.收集表的统计信息,如果最近刚收集完或表内容变化不大,可以省略这一步。
2.对空间利用率不足80%的表(20%的碎片),进行shrink或move。
3.重新收集该表的统计信息

具体实施步骤为:
如果安排在业务空闲时定时执行,可以写个过程或包,如下:
set serveroutput on
declare
  used_age  number;
  blocks_num number;
begin
  for rec in (select table_name from user_tables) loop
    execute immediate 'begin dbms_stats.gather_table_stats(user,'''||rec.table_name||''');end;';      
    execute immediate 'select round(avg_row_len*num_rows/1024/8/blocks*100,2),blocks from user_tables where table_name='''||rec.table_name||'''' into used_age,blocks_num;
    dbms_output.put_line('表利用率:' ||rec.table_name ||','||used_age);
      if used_age < 80 and blocks_num > 5 then  --如果表空间利用率小于80%,即碎片多于20%,同时高水位大于5(低于5的表shrink或move无效果),则对表进行整理,并重新收集索引统计信息
        execute immediate 'alter table ' || rec.table_name || ' enable row movement';
        execute immediate 'alter table ' || rec.table_name || ' shrink space compact cascade';
        execute immediate 'alter table ' || rec.table_name || ' disable row movement';
        dbms_output.put_line('表:' || rec.table_name || ' 整理已完成');
        execute immediate 'begin dbms_stats.gather_table_stats(user,'''||rec.table_name||''',cascade=>true);end;';
        dbms_output.put_line('表:' || rec.table_name || ' 收集统计信息已完成'||chr(10));
      end if; 
  end loop;
end;
/


以上实验对默认存储参数即storage(initial 64K)建立的表是没有问题的,但对storage(initial大于64K)的表却不适用,如下测试:
SQL> create table t2 storage(initial 100m) as select * from emp;
 
Table created
 
SQL> exec dbms_stats.gather_table_stats(user,'t2')
 
PL/SQL procedure successfully completed

SQL> select round(avg_row_len*num_rows/1024/8/blocks*100,2),blocks from user_tables where table_name='T2';
 
ROUND(AVG_ROW_LEN*NUM_ROWS/102     BLOCKS
------------------------------ ----------
                          0.19         35
可以看到initial 100m让表t2高水位一下子到了35块的高度,而且还降不下来,看来initial值不能太大,要不oracle会扫描太多无数据的空块。

SQL> alter table t2 move;
 
Table altered
 
SQL> select blocks from user_tables where table_name='T2';
 
    BLOCKS
----------
        35     
过高的高水位直接导致以上公式计算的空间利用率为0.19,也就是表中超过99%的是碎片,于是使用alter table move降低高水位,但执行后高水位没有任何变化。


那有没有更准确的统计表中碎片的方式呢?


借助系统提供的dbms_space包来查看一下碎片,以下是个人整理的程序:
create or replace procedure p_show_space(
v_object_name in varchar2,
v_owner in varchar2 default user,
v_object_type in varchar2 default 'TABLE',
v_partition_name in varchar2 default '') is
/* v_object_type 取值范围是 'INDEX'、'TABLE'、'CLUSTER'
该过程只适用于分析自动段空间管理的表 */

v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_hwm_blocks number;
v_hwm_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
v_wasted_percent number;
v_wasted_percent2 number;
v_fs1_percent number;
v_fs2_percent number;
v_fs3_percent number;
v_fs4_percent number;
v_full_percent number;

begin

dbms_space.unused_space(upper(v_owner), upper(v_object_name), upper(v_object_type), v_total_blocks,
v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
v_last_used_extent_block_id, v_last_used_block, upper(v_partition_name));

dbms_space.space_usage(upper(v_owner), upper(v_object_name), upper(v_object_type), v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, upper(v_partition_name));

v_wasted_percent:=round((v_fs1_blocks+v_fs2_blocks+v_fs3_blocks+v_fs4_blocks)/(v_total_blocks-v_unused_blocks)*100,2);
v_wasted_percent2:=round((v_fs1_blocks+v_fs2_blocks+v_fs3_blocks+v_fs4_blocks)/v_total_blocks*100,2);

v_hwm_blocks:=v_total_blocks-v_unused_blocks;
v_hwm_bytes:=v_hwm_blocks*8/1024;
v_total_bytes:=v_total_bytes/1024/1024;
v_unused_bytes:=v_unused_bytes/1024/1024;
v_unformatted_bytes:=v_unformatted_bytes/1024/1024;

v_fs1_bytes:=v_fs1_bytes/1024/1024;
v_fs2_bytes:=v_fs2_bytes/1024/1024;
v_fs3_bytes:=v_fs3_bytes/1024/1024;
v_fs4_bytes:=v_fs4_bytes/1024/1024;
v_full_bytes:=v_full_bytes/1024/1024;

v_fs1_percent:=round(v_fs1_blocks/v_hwm_blocks*100,2);
v_fs2_percent:=round(v_fs2_blocks/v_hwm_blocks*100,2);
v_fs3_percent:=round(v_fs3_blocks/v_hwm_blocks*100,2);
v_fs4_percent:=round(v_fs4_blocks/v_hwm_blocks*100,2);
v_full_percent:=round(v_full_blocks/v_hwm_blocks*100,2);

dbms_output.put_line(rpad('total blocks is',50,'-') || v_total_blocks);
dbms_output.put_line(rpad('total M bytes is ',50,'-') || v_total_bytes ||' M');
dbms_output.put_line(rpad('unused blocks is ',50,'-') || v_unused_blocks);
dbms_output.put_line(rpad('unused M bytes is ',50,'-') || v_unused_bytes ||' M');
dbms_output.put_line(rpad('HWM blocks is ',50,'-') || v_hwm_blocks);
dbms_output.put_line(rpad('HWM M bytes is ',50,'-') || v_hwm_bytes ||' M');
dbms_output.put_line(rpad('last_used_extent_file_id is ',50,'-') || v_last_used_extent_file_id);
dbms_output.put_line(rpad('last_used_extent_block_id is ',50,'-') || v_last_used_extent_block_id);
dbms_output.put_line(rpad('last_used_block is ',50,'-') || v_last_used_block);
dbms_output.put_line(rpad('*', 55, '*'));
dbms_output.put_line(rpad('unformatted_blocks is ',50,'-') || v_unformatted_blocks);
dbms_output.put_line(rpad('unformatted_bytes is ',50,'-') || v_unformatted_bytes);
dbms_output.put_line(rpad('fs1_blocks/0%--25% free space blocks ',50,'-') || v_fs1_blocks);
dbms_output.put_line(rpad('fs1_bytes/0%--25% free space M bytes ',50,'-') || v_fs1_bytes ||' M');
dbms_output.put_line(rpad('fs1_blocks/HWM_blocks ',50,'-') || v_fs1_percent ||'%');
dbms_output.put_line(rpad('fs2_blocks/25%--50% free space blocks ',50,'-') || v_fs2_blocks);
dbms_output.put_line(rpad('fs2_bytes/25%--50% free space M bytes ',50,'-') || v_fs2_bytes ||' M');
dbms_output.put_line(rpad('fs2_blocks/HWM_blocks ',50,'-') || v_fs2_percent ||'%');
dbms_output.put_line(rpad('fs3_blocks/50%--75% free space blocks ',50,'-') || v_fs3_blocks);
dbms_output.put_line(rpad('fs3_bytes/50%--75% free space M bytes ',50,'-') || v_fs3_bytes ||' M');
dbms_output.put_line(rpad('fs3_blocks/HWM_blocks ',50,'-') || v_fs3_percent ||'%');
dbms_output.put_line(rpad('fs4_blocks/75%--100% free space blocks ',50,'-') || v_fs4_blocks);
dbms_output.put_line(rpad('fs4_bytes/75%--100% free space M bytes ',50,'-') || v_fs4_bytes ||' M');
dbms_output.put_line(rpad('fs4_blocks/HWM_blocks ',50,'-') || v_fs4_percent ||'%');
dbms_output.put_line(rpad('full_blocks/full space Blocks ',50,'-') || v_full_blocks);
dbms_output.put_line(rpad('full_bytes/full space Blocks M Bytes ',50,'-') || v_full_bytes ||' M');
dbms_output.put_line(rpad('full_blocks/HWM_blocks ',50,'-') || v_full_percent ||'%');
dbms_output.put_line(rpad(' ', 55, ' '));
dbms_output.put_line(rpad('space wasted percent  (fs1+...fs4)/hwm is ',50,'-') || v_wasted_percent);
dbms_output.put_line(rpad('space wasted percent2 (fs1+...fs4)/total is ',50,'-') || v_wasted_percent2);
end;
/

 

使用以上程序查看表碎片的实验:

一、普通表
SQL> create table t as select * from dba_objects where rownum<=10;
 
Table created

SQL> set serveroutput on
SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------8
total M bytes is ---------------------------------.0625 M
unused blocks is ---------------------------------4
unused M bytes is --------------------------------.03125 M
HWM blocks is ------------------------------------4
HWM M bytes is -----------------------------------.03125 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------1896
last_used_block is -------------------------------4
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------0
fs4_bytes/75%--100% free space M bytes -----------0 M
fs4_blocks/HWM_blocks ----------------------------0%
full_blocks/full space Blocks --------------------1
full_bytes/full space Blocks M Bytes -------------.0078125 M
full_blocks/HWM_blocks ---------------------------25%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------0
space wasted percent2 (fs1+...fs4)/total is ------0
 
PL/SQL procedure successfully completed

开始增长数据:
SQL> insert into t select * from t;
 
10 rows inserted
 
SQL> /
 
20 rows inserted
 
SQL> /
 
40 rows inserted
 
SQL> /
 
80 rows inserted
 
SQL> /
 
160 rows inserted
 
SQL> /
 
320 rows inserted
 
SQL> /
 
640 rows inserted
 
SQL> /
 
1280 rows inserted
 
SQL> commit;
 
Commit complete


查看碎片情况:
SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------40
total M bytes is ---------------------------------.3125 M
unused blocks is ---------------------------------0
unused M bytes is --------------------------------0 M
HWM blocks is ------------------------------------40
HWM M bytes is -----------------------------------.3125 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6536
last_used_block is -------------------------------8
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------1
fs2_bytes/25%--50% free space M bytes ------------.0078125 M
fs2_blocks/HWM_blocks ----------------------------2.5%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------5
fs4_bytes/75%--100% free space M bytes -----------.0390625 M
fs4_blocks/HWM_blocks ----------------------------12.5%
full_blocks/full space Blocks --------------------29
full_bytes/full space Blocks M Bytes -------------.2265625 M
full_blocks/HWM_blocks ---------------------------72.5%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------15
space wasted percent2 (fs1+...fs4)/total is ------15
 
PL/SQL procedure successfully completed

没有删除操作浪费率就到了15%,看来浪费率为15%是不用管的!

继续增加数据:
SQL> insert into t select * from t;
 
2560 rows inserted
 
SQL> insert into t select * from t;
 
5120 rows inserted
 
SQL> insert into t select * from t;
 
10240 rows inserted
 
SQL> insert into t select * from t;
 
20480 rows inserted
 
SQL> commit;
 
Commit complete
SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------512
total M bytes is ---------------------------------4 M
unused blocks is ---------------------------------0
unused M bytes is --------------------------------0 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------2688
last_used_block is -------------------------------128
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------35
fs4_bytes/75%--100% free space M bytes -----------.2734375 M
fs4_blocks/HWM_blocks ----------------------------6.84%
full_blocks/full space Blocks --------------------461
full_bytes/full space Blocks M Bytes -------------3.6015625 M
full_blocks/HWM_blocks ---------------------------90.04%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------6.84
space wasted percent2 (fs1+...fs4)/total is ------6.84
 
PL/SQL procedure successfully completed

开始删除数据:
SQL> select count(*) from t;
 
  COUNT(*)
----------
     40960
 
SQL> delete t where rownum<=10960;
 
10960 rows deleted
 
SQL> commit;
 
Commit complete

SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------512
total M bytes is ---------------------------------4 M
unused blocks is ---------------------------------0
unused M bytes is --------------------------------0 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------2688
last_used_block is -------------------------------128
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------159
fs4_bytes/75%--100% free space M bytes -----------1.2421875 M
fs4_blocks/HWM_blocks ----------------------------31.05%
full_blocks/full space Blocks --------------------337
full_bytes/full space Blocks M Bytes -------------2.6328125 M
full_blocks/HWM_blocks ---------------------------65.82%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------31.05
space wasted percent2 (fs1+...fs4)/total is ------31.05
 
PL/SQL procedure successfully completed

近41000行,删除了近11000,浪费了31.05%,还比较正确。

阶段总结:建表时,如果没有指定initial和next参数,则得出的两个浪费率总是一致,因为此时总块数和高水位数总是一致。

 

 

二、有initial 100M初始值的表
SQL> create table t storage(initial 100m) as select * from dba_objects where rownum<=10;
 
Table created
SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------13277
unused M bytes is --------------------------------103.7265625 M
HWM blocks is ------------------------------------35
HWM M bytes is -----------------------------------.2734375 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------35
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------0
fs4_bytes/75%--100% free space M bytes -----------0 M
fs4_blocks/HWM_blocks ----------------------------0%
full_blocks/full space Blocks --------------------1
full_bytes/full space Blocks M Bytes -------------.0078125 M
full_blocks/HWM_blocks ---------------------------2.86%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------0
space wasted percent2 (fs1+...fs4)/total is ------0
 
PL/SQL procedure successfully completed

开始增加数据:
SQL> insert into t select * from t;
 
10 rows inserted
 
SQL> /
 
20 rows inserted
 
SQL> /
 
40 rows inserted
 
SQL> /
 
80 rows inserted
 
SQL> /
 
160 rows inserted
 
SQL> /
 
320 rows inserted
 
SQL> /
 
640 rows inserted
 
SQL> /
 
1280 rows inserted
 
SQL> /
 
2560 rows inserted
 
SQL> /
 
5120 rows inserted
 
SQL> /
 
10240 rows inserted
 
SQL> /
 
20480 rows inserted
 
SQL> commit;
 
Commit complete


SQL> select count(*) from t;
 
  COUNT(*)
----------
     40960
 
SQL> delete t where rownum<=10960;
 
10960 rows deleted
 
SQL> commit;
 
Commit complete

SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------12800
unused M bytes is --------------------------------100 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------512
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------17
fs4_bytes/75%--100% free space M bytes -----------.1328125 M
fs4_blocks/HWM_blocks ----------------------------3.32%
full_blocks/full space Blocks --------------------461
full_bytes/full space Blocks M Bytes -------------3.6015625 M
full_blocks/HWM_blocks ---------------------------90.04%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------3.32
space wasted percent2 (fs1+...fs4)/total is ------.13
 
PL/SQL procedure successfully completed

SQL> delete t where rownum<=10960;
 
10960 rows deleted
 
SQL> commit;
 
Commit complete

SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------12800
unused M bytes is --------------------------------100 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------512
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------141
fs4_bytes/75%--100% free space M bytes -----------1.1015625 M
fs4_blocks/HWM_blocks ----------------------------27.54%
full_blocks/full space Blocks --------------------337
full_bytes/full space Blocks M Bytes -------------2.6328125 M
full_blocks/HWM_blocks ---------------------------65.82%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------27.54
space wasted percent2 (fs1+...fs4)/total is ------1.06
 
PL/SQL procedure successfully completed

SQL> select count(*) from t;
 
  COUNT(*)
----------
     30000
 
SQL> delete t where rownum<=20000;
 
20000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> exec p_show_space('t')
 
PL/SQL procedure successfully completed
 
SQL> set serveroutput on
SQL> exec p_show_space('t')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------12800
unused M bytes is --------------------------------100 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------512
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------1
fs3_bytes/50%--75% free space M bytes ------------.0078125 M
fs3_blocks/HWM_blocks ----------------------------.2%
fs4_blocks/75%--100% free space blocks -----------365
fs4_bytes/75%--100% free space M bytes -----------2.8515625 M
fs4_blocks/HWM_blocks ----------------------------71.29%
full_blocks/full space Blocks --------------------112
full_bytes/full space Blocks M Bytes -------------.875 M
full_blocks/HWM_blocks ---------------------------21.88%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------71.48
space wasted percent2 (fs1+...fs4)/total is ------2.75
 
PL/SQL procedure successfully completed


=========================================================================================================

 

 

三、有有initial 100M初始值、next 30M的表
SQL> create table t storage (initial 100m next 30m) as select * from dba_objects where rownum<=10;
 
Table created

SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------13277
unused M bytes is --------------------------------103.7265625 M
HWM blocks is ------------------------------------35
HWM M bytes is -----------------------------------.2734375 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------35
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------0
fs4_bytes/75%--100% free space M bytes -----------0 M
fs4_blocks/HWM_blocks ----------------------------0%
full_blocks/full space Blocks --------------------1
full_bytes/full space Blocks M Bytes -------------.0078125 M
full_blocks/HWM_blocks ---------------------------2.86%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------0
space wasted percent2 (fs1+...fs4)/total is ------0
 
PL/SQL procedure successfully completed

SQL> insert into t select * from t;
 
10 rows inserted
 
SQL> /
 
20 rows inserted
 
SQL> /
 
40 rows inserted
 
SQL> /
 
80 rows inserted
 
SQL> /
 
160 rows inserted
 
SQL> /
 
320 rows inserted
 
SQL> /
 
640 rows inserted
 
SQL> /
 
1280 rows inserted
 
SQL> /
 
2560 rows inserted
 
SQL> /
 
5120 rows inserted
 
SQL> /
 
10240 rows inserted
 
SQL> /
 
20480 rows inserted
 
SQL> commit;
 
Commit complete


SQL> select count(*) from t;
 
  COUNT(*)
----------
     40960
 
SQL> delete t where rownum<=10960;
 
10960 rows deleted
 
SQL> commit;
 
Commit complete

SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------12800
unused M bytes is --------------------------------100 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------512
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------0
fs3_bytes/50%--75% free space M bytes ------------0 M
fs3_blocks/HWM_blocks ----------------------------0%
fs4_blocks/75%--100% free space blocks -----------141
fs4_bytes/75%--100% free space M bytes -----------1.1015625 M
fs4_blocks/HWM_blocks ----------------------------27.54%
full_blocks/full space Blocks --------------------337
full_bytes/full space Blocks M Bytes -------------2.6328125 M
full_blocks/HWM_blocks ---------------------------65.82%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------27.54
space wasted percent2 (fs1+...fs4)/total is ------1.06
 
PL/SQL procedure successfully completed

SQL> delete t where rownum<=20000;
 
20000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select count(*) from t;
 
  COUNT(*)
----------
     10000
 
SQL> exec p_show_space('t', 'scott', 'table')
 
total blocks is-----------------------------------13312
total M bytes is ---------------------------------104 M
unused blocks is ---------------------------------12800
unused M bytes is --------------------------------100 M
HWM blocks is ------------------------------------512
HWM M bytes is -----------------------------------4 M
last_used_extent_file_id is ----------------------4
last_used_extent_block_id is ---------------------6656
last_used_block is -------------------------------512
*******************************************************
unformatted_blocks is ----------------------------0
unformatted_bytes is -----------------------------0
fs1_blocks/0%--25% free space blocks -------------0
fs1_bytes/0%--25% free space M bytes -------------0 M
fs1_blocks/HWM_blocks ----------------------------0%
fs2_blocks/25%--50% free space blocks ------------0
fs2_bytes/25%--50% free space M bytes ------------0 M
fs2_blocks/HWM_blocks ----------------------------0%
fs3_blocks/50%--75% free space blocks ------------1
fs3_bytes/50%--75% free space M bytes ------------.0078125 M
fs3_blocks/HWM_blocks ----------------------------.2%
fs4_blocks/75%--100% free space blocks -----------365
fs4_bytes/75%--100% free space M bytes -----------2.8515625 M
fs4_blocks/HWM_blocks ----------------------------71.29%
full_blocks/full space Blocks --------------------112
full_bytes/full space Blocks M Bytes -------------.875 M
full_blocks/HWM_blocks ---------------------------21.88%
                                                      
space wasted percent  (fs1+...fs4)/hwm is --------71.48
space wasted percent2 (fs1+...fs4)/total is ------2.75
 
PL/SQL procedure successfully completed

第二种情况和第三种情况测试得到的数据完全一致!看来表的initial 参数,会对以上程序的结果影响很大。

 

最终总结:
由于小表全表扫描速度影响不大,所以对于1M以下的表,原则上不用监控。
表碎片达到什么情况,可以整理表了呢?
一、如果表没有initial参数,那么有两种查看表碎片多少的方式:
1.开头的平均行长*行数/高水位块的大小,该值比较准确,利用率不足80%,可以考虑重整表。
2.以上p_show_space()过程的两个浪费百分比,浪费率达到50%,可以考虑重整表。
二、如果表有initial参数,则不好判断是否该整理碎片了,但勉强总结出以下:
使用p_show_space()过程查看浪费率,如果浪费率2值高于50%的话,一定需要重整表了;浪费率1值高于50%的话,如果该表涉及到全表扫描那么也应该重整表了。


if v_wasted_percent>50 then
  dbms_output.put_line(v_object_name||' wasted_percent '||v_wasted_percent);
  execute immediate 'alter table '||v_object_name||' enable row movement';
  execute immediate 'alter table '||v_object_name||' shrink space cascade';
end if;


把以上查找碎片的过程改为块形式,用于不便于建立过程或偶尔分析一次的库:
set serveroutput on
declare
--该过程只适用于分析自动段空间管理的表
v_object_name varchar2(80);
v_owner varchar2(50) default user;
v_object_type varchar2(50) default 'TABLE';  --v_object_type 取值范围是 'INDEX'、'TABLE'、'CLUSTER'
v_partition_name varchar2(80) default '';
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_hwm_blocks number;
v_hwm_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
v_wasted_percent number;
v_wasted_percent2 number;
v_fs1_percent number;
v_fs2_percent number;
v_fs3_percent number;
v_fs4_percent number;
v_full_percent number;

begin
v_object_name:='&table_name';
dbms_space.unused_space(upper(v_owner), upper(v_object_name), upper(v_object_type), v_total_blocks,
v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
v_last_used_extent_block_id, v_last_used_block, upper(v_partition_name));

dbms_space.space_usage(upper(v_owner), upper(v_object_name), upper(v_object_type), v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, upper(v_partition_name));

v_wasted_percent:=round((v_fs1_blocks+v_fs2_blocks+v_fs3_blocks+v_fs4_blocks)/(v_total_blocks-v_unused_blocks)*100,2);
v_wasted_percent2:=round((v_fs1_blocks+v_fs2_blocks+v_fs3_blocks+v_fs4_blocks)/v_total_blocks*100,2);

v_hwm_blocks:=v_total_blocks-v_unused_blocks+1;
v_hwm_bytes:=v_hwm_blocks*8/1024;
v_total_bytes:=v_total_bytes/1024/1024;
v_unused_bytes:=v_unused_bytes/1024/1024;
v_unformatted_bytes:=v_unformatted_bytes/1024/1024;

v_fs1_bytes:=v_fs1_bytes/1024/1024;
v_fs2_bytes:=v_fs2_bytes/1024/1024;
v_fs3_bytes:=v_fs3_bytes/1024/1024;
v_fs4_bytes:=v_fs4_bytes/1024/1024;
v_full_bytes:=v_full_bytes/1024/1024;

v_fs1_percent:=round(v_fs1_blocks/v_hwm_blocks*100,2);
v_fs2_percent:=round(v_fs2_blocks/v_hwm_blocks*100,2);
v_fs3_percent:=round(v_fs3_blocks/v_hwm_blocks*100,2);
v_fs4_percent:=round(v_fs4_blocks/v_hwm_blocks*100,2);
v_full_percent:=round(v_full_blocks/v_hwm_blocks*100,2);

dbms_output.put_line(rpad('total blocks is',50,'-') || v_total_blocks);
dbms_output.put_line(rpad('total M bytes is ',50,'-') || v_total_bytes ||' M');
dbms_output.put_line(rpad('unused blocks is ',50,'-') || v_unused_blocks);
dbms_output.put_line(rpad('unused M bytes is ',50,'-') || v_unused_bytes ||' M');
dbms_output.put_line(rpad('HWM blocks is ',50,'-') || v_hwm_blocks);
dbms_output.put_line(rpad('HWM M bytes is ',50,'-') || v_hwm_bytes ||' M');
dbms_output.put_line(rpad('last_used_extent_file_id is ',50,'-') || v_last_used_extent_file_id);
dbms_output.put_line(rpad('last_used_extent_block_id is ',50,'-') || v_last_used_extent_block_id);
dbms_output.put_line(rpad('last_used_block is ',50,'-') || v_last_used_block);
dbms_output.put_line(rpad('*', 55, '*'));
dbms_output.put_line(rpad('unformatted_blocks is ',50,'-') || v_unformatted_blocks);
dbms_output.put_line(rpad('unformatted_bytes is ',50,'-') || v_unformatted_bytes);
dbms_output.put_line(rpad('fs1_blocks/0%--25% free space blocks ',50,'-') || v_fs1_blocks);
dbms_output.put_line(rpad('fs1_bytes/0%--25% free space M bytes ',50,'-') || v_fs1_bytes ||' M');
dbms_output.put_line(rpad('fs1_blocks/HWM_blocks ',50,'-') || v_fs1_percent ||'%');
dbms_output.put_line(rpad('fs2_blocks/25%--50% free space blocks ',50,'-') || v_fs2_blocks);
dbms_output.put_line(rpad('fs2_bytes/25%--50% free space M bytes ',50,'-') || v_fs2_bytes ||' M');
dbms_output.put_line(rpad('fs2_blocks/HWM_blocks ',50,'-') || v_fs2_percent ||'%');
dbms_output.put_line(rpad('fs3_blocks/50%--75% free space blocks ',50,'-') || v_fs3_blocks);
dbms_output.put_line(rpad('fs3_bytes/50%--75% free space M bytes ',50,'-') || v_fs3_bytes ||' M');
dbms_output.put_line(rpad('fs3_blocks/HWM_blocks ',50,'-') || v_fs3_percent ||'%');
dbms_output.put_line(rpad('fs4_blocks/75%--100% free space blocks ',50,'-') || v_fs4_blocks);
dbms_output.put_line(rpad('fs4_bytes/75%--100% free space M bytes ',50,'-') || v_fs4_bytes ||' M');
dbms_output.put_line(rpad('fs4_blocks/HWM_blocks ',50,'-') || v_fs4_percent ||'%');
dbms_output.put_line(rpad('full_blocks/full space Blocks ',50,'-') || v_full_blocks);
dbms_output.put_line(rpad('full_bytes/full space Blocks M Bytes ',50,'-') || v_full_bytes ||' M');
dbms_output.put_line(rpad('full_blocks/HWM_blocks ',50,'-') || v_full_percent ||'%');
dbms_output.put_line(rpad(' ', 55, ' '));
dbms_output.put_line(rpad('space wasted percent  (fs1+...fs4)/hwm is ',50,'-') || v_wasted_percent);
dbms_output.put_line(rpad('space wasted percent2 (fs1+...fs4)/total is ',50,'-') || v_wasted_percent2);
end;
/

  评论这张
 
阅读(487)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018