oracle数据库碎片化管理(二)
NTIGUOUS_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
/
********************************************************************************
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_