Oracle表碎片分析

在Oracle运维的过程中，会经常遇到说表碎片严重的问题。表碎片严重会导致出现数据库的性能问题，在sql访问数据的时候需要消耗更多的资源，因此常常出现在数据库的优化中。想要获取表碎片的情况，计算方式和处理方式很重要。

1.计算表的碎片情况

• 查看某个用户下表的碎片情况，查询信息的准确性依赖于统计信息的准确性，并不能反映表的碎片率。

set lines 199 pagesize 199
set COLSEP '|'
select d.owner,
       d.table_name,
       round((d.blocks * 8) / 1024, 2) "allocated MB",
       round((d.num_rows * d.avg_row_len / 1024 / 1024), 2) "used MB",
       round((d.blocks * 10 / 100) * 8 / 1024, 2) "reserved(d.pct_free) MB",
       round((d.blocks * 8 - (d.num_rows * d.avg_row_len / 1024) -d.blocks * 8 * 10 / 100) / 1024,2) "waste_MB"
  from dba_tables d
 where d.blocks * 8 / 1024 > 10
  -- and d.owner = 'SCOTT'
 order by 6 desc;

• 使用脚本时替换上面注释掉的条件

OWNER   |TABLE_NAME     |allocated MB|   used MB|reserved(d.pct_free) MB|  waste_MB
--------|---------------|------------|----------|-----------------------|----------
SYS     |IDL_UB1$       |      266.02|        .9|                   26.6|    238.52
SYS     |IDL_UB2$       |       30.66|       .28|                   3.07|     27.32
SYS     |COLLECTION$    |       23.27|       .07|                   2.33|     20.87
SYS     |TYPE$          |       23.27|       .22|                   2.33|     20.72
SYS     |ATTRIBUTE$     |       23.27|       .73|                   2.33|     20.21
SYS     |RESULT$        |       18.03|       .13|                    1.8|      16.1
SYS     |METHOD$        |       18.03|       .18|                    1.8|     16.05
SYS     |PARAMETER$     |       18.03|       .69|                    1.8|     15.54
SYS     |JAVA$MC$       |       12.15|       .09|                   1.21|     10.85
SYS     |VIEWTRCOL$     |       11.15|         0|                   1.11|     10.03
SYS     |OPQTYPE$       |       11.15|       .01|                   1.11|     10.03

输出项说明如下：

"allocated MB"             代表实际的大小
 "used MB"                 真正使用的大小
 "reserved(d.pct_free) MB" 代表保留的大小，一般都是默认10%
 "waste_MB"                代表浪费的空间

如果想查看具体某张表的碎片情况，可以把where条件中的OWNER='SCOTT’换成table_name='表名’。

2.处理表空间的碎片问题

可以通过以下几种方式回收表的空闲空间。

第一种方式

alter table scott.emp move;
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'EMP')

• EMP换成需要回收的表名字
• 回收之后需要重新收集表的统计信息，统计信息的收集方式可以参考
• 统计信息收集完成之后，可以重启再重新检查一遍表碎片情况

第二种方式

alter table emp enable row  movement;
alter table emp shrink space cascade;
alter table emp disable row  movement;
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'EMP')

• EMP换成需要回收的表名字
• 回收之后需要重新收集表的统计信息，统计信息的收集方式可以参考
• 统计信息收集完成之后，可以重启再重新检查一遍表碎片情况

第三种方式

可以通过ctas的方式，重建碎片率高的表，如果创建过程中，源表有记录更改，那么数据可能存在不准确的情况

create table emp_temp as select * from emp;