详细聊聊Oracle表碎片对性能有多大的影响

目录

• 前言
• ️1.创建测试表
• 1.1建立表空间
• 1.2创建ASSM表空间
• 1.3创建表及索引
• ️2.查看表统计信息
• ️3.空块占用空间
• ️4.查看执行计划
• ️5.删除大量数据
• ️6.再次查看执行计划
• ️7.再次空块占用空间
• ️8.整理表碎片
• ️9.效果确认
• ️10.技能拓展
• 总结

前言

本文通过对Oracle 表碎片整理，对比了前后对数据库性能的影响。

️ 1.创建测试表

1.1 建立表空间

SYS@EDB> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

1.2 创建ASSM表空间

CREATE TABLESPACE “JEAMES” DATAFILE
‘/u01/app/oracle/oradata/EDB/jeames01' SIZE 50M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

1.3 创建表及索引

##创建测试表t1,id列创建索引in_t1_id
create table t1 tablespace JEAMES as select level as id from dual connect by level<=300000;
create index in_t1_id on t1(id);
analyze table t1 compute statistics;
select count(*) from t1;

️ 2.查看表统计信息

select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1';

select sum(bytes)/1024/1024 from dba_segments where segment_name=‘IN_T1_ID';

SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1';

总结：

查看表T1,段4M, 占用473个数据块,39个空块,索引IN_T1_ID段6M；

️ 3.空块占用空间

查看没有数据的块占用的空间

DBMS_STATS 包无法获取 EMPTY_BLOCKS 统计信息，所以需要用 analyze 命令再收集一次统计信息，估算表在高水位线下还有多少空间可用 ，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少! !

SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB”
FROM USER_TABLES
WHERE table_name = ‘T1';

️ 4.查看执行计划

查看全表扫描cost为131，基于成本

explain plan for select * from t1;
select * from table(dbms_xplan.display);

️ 5.删除大量数据

删除大部分数据,并收集统计信息,查看T1占用数据块和空块都没有减少

delete from t1 where id>10;

analyze table t1 compute statistics;
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1';

️ 6.再次查看执行计划

查看全表扫描cost为125，基于成本, 使用率几乎没有下降

explain plan for select * from t1;
select * from table(dbms_xplan.display);

️ 7.再次空块占用空间

SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB”
FROM USER_TABLES
WHERE table_name = ‘T1';

️ 8.整理表碎片

开启行迁移
alter table t1 enable row movement;
降低水位线
alter table t1 shrink space;
关闭行迁移
alter table t1 disable row movement;
SYS@EDB> select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1'

SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB”
FROM USER_TABLES
WHERE table_name = ‘T1';

收集统计信息

analyze table t1 compute statistics;

️ 9.效果确认

占用数据块及空闲数据块下降,并且cost使用也下降

SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB”
FROM USER_TABLES
WHERE table_name = ‘T1';

select blocks,empty_blocks,num_rows from user_tables where table_name=‘T1';

explain plan for select * from t1;
select * from table(dbms_xplan.display);

️ 10.技能拓展

1.再用alter table table_name move 时，表相关的索引会失效，
所以之后还要执行 alter index index_name
rebuild online; 最后重新编译数据库所有失效的对象
2. 在用 alter table table_name shrink space cascade 时，
3. 他相当于 alter table table_name move 和
alter index index_name rebuild online. 所以只要编译数据库失效的对象就可以；
4. Move 会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
5. shrink space 同样会移动高水位，
6. 但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。
原理不一样，move 是以 block 为单位重组数据，
行的 rowid 都会跟着变化，而 shrink 是以”行“为单位重组
数据，他是根据复杂的算法从逻辑+物理重组数据
move 速度快于 shrink.
Move 相当于 从 segment 底部 move 到 头。
Shrink 相当于先 delete，然后再 insert 这样产生很多 undo,redo
通常首选 MOVE
语法：
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table <table_name> shrink space compcat;
k segment shrink 分为两个阶段：
1、数据重组(compact):通过一系列 insert、delete 操作，
将数据尽量排列在段的前面。在这个过程中需
要在表上加 RX 锁，即只在需要移动的行上加锁。由于涉及到 rowid 的改变，
需要 enable row movement.同时要 disable 基于 rowid 的 trigger.这一过程对业务影响比较小。
2、HWM 调整：第二阶段是调整 HWM 位置，释放空闲数据块。
此过程需要在表上加 X 锁，会造成表上的所有
DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
 Shrink Space语句两个阶段都执行。Shrink
Space compact 只执行第一个阶段。
如果系统业务比较繁忙，可以先执行 Shrink Space compact 
重组数据,然后在业务不忙的时候再执行 Shrink
Space 降低 HWM 释放空闲数据块。shrink 必须开启行迁移功能。

总结

到此这篇关于Oracle表碎片对性能有多大影响的文章就介绍到这了,更多相关Oracle表碎片对性能影响内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！