表碎片分析（三种方法对比）

三种表碎片分析语句比较：
insert into a select * from a;


/
delete a where rownum<100000;






select bytes/1024/1024 from dba_segments where segment_name='A';


analyze table wyq.a compute statistics;


第一种：
 SELECT a.owner "用户",
       a.TABLE_NAME "表名",
       a.NUM_ROWS "行数",
       ROUND(a.AVG_ROW_LEN * a.NUM_ROWS / 1024 / 1024 / 0.9) "预计需要(m)",
       round(bytes / 1024 / 1024) "实际大小(m)",
       ROUND(bytes / 1024 / 1024 -
             AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) "空块大小(m)",
       round(ROUND(bytes / 1024 / 1024 -
                   AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) /
             round(bytes / 1024 / 1024),
             2) * 100 "空块占比",
       'exec system.dbms_private.shrinktable(''' || a.owner || ''',''' ||
       a.table_name || ''');' shrink_ddl
  from dba_tables a, dba_segments b
 WHERE b.bytes / 1024 / 1024 -
       a.AVG_ROW_LEN * a.NUM_ROWS / 1024 / 1024 / 0.9 > 10
   and round(ROUND(bytes / 1024 / 1024 -
                   AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) /
             round(bytes / 1024 / 1024),
             2) * 100 > 4
   and a.owner in ('WYQ')
   and a.blocks>10
   and a.owner = b.owner
   and a.table_name = b.segment_name
 order by 7 desc, 6 desc;
 
 
 释放：65M
 第二种：
 select owner,table_name,round((blocks*8192/1024/1024),0) "total",
round((num_rows*avg_row_len/1024/1024),0) "used",
(1-round((num_rows*avg_row_len/1024/1024)/(blocks*8192/1024/1024),2))*100||'%' "fragment_per",
round((blocks*8192-num_rows*avg_row_len)/1024/1024,0) "frag_size Mb"
from dba_tables
where round((blocks*8192-num_rows*avg_row_len)/1024/1024,0)  > 1
and owner='WYQ'
order by 6 desc;




78M


第三种：
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='A';
descr:='Segment Advisor Example';


dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);


dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'WYQ',
attr2 => 'A',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);


dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');


dbms_advisor.execute_task(name);
end;
end; 
/




select segment_owner,segment_name,segment_type,partition_name,allocated_space/1024/1024 "allocate(mb)",TRUNC(reclaimable_space/1024/1024,2) "reclaim(MB)",C3,C2,C1 
from table (dbms_space.asa_recommendations()) 
where segment_type='TABLE'
ORDER BY "reclaim(MB)" DESC;




删除执行的任务：
exec dbms_advisor.delete_task('A');


50M


真正释放空间：
alter table wyq.a move;


select bytes/1024/1024 from dba_segments where segment_name='A';
共释放56M，


第一种：65M，
第二种：78M
第三种：50M

总结：还是官方的segment advisor更接近实际。

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

转载于:http://blog.itpub.net/29677883/viewspace-2123977/