Oralce各个碎片查询与整理
在维护 Oracle 数据库时，我们应该经常去巡检 Oracle 的表空间碎片、dba_free_space 、表碎片、索引碎片等等一篮子数据库运行过程中的参数。
1.表空间碎片
1.查看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% 则表空间碎片太多.
fsfi的最大可能值为 100（一个理想的单文件表空间）。随着范围的增加，fsfi值缓慢下降，而随着最大范围尺寸的减少，fsfi值会迅速下降。
2.查看dba_free_space
dba_free_space 显示的是有 free 空间的 tablespace ，如果一个 tablespace 的free 空间不连续，那每段free空间都会在 dba_free_space 中存在一条记录。如果一个 tablespace 有好几条记录，说明表空间存在碎片，当采用字典管理的表空间碎片超过 500 就需要对表空间进行碎片整理。
select
a.tablespace_name ,count(1) 碎片量
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) >20
order by
2;
3.按照表空间显示连续的空闲空间
脚本英文申明部分
========
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;
脚本结束，删除SPACE_TEMP临时表
spool off
drop table SPACE_TEMP
/
2.表碎片
方法1：显示碎片率最高的200个表(基于统计信息是否准确)
命令行下设置输出格式
col frag format 999999.99
col owner format a30;
col table_name format a30;
select * from
(select
a.owner,a.table_name,a.num_rows,
a.avg_row_len * a.num_rows total_bytes,
sum(b.bytes),
trunc((a.avg_row_len*a.num_rows)/sum(b.bytes),2)*100||'%' 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','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS',
'EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')
group by
a.owner,a.table_name,a.avg_row_len,a.num_rows
having
a.avg_row_len*a.num_rows/sum(b.bytes)<0.7
order by
sum(b.bytes) desc)
where
rownum<=200;
方法2：收集表统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=> 'TBLORDERS');
确定碎片程度
SELECT
table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real used spaceM",
trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
trunc(ROUND ((blocks * 8 - (num_rows * avg_row_len / 1024)- blocks * 8 * 10 / 100),2) /1024,2) "Waste spaceM"
FROM
dba_tables
WHERE
table_name = 'TBLORDERS';
3.索引碎片
1.查看索引高度为 2 并且索引大小超过 20M 的索引
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>=2
group by
id.tablespace_name,id.owner,id.index_name,id.blevel,sg.blocks,sg.extents
having
sum(sg.bytes)/1024/1024>20;
2.analyze index方法(会锁表)
analyze index index_name validate structure;
select
del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) pct_deleted
from
index_stats;
如果 pct_deleted> 20%说明索引碎片严重.
4.automatic segment advisor
数据表上频繁的进行插入、更新和删除动作会产生表空间碎片。Oracle 可在表或索引上执行 Segment shrink。
使得 segment 的空闲空间可用于表空间中的其它 segment，可改善 DML 性能。
调用 Segment Advisor 对指定 segment 执行增长趋势分析以确定哪些 Segment 受益于 Segment shrink。
执行 shrink 操作，Segment Advisor 推荐启用表的 ROW MOVEMENT
SQL> alter table scott.tblorders enable row movement;
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name := 'Manual_tblorders';
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 => 'SCOTT',
attr2 => 'TBLORDERS',
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;
/
删除执行计划
declare
name varchar2(100);
begin
name := 'Manual_tblorders';
DBMS_ADVISOR.DELETE_TASK(name);
end;
/
删除也可以像下面这样
exec dbms_advisor.delete_task('Manual_tblorders');
手动执行计划
declare
name varchar2(100);
begin
name := 'Manual_tblorders';
dbms_advisor.execute_task(name);
end;
/
执行也可以像下面这样
exec dbms_advisor.execute_task('Manual_tblorders');
查看手动新建的计划是否已经执行完成
select
task_id, task_name, status,advisor_name, created
from
dba_advisor_tasks
where
owner = 'SYS' and task_name='Manual_tblorders'
and advisor_name = 'Segment Advisor' ;
select
af.task_name, ao.attr2 segname,
ao.attr3 partition, ao.type, af.message
from
dba_advisor_findings af, dba_advisor_objects ao
where
ao.task_id = af.task_id
and ao.object_id = af.object_id
and af.task_id = &task_id;
只查询可以进行shrink操作的对象
select
f.task_name, o.attr2 segname, o.attr3 partition, o.type, f.message
from
dba_advisor_findings f, dba_advisor_objects o
where
o.object_id = f.object_id
and o.task_name=f.task_name
and (f.message like '%shrink%'
or f.message like '%收缩%')
and f.task_id=&task_id
order by
f.impact desc;
查看automatic segment advisor的recommendations结果
select
tablespace_name, segment_name, segment_type,
partition_name, recommendations, c1
from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
5. 碎片整理方法
5.1表空间碎片整理
alter tablespace users coalesce;
5.2表碎片整理
方法说明
方法1: exp/imp 或 data pump 数据泵技术
方法2: CTAS
create table newtable as select * from oldtable;
drop table oldtable;
rename table newtable to oldtable;
方法3:move tablespace技术
alter table move tablespace ;
方法4:shrink
alter table enable row movement;
–压缩表以及相关数据段并下调HWM
alter table shrink space cascade;
–只压缩数据不下调HWM,不影响DML操作
alter table shrink space compact;
–下调HWM,影响DML操作
alter table shrink space;
方法5:online redefinition
online redefinition具有的应用场景:
1).Online table redefinition enables you to:
2).Modify the storage parameters of a table or cluster
3).Move a table or cluster to a different tablespace
4).Add or drop partitioning support (non-clustered tables only)
5).Change partition structure
6).Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
7).Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
8).Add support for parallel queries
9).Re-create a table or cluster to reduce fragmentation
10).Convert a relational table into a table with object columns, or do the reverse.
11).Convert an object table into a relational table or a table with object columns, or do the reverse.
整理步骤
步骤1：检测表是否具有按主键进行ONLINE REDIFINITION能力
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('scott','t1',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
步骤2：新建一张同结构的临时表
create table scott.tp1 tablespace ocpyang
as
select * from scott.t1 where 1=2;
步骤3:启动ONLINE REDIFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('scott', 't1','tp1','',dbms_redefinition.cons_use_pk);
END;
/
步骤4: Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs,
grants, and constraints on scott.tblorders.)
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott', 't1','tp1',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
步骤5:检查是否除 primary、constraint 之外的错误
select
object_name, base_table_name, ddl_txt
from
DBA_REDEFINITION_ERRORS;
步骤6：Optionally, synchronize the interim table hr.int_admin_emp.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 't1', 'tp1');
END;
/
步骤7：Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 't1', 'tp1');
END;
/
NOTE:
The table scott.tblorders is locked in the exclusive mode only for a small window toward the end of this step.
After this call the table scott.tblorders is redefined such that it has all the attributes of the scott.tptblorders table.
5.3 索引碎片整理
alter index rebuild online parallel 4 nologging;
alter table coalesce;
由于 rebuild index 可以在线、并行、不产生日志方式进行.推荐使用 rebuild index.
6.最佳实践
1.针对表的碎片化优先考虑 shrink 技术；针对索引的碎片优先考虑 rebuild index 技术；
2.如果 shrink 不理想则采用 online redefinition 技术
3.如果空间不够导致 rebuild index 无法实施则考虑 coalesce 技术
4.虽然 shrink 和 rebuild index 都不影响在线应用但保险起见尽量避免在业务高峰执行
5.shrink 技术考虑先压缩数据不下调HWM，然后找业务低谷时间再下调 HWM 并释放空间
6.建议 rebuild index 以非 ONLINE 方式执行虽然支持 online .
