oracle数据库碎片化管理
作者:网络转载 发布时间:[ 2017/4/20 10:51:32 ] 推荐标签:数据库 Oracle
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;
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;
/
---手动执行计划
declare name varchar2(100);
begin
name:='Manual_tblorders';
dbms_advisor.execute_task(name);
end;
/
NOTE:如果执行计划结果中已经有数据则不能直接手动执行需要删除再执行
---查看手动新建的计划是否已经执行完成
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%'
and 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'));
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11