已索引
该文章已被如下文章引用：
表空间碎片、表碎片、索引碎片本质上是相通的，因为表空间里面放的就是表和索引，表空间的碎片就是表和索引在使用过程中产生的。
碎片是否需要特别关注
按Oracle的说法，不需要。
现在数据库的表空间都是使用本地管理方式(local) + 自动段空间管理(ASSM)，而非字典管理方式(dictionary) + 手动段空间管理(MSSM)，碎片问题我们不用过多关注。
对于本地管理的表空间，SMON 会自动进行“相邻”空闲空间的碎片整理。
在 Oracle 手册 Database Administrator's Guide > 14 Managing Tablespaces 的 Locally Managed Tablespaces 部分有这样写到：
- Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps.
- Coalescing free extents is unnecessary for locally managed tablespaces.
- If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. AUTOALLOCATE is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback.
- If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace.
另外，表空间碎片是不可避免的，即使简单的 insert 语句也会造成表空间碎片。这是由于 Extent Alignment 特性造成的， Doc ID 2966557.1 对此有解释。
例如，新建一个表空间和一个表，然后往表中循环插入10万数据，就可以在表空间中查到碎片。
如果已经因为碎片产生了影响，希望对碎片问题进行发现和分析，可以使用下面的方法。
表空间碎片
发现表空间碎片问题
方法一：Doc ID 1264470.1 给出了快速分析表空间碎片及其处理办法，如下：
set lines 200
set pages 200
SELECT fs.tablespace_name,
COUNT(*) AS fragments,
ROUND(SUM(fs.bytes)/1024,2) AS total_kb,
ROUND(MAX(fs.bytes)/1024,2) AS biggest_kb,
ROUND(SUM(fs.bytes)/1024,2)-ROUND(MAX(fs.bytes)/1024,2) AS fragmented_space_kb, -- I added this row
(ROUND(SUM(fs.bytes)/1024,2)-ROUND(MAX(fs.bytes)/1024,2))/ROUND(SUM(fs.bytes)/1024,2) AS fragmented_space_pct -- I added this row
FROM DBA_FREE_SPACE fs, DBA_DATA_FILES df
WHERE fs.file_id(+) = df.file_id
GROUP BY fs.tablespace_name
order by 6 desc;
## The difference between TOTAL_KB and BIGGEST_KB is the fragmented space in KB for the corresponding tablespace.
## 文档说，TOTAL_KB 与 BIGGEST_KB 的差值，即我增加的那一列，就是碎片空间的大小，PCT 表示碎片空间占整个 free 空间的比值。方法二：很多 DBA 通过查询表空间的 FSFI 值来进行分析：
select a.tablespace_name,
trunc(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) fsfi
from dba_free_space a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name
and b.contents not in('TEMPORARY','UNDO','SYSAUX')
group by A.tablespace_name
order by fsfi;数字越小，表空间碎片越多，如果 FSFI 值 < 30%，则该表空间的碎片较多。
方法三：通过 dba_free_space 查询表空间中不连续的空闲空间数量
set pages 800;
SELECT a.tablespace_name, COUNT(1) count_fras
FROM dba_free_space a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
AND b.contents NOT IN ('TEMPORARY', 'UNDO', 'SYSAUX')
GROUP BY a.tablespace_name
HAVING COUNT(1) > 500
ORDER BY count_fras;如果一个tablespace的 free 空间不连续，那每段free空间都会在dba_free_space中存在一条记录。
如果一个tablespace有过多记录，说明表空间存在碎片，当采用“字典管理的表空间”碎片超过500就需要对表空间进行碎片整理。
方法四：使用 Oracle 公司提供的分析脚本
运行下面的 Oracle 公司提供的脚本，可按表空间显示连续的空闲空间情况，脚本最后还会给出每个表空间的extents数量及总的可用空间大小。
如果一个表空间总的free空间很大，extent数量很多，且extent都比较小，则该表空间碎片现象比较严重。
========
Script : tfstsfgm
========
SET ECHO off
REM NAME:TFSTSFRM.SQL
REM USAGE:"@path/tfstsfgm"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT ON DBA_FREE_SPACE
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The following is a script that will determine how many extents
REM of contiguous free space you have in Oracle as well as the
REM total amount of free space you have in each tablespace. From
REM these results you can detect how fragmented your tablespace is.
REM
REM The ideal situation is to have one large free extent in your
REM tablespace. The more extents of free space there are in the
REM tablespace, the more likely you will run into fragmentation
REM problems. The size of the free extents is also very important.
REM If you have a lot of small extents (too small for any next
REM extent size) but the total bytes of free space is large, then
REM you may want to consider defragmentation options.
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:
create table SPACE_TEMP (
TABLESPACE_NAME CHAR(30),
CONTIGUOUS_BYTES NUMBER)
/
declare
cursor query is select *
from dba_free_space
order by tablespace_name, block_id;
this_row query%rowtype;
previous_row query%rowtype;
total number;
begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
exit when query%notfound;
if this_row.block_id = previous_row.block_id + previous_row.blocks then
total := total + this_row.bytes;
insert into SPACE_TEMP (tablespace_name)
values (previous_row.tablespace_name);
else
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
total := this_row.bytes;
end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
end;
.
/
set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;
select tablespace_name, count(*) "# OF EXTENTS",
sum(contiguous_bytes) "TOTAL BYTES"
from space_temp
group by tablespace_name;
spool off
drop table SPACE_TEMP
/
方法五：Doc ID 1019709.6 也介绍了如何查询表空间的剩余空间和碎片情况。
Script to Report Tablespace Free and Fragmentation.
set pages 800;
set lines 200;
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from
dba_data_files total,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
and total.file_id=free.file_id(+)
group by
total.tablespace_name,
total.bytes;方法六：使用段顾问来分析发现问题
处理表空间碎片问题
对于本地管理的表空间，SMON 会自动进行相邻空闲空间的碎片整理，无须进行碎片合并。
Coalescing free extents is unnecessary for locally managed tablespaces.
对于非连续的空闲空间，无法从表空间级别进行处理。
对于字典管理的表空间，可以使用如下命令手动处理：
alter tablespace xxx coalesce;文档( Doc ID 1264470.1 )在给出表空间碎片查询方法的同时，也给出了表空间碎片的处理办法。
文档说，有很多办法来回收碎片，下面介绍的是最好、最可靠、最快的三种方式。
总体思路就是：shrink、move table、expdp/impdp
Please carry the following steps to de-fragment the tablespace in non-peak hours, preferably in the weekends to resolve this issue :-
a) If the tablespace PSHUGE01 is dictionary managed , then please follow one of the following methods to de-fragment it :-
如果是字典管理的表空间（同样适用于本地管理的表空间），使用下面的方法回收碎片：
1)Creation of another tablespace (locally managed) TBS1 of the same size
2)Moving of all the tables of this tablespace (PSHUGE01) to the new tablespace TBS1
3)Export of all the tables in PSHUGE01 tablespace with long datatypes
4)Rebuilding online of all the index present in PSHUGE01 in TBS1 and the indexes whose status have now become UNUSABLE
5)Dropping the tablespace PSHUGE01 and recreating it making it locally managed
6)Again moving all the tables of this tablespace of TBS1 to the tablespace PSHUGE01
7)Importing the exported tables back to the database in tablespace PSHUGE01.
8)Rebuilding online of all the index present in TBS1 back in PSHUGE01
9)Enabling all the required constraints and rebuilding all the indexes that have become invalid
10)Dropping the tablespace TBS1
This method can be used for locally managed tablespace also. NOTE:- Tables with LONG and LOB data types cannot be moved. So, export/import is the best way to re-organize such tables.
b)If the tablespace is extent management local segment space management AUTO , Moving of all the tables of this tablespace (PSHUGE01) within the same tablespace and then rebuilding the UNUSABLE indexes will help in de-fragmentation.
如果表空间是本地管理且ASSM，原表空间移动表并重建失效的索引。如果有LONG和LOB字段，则 expdp/impdp 是最好的方式。
NOTE:
1)Tables with LONG and LOB data types cannot be moved. So, export/import is the best way to re-organize such tables.
User can move Normal table, partition tables, Indexes, partition indexes, subpartition indexes, whichever is present is the tablespace
The commands to move these are as below:
Alter table owner.tablename move tablespace TS_NAME;
Alter table owner.tablename move partition partition_name tablespace TS_NAME;
Alter index owner.index_name rebuild tablespace TS_NAME;
ALTER INDEX owner.index_name REBUILD PARTITION partition_name TS_NAME;
ALTER INDEX owner.index_name REBUILD SUBPARTITION sub_partition_name;
c) If the tablespace is extent management local , as you are using 10g, you can implement the following:
如果是10g，可以通过 shrink 表空间中所有表的方式来回收碎片。
1)Enable row movement for all the tables in tablespace PSHUGE01.
2)Shrinking space for all the tables in tablespace PSHUGE01
2)Rebuilding all the indexes online which have become INVALID...if any...they should not be invalid using this method
NOTE:- FOR ALL THE METHODS, you have to check first for self-containment of the tablespace by executing DBMS_TTS.TRANSPORT_SET_CHECK package on the tablespace PSHUGE01
Moving a dictionary tablespace to a tablespace with extent management local segment space management AUTO is the most advisable to make sure that you do not get much fragmentation in future
There are many methods to de-fragment. Mentioned above are the best , most reliable and the fastest 3 options.
有很多办法来回收碎片，上面介绍的是最好、最可靠、最快的三种方式。
表碎片
发现表碎片问题
根据统计信息分析法1：
set lines 200;
set pages 800;
col frag format 999999.99;
col owner format a10;
col table_name format a35;
select t.* from (
select a.owner,
a.table_name,
a.tablespace_name,
a.num_rows,
(a.avg_row_len*a.num_rows)/1024/1024/1024 as "REAL_SIZE(G)",
sum(b.bytes)/1024/1024/1024 as "USED_SIZE(G)",
(a.avg_row_len*a.num_rows)/sum(b.bytes) frag
from dba_tables a, dba_segments b
where a.table_name = b.segment_name
and a.owner=b.owner
and a.owner not in ('SYS','SYSTEM','USER','DBSNMP','ORDSYS','OUTLN')
group by a.owner, a.table_name, a.tablespace_name, a.avg_row_len, a.num_rows
having (a.avg_row_len*a.num_rows) / sum(b.bytes) < 0.7
order by "USED_SIZE(G)" desc
) t
where rownum <= 100;根据统计信息分析法2：
set lines 200;
set pages 800;
select owner,
table_name,
round(avg_row_len*num_rows*100 / ((100 - pct_free)*8192)) need_blocks,
blocks ALLOC_BLOCKS,
round(avg_row_len*num_rows*100*8 / ((100 - pct_free)*8192*1024)) "NEED(MB)",
round(blocks*8 / 1024) "ALLOC(MB)",
round(avg_row_len*num_rows*10000 / ((100 - pct_free)*8192*blocks)) "USED%",
pct_free,
to_char(last_analyzed, 'yyyymmdd hh24:mi:ss') analyzed,
num_rows,
partitioned
from dba_tables a
where owner not in ('SYS', 'SYSTEM')
and blocks > 3000
-- and partitioned='NO'
and round(avg_row_len*num_rows*10000 / ((100 - pct_free)*8192*blocks)) < 70
order by "USED%";使用段顾问分析：
通过上面的方法，定位到表后，可以使用下面的查询来进一步确认该表的真实情况：
表的实际块数：select count( distinct substr(rowid,1,15)) "block_count" from FSSE.TXN_LOG;
表的统计块数：select blocks from dba_segments where owner='FSSE' and segment_name='TXN_LOG';表的实际块数我们是通过对表所有行的 ROWID 进行分析来得到。ROWID 的前15位可以定位到块号，所以 distinct 前15位就可以统计出所有行使用了多少个块：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 20
1 2 3 4 5 6 7 8 9 10 11 12 13 14 16 16 17 21
但是注意：该方法只能对是否存在大量无数据的块这种情况进行分析，如果表中的行在块里分布的很分散，该方法是分析不出来的。比如说，假设一个块能放10行数据，目前表中共有10行数据，理想的情况是这10条数据全部放在同一个块里，但是如果每一行记录都放在不同的块里，则通过ROWID分析出来是10个块，通过统计信息分析出来也是10个块。
处理表碎片问题
处理方法1：
exp/expdp 导出，drop或者truncate表后，再 imp/impdp
处理方法2：
CTAS 重建表
处理方法3：
alter table <表名> move [tablespace <表空间名>];
处理方法4：
shrink，完成后注意收集统计信息
处理方法5：
Online Redifinition
索引碎片
发现索引碎片问题
按 BLEVEL 分析：
col tablespace_name for a20;
col owner for a10;
col index_name for a30;
select id.tablespace_name,
id.owner,
id.index_name,
id.blevel,
sum(sg.bytes)/1024/1024,
sg.blocks,
sg.extents
from dba_indexes id, dba_segments sg
where id.owner=sg.owner
and id.index_name=sg.segment_name
and id.tablespace_name=sg.tablespace_name
and id.owner not in ('SYS','SYSTEM','USER','DBSNMP','ORDSYS','OUTLN')
and sg.extents > 100
and id.blevel >=3
group by id.tablespace_name,
id.owner,
id.index_name,
id.blevel,
sg.blocks,
sg.extents
having sum(sg.bytes)/1024/1024 > 100;该语句会显示索引高度 Blevel >=3 且索引大小超过 100MB 的索引。
使用 Analyze 分析：
analyze index EXP.PK_RT_F_NHX_H_GL_KNS_GLVC validate structure; 注意：会产生锁，且耗时较长
select DEL_LF_ROWS*100/decode(LF_ROWS,0,1,LF_ROWS) PCT_DELETED from index_stats;查询语句中 PCT_DELETED 的含义为索引被删除项与索引项总数的所占比例，如果该值 >= 20%，则说明该索引碎片严重。
发现索引碎片问题
方法1：重建
alter index xxx rebuild online parallel 4 nologging; 可迁移到新的表空间，需要额外空间，创建新的索引树，压缩索引高度方法2：coalesce
alter index xxx coalesce; 不迁移到新的表空间，不需要额外空间，压缩同一个索引枝下的叶节点空间
方法3：shrink
